SQL: Query to Search All possible words

I have a form with an input box where the user can specify their names, the names may be two or more words, eg John Smith or John Michael Smith.

I have to write a query that returns records containing all words in the name presented. So query will return records that has the name of all those words, but may have different order.

For example, if the search string is John Michael Smith, the query should be able to return records with names like Michael John Smith, Michael Smith John, John Smith Michael or some combination of al开发者_运维知识库l these words there. As can be seen only return records that still has all the words in the name field, but may have different order. However, it should return results that do not contain part of the name, for example, John Michael should not be returned because it does not have Smith in there.

I tried query like this:

SELECT id, name FROM users WHERE
     name LIKE '%Michael%' &&
     name LIKE '%Smith%' &&
     name LIKE '%John%'

The same problem happens with:

SELECT * FROM users WHERE MATCH (name)
    AGAINST ('+Michael +Smith +John' IN BOOLEAN MODE);

Both queries also returns John Michael instead of records that contain all three words :(

I can not figure out how to write a query to the requirement so that I have. Please help.

Use a fulltext index. That's the easiest/quickest method. Otherwise you're stuck parseing your search string, converting

John Michael Smith

into

SELECT ... WHERE (name LIKE '%John%') OR (name LIKE '%Michael%') OR (name LIKE '%Smith%')

which very quickly gets painful to do, and VERY slow to perform, as LIKE %..% searches cannot use indexes.

Note that Fulltext indexes are currently restricted to MyISAM tables, so if you're on InnoDB, you'll have to use workarounds (parallel tables in MyISAM format to hold the searchable data with triggers to keep the two copies synchronized). Apparently Fulltext for InnoDB is FINALLY in the development pipeline, but it's not here yet.

If you save the name in the field 'name' your sql query should be like this

first explode the string by space

$params = explode(' ', $input_name);

then append this on your query:

    $i = 0;
    $q= '';
    foreach($params as $p){
        $p = strtolower($p);
        if($i > 0) $q.= " and ";
        $q.= "LOWER(name) like '%$p%'";
        $i++;
    }

$query="select .... where ".$q; 

the lower things are needed to make it case insensitive.

Whatever variable you are setting up as the value of this input, you want to use explode on: http://php.net/manual/en/function.explode.php

$names = explode(' ', $userinput);

with a space as your delimiter. This will give you an array with the 3 different elements (or however many there are).

$names[0] = "John";
$names[1] = "Michael"
$names[2] = "Smith";

Now you need to run them through strtolower so that case is not an issue.

http://us3.php.net/manual/en/function.strtolower.php

 foreach ($names as $name)
{
  $name = strtolower($name);
}

Now we have

$names[0] = "john";
$names[1] = "michael"
$names[2] = "smith";

Next you need to build a function that reassembles these variables in all possible orders. But because you might have 1,2,3, or 4 elements in your array, you don't know how many queries you will need. So you check:

$numberofnames = count($names);

Then create a function to build your different name combinations:

$namecombo = "";

function createNames($numberofnames)
{
 for($i=0; $i <= $Numberofnames; $i++)
 {
  $namecombos[i] .= $names[i];
 }

So. This is an incomplete answer because here we run into a problem. You need a for loop to generate the name combos, but you also need a for loop generating how many for loops you need. Not something I can think of a way to code. But... Hope this gets you closer to an answer.

 for($i=0; $i <= $Numberofnames; $i++)
 {
  $secondnamecombo[i] = $names[$i - 1]
  etc...
 }
}