1

I have this working statement for searching a single column in my database:

$foods = $wpdb->get_results( $wpdb->prepare( "SELECT id, foodname, namevariations, calories, carbs, fat, protein, sodium FROM foodsTable WHERE namevariations like %s", "%$search_text%"), ARRAY_A ); 

I would like to include another column in the search called "foodname" but I can't seem to figure out the correct syntax.

How should I formulate my prepare statement so that I'm searching "namevariations" OR "foodname" and a match in either column will select that row?

3 Answers 3

1

The part after WHERE decides which columns are searched.

So you need to change it this way:

$foods = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT id, foodname, namevariations, calories, carbs, fat, protein, sodium FROM foodsTable WHERE namevariations like %1$s OR foodname like %1$s",
        '%' . $wpdb->esc_like( $search_text ) . '%'
    ),
    ARRAY_A
);

Also you were using LIKE queries incorrectly. If the $search_text contained % character, it would be interpreted as wildcard and not as simple character. That's why I've added some escaping in your code. More on this topic here: How do you properly prepare a %LIKE% SQL statement?

2
  • Hi! For some reason I'm getting this error with your suggestion: WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%1 OR recipename like %1' at line 1] SELECT * FROM recipestable WHERE recipetags like %1 OR recipename like %1; []
    – Andy Bay
    Apr 3, 2021 at 12:30
  • Got it to work with this:$like = '%'. $wpdb->esc_like($searchExpression) . '%'; $sql_content = $wpdb->prepare( "SELECT ID FROM $wpdb->posts WHERE ( post_title LIKE '%%%s%%' or post_content LIKE '%%%s%%' )", $like, $like ); $post_id = $wpdb->get_var($sql_content);
    – Andy Bay
    Apr 3, 2021 at 15:01
0

Got it to work like this:

$recipes = $wpdb->get_results($wpdb->prepare("SELECT * FROM recipestable WHERE ( recipetags LIKE '%%%s%%' or recipename LIKE '%%%s%%' )", $like, $like ), ARRAY_A )
1
  • Your answer is off to a good start. Please consider explaining how and why this code solves the problem so others can learn from it. Check the guide to answers for further advice. Apr 3, 2021 at 19:55
0

For people landing from Google search, you can use numbered placeholders (%1$s, %2$s, etc.) in $wpdb->prepare() to use the same search term on multiple columns.

The WP documentation states that: "for compatibility with old behavior, numbered or formatted string placeholders (eg, %1$s, %5s) will not have quotes added by this function, so should be passed with appropriate quotes around them." Since we use numbered placeholders we will also have to put the quotes:

$query = $wpdb->prepare( 'SELECT * FROM table_name WHERE column_name LIKE \'%1$s\' OR other_column_name LIKE \'%1$s\'', $search_term );
$result = $wpdb->get_results( $query );

Literal percentage signs need to be written like %%:

$query = $wpdb->prepare( 'SELECT * FROM table_name WHERE column_name LIKE \'%%%1$s%%\' OR other_column_name LIKE \'%%%1$s%%\'', $search_term );
$result = $wpdb->get_results( $query );

So the code from the initial question would look like:

$foods = $wpdb->get_results( $wpdb->prepare( 'SELECT id, foodname, namevariations, calories, carbs, fat, protein, sodium FROM foodsTable WHERE namevariations LIKE \'%%%1$s%%\' OR foodname LIKE \'%%%1$s%%\'', $search_text ), ARRAY_A );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.