5

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?

11

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 . '%%')
) );
  • You should use $wpdb->esc_like() in your code. Otherwise the PLUGIN_SLUG can mess it up, if there’s ‘%’ in it. (Probably it won’t, since it’s a slug, but...) – Krzysiek Dróżdż Mar 24 '18 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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