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

Question

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?

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

Answer 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 )

Answer 3

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 );