How to Use Wildcards in $wpdb Queries Using $wpdb->get_results & $wpdb->prepare?

by

I am attempting to make a custom search for BuddyPress groups.

Here is my method:

public function search_groups($string){
    global $wpdb;
    $results = $wpdb->get_results($wpdb->prepare(
        "
        SELECT * 
        FROM {$wpdb->prefix}bp_groups 
        WHERE `name` LIKE '%%s%'
        AND `description` LIKE '%%s%' 
        AND `status` = 'public'
        ORDER BY `name` ASC
        ",
        $string,
        $string
    ));
    if(!empty($results)){
        $return = $results;
    } else{
        $return = false;    
    }
    return $return;
}

However, I am receiving a WordPress database error. I’m pretty sure it’s due to the fact that I’m using $wpdb->prepare();, since it uses % signs for replacing variables.

Let’s take a look at this portion of my query:

WHERE `name` LIKE '%%s%'
AND `description` LIKE '%%s%'

That just looks like trouble. Is there any way that I can accomplish something along the lines of…

WHERE `name` LIKE '%{$string}%'
AND `description` LIKE '%{$string}%'

and still use the $wpdb->prepare(); method?

1
1

I figured out a solution to my question.

First of all, in my original query, I should have specified OR instead of AND for searching between group names and group descriptions. (It was skewing the results.)

And I needed to double escape my ‘%’s in the LIKE statements.

Here is the updated query which works correctly:

SELECT * 
FROM {$wpdb->prefix}bp_groups 
WHERE `name` LIKE '%%%s%%'
OR `description` LIKE '%%%s%%' 
AND `status` = 'public'
ORDER BY `name` ASC

Example using $wpdb->query and $wpdb->prepare:

global $wpdb;
$wpdb->query( $wpdb->prepare(
    'DELETE FROM %s WHERE `option_name` LIKE %s',
    $wpdb->options,
    $wpdb->esc_like(PLUGIN_SLUG . '%%')
) );

Leave a Comment