Reputation: 633

PHP Search First Name and Last Name

So I have a search feature on my website and this is what the code looks like.

$search_people = mysql_query("SELECT * FROM glnce_users 
                 WHERE f_name LIKE '%$search%' OR l_name LIKE '%$search%'");`

If i type in my search bar Chris it will bring up all of the Chris'

If I type in my search bar Olson it will bring up all of the Olson's

However if I type in Chris Olson it won't provide me results for Chris Olson even though there is a person with the First name of Chris and the Last name of olson.

What am I doing wrong?

Upvotes: 5

Answers (9)

Zahra Badri

Reputation: 2034

I use like this in laravel:

     $q->where('first_name', 'like', '%'.$name.'%')
              ->orWhere('last_name', 'like', '%'.$name.'%')
->orWhere(DB::raw('CONCAT(first_name," ",last_name)'),'like','%'.$name.'%');

Upvotes: 0

Ijaz Ahmed Bhatti

Reputation: 729

I have used this and its working fine

                   $q->where('first_name', 'like', "%$name%")
                   ->orWhereRaw("concat(first_name, ' ', last_name) like '%$name%' ")
                  ->orWhere('last_name', 'like', "$name");

Upvotes: 1

stackflow

Reputation: 2122

Normal SQL,

$sql = "SELECT `id`, `first_name`, `last_name`, `mobile`, CONCAT(`first_name`, ' ',`last_name`) AS `full_name` FROM `users`
        WHERE `first_name` LIKE '%".$word."%'
        OR `last_name` LIKE '%".$word."%'
        OR CONCAT(`first_name`, ' ',`last_name`) LIKE '%".$word."%'
        OR `mobile` LIKE '%".$word."%';";

In Laravel,

$result = User::select('id', 'first_name', 'last_name', 'mobile', DB::raw('CONCAT(first_Name, " ", last_Name) AS name'))
        ->where('first_name', 'LIKE', '%'.$word.'%')
        ->orWhere('last_name', 'LIKE', '%'.$word.'%')
        ->orWhere(CONCAT(first_Name, " ", last_Name), 'LIKE', '%'.$word.'%')
        ->get();

Upvotes: 3

Number1SuperGuy

Reputation: 374

An exploded array seems bulky and error-prone. I would recommend concatenating your database columns in the query. That way if someone has a first name with a space in it, like "Sarah Jane Albertson", it won't look for a first name of "Sarah" and a last name of "Jane" and ignore the "Albertson" entirely.

This should do what you need:

$search_people = mysql_query("SELECT * FROM glnce_users 
    WHERE CONCAT(f_name, ' ', l_name) LIKE '%$search%' OR l_name LIKE '%$search%'");

Upvotes: 18

grc

Reputation: 23555

$searchArray = explode(" ", $search);

if (count($searchArray) > 1) {
$search_people = mysql_query("SELECT * FROM glnce_users WHERE f_name
    LIKE '%{$searchArray[0]}%' OR f_name LIKE '%{$searchArray[1]}%'
    OR l_name LIKE '%{$searchArray[0]}%' OR l_name LIKE '%{$searchArray[1]}%'");
} /* else do the original query */

You can switch the second OR with an AND if you want only Chris Olson be brought up, otherwise the Chris' and the Olson's will be brought up as well.

Upvotes: 2

Chandresh M

Reputation: 3828

First of all use explode function to get that two words(Any number of words you searched) from space.and then just try to create query in FOR LOOP..

Then use that query. you will absolutely get whatever you wanted.

Thanks.

Upvotes: 1

Silicone

Reputation: 673

you could use explode(). Here is some code which you could use:

<?php

$fullName = "Chris Olson";
$names_exploded = explode(" ", $fullName);   // will split " " (a space!)

counter_words = 0;

foreach($names_exploded as $each_name){
    $counter_words++;

/* check the word count */
if($counter_words == 1){
    $qPart .= " `f_name` LIKE '%$each_name%' OR `l_name` LIKE '%$each_name%'";
}else{
    $qPart .= " OR `f_name` LIKE '%$each_name%' OR `l_name` LIKE '%$each_name%'";
}

}

$q = mysql_query("ELECT * FROM `glnce_users` WHERE $qPart");
while($r = mysql_fetch_assoc($q)){
   // get your data here!
}

?>

Hope this helps. Check out this php search tut: YouTube

Upvotes: 2