Search for more than one keyword

Question

I have this little php search script to help me search through my users table.

Example:

If I search for "John", I get results
If I search for "John Doe" (two words with a space) I get no results. Even if the users name is John Doe.

I was wondering if there is something in this script I could change to help me search for multiple key words.

Here is the Search code:

<?php
if(isset($_GET['keywords'])){
    $keywords = escape($_GET['keywords']);

    $search = DB::getInstance()->query("
    SELECT `id`,`username`,`first_name`,`last_name`,`unit`,`email`,`rent_own`,`city`,`zip`,`phone`,`joined`,`group_id` FROM `users` WHERE 
    `username`    LIKE '%{$keywords}%' OR
    `first_name`  LIKE '%{$keywords}%' OR
    `last_name`   LIKE '%{$keywords}%' OR
    `unit`        LIKE '%{$keywords}%' OR
    `email`       LIKE '%{$keywords}%' OR
    `rent_own`    LIKE '%{$keywords}%' OR
    `city`        LIKE '%{$keywords}%' OR
    `zip`         LIKE '%{$keywords}%' OR
    `phone`       LIKE '%{$keywords}%' OR
    `joined`       LIKE '%{$keywords}%' OR
    `group_id`    LIKE '%{$keywords}%'  
    ");                



?>

Any thought or solutions are welcome and appreciated.

My first thought is that you're passing `John Doe` as a query string parameter which includes the space as `%20` in between the two words. Can you confirm that `$keywords` actually is `John Doe` when you intend it to be? — okoboko, Jul 17 '14 at 06:12
No, I'm pretty sure you are right about the space being `%20`. Is there a way I can fix that? — daugaard47, Jul 17 '14 at 06:15
make sure $keywords = escape($_GET['keywords']); is doing what you THINK it'll do. — Jacky Cheng, Jul 17 '14 at 06:15
Explode your keywords and search in each database table row for each keyword, so you should get all possible data returned. — xyNNN, Jul 17 '14 at 06:15
I have no idea about requirements. But it seems fulltext search can be useful here. — Ravi Kant Mishra, Jul 17 '14 at 06:17
`echo` `$keywords` in standard SQL format and find out if the space is there. — 6339, Jul 17 '14 at 06:21
another good answer is [here](http://stackoverflow.com/questions/15538479/search-for-multiple-keywords-with-php-and-mysql-where-x-like) — Shaunak Shukla, Jul 17 '14 at 06:50

score 3 · Answer 1 · answered Jul 17 '14 at 06:26

When doing a search function of your site. Match and Against is better than Like sql statement. The field must be set to FullText match the term on the field:

SELECT `id`,`username`,`first_name`,`last_name`,`unit`,`email`,`rent_own`,`city`,`zip`,`phone`,`joined`,`group_id` FROM `users` WHERE MATCH(`id`,`username`,`first_name`,`last_name`,`unit`,`email`,`rent_own`,`city`,`zip`,`phone`,`joined`,`group_id`) AGAINST('keywords')

You can also use the IN BOOLEAN MODE to allow operators in the sql statement. eg. ... MATCH(first_name,last_name) AGAINST('-John +Doe' IN BOOLEAN MODE) ... (-) minus sign that means nothing should match 'John' (+) the word must be present in the match.

There are many other operators to be used. Refer to this page for more operator and explanation

score 1 · Accepted Answer · answered Jul 17 '14 at 06:16

1

If you're searching by the full name you'll need to add a condition for it

CONCAT(`first_name`,' ',`last_name`) LIKE '%{$keywords}%' OR

answered Jul 17 '14 at 06:16

FuzzyTree

32,014
3
54
85

the OP is asking about `username` not `first_name` & `last_name`. – 6339 Jul 17 '14 at 06:18
@echo edit your question and mention about `first_name` and `last_name` instead of `user name`. – 6339 Jul 17 '14 at 06:23
@FuzzyTree Also one more question. If I wanted to search for users in different user groups such as Admin moderator ...etc how could I do that if my `group_id` is numeric. Example: Admin =1 , moderator =2 If I search Admin I get no results. – daugaard47 Jul 17 '14 at 06:27
@echo if you want to show admins when searching for that keyword, you'd have to join on `group_id` and add an or condition for the admin column – FuzzyTree Jul 17 '14 at 06:30
@FuzzyTree something like this: ` group_id == 1){ echo 'admin'; } elseif ($s->group_id == 2){ echo 'moderater'; } elseif ($s->group_id == 3){ echo 'owner'; } ?>` – daugaard47 Jul 17 '14 at 06:33

Search for more than one keyword

2 Answers2