PHP Search First Name and Last Name

Question

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?

Solution 1

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%'");

Solution 2

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();

Solution 3

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

Solution 4

$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.

Solution 5

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.

View more solutions

Comments

[styfle]

You have two options: 1)Use two search bars, first-name and last-name and pass both variables to your current query string. 2)Use one search bar and assume a space separates the first from the last name and split. And as a bonus option you could change the query to combine first and last name and just use your single variable name.

[Chandresh M]

there is no need to put that type of query dude..Just use explode function and get what do you want..Thx.

[FooBar]

Sorry, but this is a horrible solution. Please simply do: WHERE CONCAT(f_name, ' ', l_name) LIKE '%$search%'

[aagjalpankaj]

Use COALESCE in case if the columns contain null value.