Syntax for $wpdb->prepare when searching in two columns

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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?

Method 2

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 )

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0