Wordpress SQL LIKE statement with multiple values

Question

I'm working on a Wordpress plugin to search through all the users by first name, last name, email etc. It is working as long as I only use 1 of the values like first name. I need to add all the values to the select!

I already tried adding more by using ',' and OR statements but it isn't working.

$results = $wpdb->prepare("SELECT * FROM users WHERE first_name LIKE %s", "%".$_POST['search']."%");

EDIT: found it

Found it!

$results = $wpdb->prepare("SELECT * FROM users WHERE lower(first_name) LIKE lower (%s) OR lower(last_name) LIKE lower(%s) LIKE lower(%s)OR lower(postcode) LIKE lower(%s)", "%".$_POST['search']."%", "%".$_POST['search']."%");

Community · Accepted Answer · 2017-04-13 12:37:28Z

1

To get a literal % to pass through $wpdb->prepare just double it. You don't need to be avoiding $wpdb->prepare.

Proof of concept:

var_dump($wpdb->prepare('SELECT * FROM {$wpdb->posts} WHERE post_title LIKE "%%%s%%"','Hello'));

So your query should be :

$results = $wpdb->prepare("SELECT * FROM users WHERE first_name LIKE %%%s%%",$_POST['search']);

(more...)

Update

Use this for imploding OR :

$var[] = 'name LIKE "%%%s%%"';
$var_data[] = 'Hello';
$var[] = 'email LIKE "%%%s%%"';
$var_data[] = '[email protected]';
$var[] = 'date LIKE "%%%s%%"';
$var_data[] = 'Howdy';
var_dump($wpdb->prepare('SELECT * FROM users WHERE '.implode(' OR ',$var),$var_data));

edited Apr 13, 2017 at 12:37

CommunityBot

11 silver badge

answered Jun 11, 2014 at 8:01

Saman Gholami

3,5127 gold badges34 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3729015 Over a year ago

But I need like: SELECT * FROM users WHERE first_name, last_name, street, email LIKE %s", etc.

Jon Lachonis · Accepted Answer · 2014-06-11 08:03:33Z

0

Are you treating the object properly? This would iterate over the values retrieved

while($row = $results->fetch_assoc()){
    echo $row['username'] . '<br />';
}

answered Jun 11, 2014 at 8:03

Jon Lachonis

9312 gold badges8 silver badges18 bronze badges

Collectives™ on Stack Overflow

Wordpress SQL LIKE statement with multiple values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related