CODEWITHSUNDEEP
php
daugaard47
daugaard47

Reputation: 1868

Search for more than one keyword

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

Example:

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.

Upvotes: 2

Views: 241

Answers (2)

Mohit S
Mohit S

Reputation: 14044

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

Upvotes: 3

FuzzyTree
FuzzyTree

Reputation: 32392

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

Upvotes: 1

Related Questions