mysql multiple queries with php

Question

I am trying to have a search result for someones name exploded then the first name and last name each search against each field 'first' and 'last'.

But need to leave room what if someone search for first last midle name like = $who = James David Smith it will exploded it and search each of the names against the first column and the last column. Incase the user inputs the name lastname first or adds a middle name.

Here is what i have so far but im stuck. Help. Please.

<?

$search = explode(' ', $who);

$limit=verify($get['page'])?" limit ".($get['page']*10).",10":" limit 10";

$q="select * from users where (first IN('%$search[]%') or last IN('%$search[]%') or full_name LIKE '%".$get['who']."%') AND (city LIKE '%".$get['where']."%' or province LIKE '%".$get['where']."%') order by province";        

$rows=get_recordset($q);

if(count($rows)){
    foreach($rows as $row){

echo $q;

?>

Answer 1

I am not sure I got you but if what you mean is :

When you have 3 names then - check all names against all three fields (first,last,fullname).

When you have only 2 names then - check first name against first field and last name against last field

then this should get it done:

$search = explode(' ', $who);
if (count($search) >2) { // in case we do got a middle name - 
    $whereNameStringsArr = array();
    foreach ($search as $val) {
     $whereNameStringsArr[] =  " first LIKE '%$val%' 
         OR last LIKE '%$val%' 
         OR full_name LIKE '%$val%' ";
    }
    $whereNameClause = implode(" OR ",$whereNameStringsArr);
} else { // in case we don't got a middle name
    $whereNameClause =  " first LIKE '%{$search[0]}%' 
       OR last LIKE '%{$search[1]}%' ";
}
$limit=verify($get['page'])?" limit ".($get['page']*10).",10":" limit 10";
$q="select * from users where  ($whereNameClause) 
       AND (city LIKE '%".$get['where']."%' or province LIKE '%".$get['where']."%') 
       order by province
       $limit";        

$rows=get_recordset($q);
....

Of course - make sure you validate all data you get from user's input

Answer 2

So why not do a fuzzy search? When you explode the data, do something like this:

$name = $_POST['name'] // John Michael Smith

$first_name = substr($name, 0, strrpos($name, ' ')); //Returns everything before last space, ie John Michael

$last_name = strstr($name, ' '); //Returns everything after first space, ie John Michael

Now you just run the mysql query but set it to look for anything that contains the above values (substring search) using LOCATE:

$results = mysql_query("SELECT * FROM names 
                WHERE LOCATE(firstname, '$first_name') > 0 OR 
                      LOCATE(lastname, '$last_name')   > 0
                ORDER BY lastname

I'll assume you know what to do with the results.

---

Quick Update, If you are worried about users entering in Last Name First Name Middle, or what have you, you could always use the above but do a locate on the entire string:

$results = mysql_query("SELECT * FROM names 
                WHERE LOCATE(firstname, '$name') > 0 OR 
                      LOCATE(lastname, '$name')   > 0
                ORDER BY lastname

This will check both columns for any instances of any of the string. You should probably (after sanitizing the string, as was rightly suggested already) also remove any commas (but not hyphens) in case someone enters last,first or what have you. replace them with spaces and then reduce all multi-spaces to one space to be double sure.

---

Sorry about that. Here is the way to split the names (by blanks). There as a problem with my substr syntax:

 $name = $_POST['name'] // John Michael Smith

 $first_name = substr($name, 0, strrpos($name, ' ')); //Returns everything before last space, ie John Michael

 $last_name = strstr($name, ' '); //Returns everything after first space, ie John Michael