MySQL: Query to Search All possible words

I have a form with an input box where user can 开发者_Go百科specify their names, names can be two or more words eg John Smith or John Michael Smith.

I need to write a query which would return the records including ALL words in the submitted name. So query will return records which has all those name words but can have different order.

For example, if search string is John Michael Smith, query should be able to return records with names such as John Smith Michael, Michael Smith John, Smith John Michael or other combination with all those words there. As can be seen return only records which still has all words in name field but can have different order. It should not however return results which do not contain a name part for example John Michael should not be returned since it is missing Smith.

I can't figure out how to write a query for such requirement that I have. Please help.

Update

The answers provided so far return even the records that match John Michael also. I tried answers of @Marco, @abesto and @Eddie.

The problem still persists :(

You could try a fulltext search:

SELECT * FROM table WHERE MATCH (name)
AGAINST ('+part1 +part2 +part3' IN BOOLEAN MODE);

In this solution, '+part1 +part2 +part3' would be generated in PHP.

See the MySQL docs

mysql> select * from sof;
+--------------------+
| n                  |
+--------------------+
| John               |
| Smith              |
| Smith John         |
| Smith John Michael |
| John               |
+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM sof WHERE MATCH(n) AGAINST('+John +Smith +Michael' IN BOOLEAN MODE);
+--------------------+
| n                  |
+--------------------+
| Smith John Michael |
+--------------------+
1 row in set (0.00 sec)

You could use a query like this:

SELECT * FROM your_table
WHERE 
    name LIKE '%John%' AND 
    name LIKE '%Michael%' AND 
    name LIKE '%Smith%'      

Obviously the WHERE part must be built dinamically using user input.
There is something that could be wrong, but you have to decide it: if column contains something like "Michael Smith Johnas" this record is returned.

its quite easy dude.

$keyword=$_POST['keyword'];
$keywords=explode(' ',$keyword);
for($i=0;$i<count($keywords);$i++)
$where .= ($i ? ' and ' : ' where ')."`name` like '%{$keywords[$i]}% '";
$q="select * from $table ".$where;
$rs=mysql_query($q);

$search = $_POST['search'];
$keywords = explode(" ",$search);
$query = "SELECT * FROM $table WHERE songTitle LIKE '%".$keywords[0];       
  for($kw=1;$kw < count($keywords);$kw++) {
    $query .= "%".$keywords[$kw];
  }
$query .= "%' ";
//echo "query= ".$query."<br>";  // Check your query statement here
        
mysql_query($query, $connect) or die ($Error=mysql_error());

$name=$_POST['name'];  //save the search query in a variable
$query = "SELECT * FROM names WHERE name like %$name%";