Search with multiple terms

emkay

How do I code a MySQL search query with multiple terms that could be in any arbitratry order?

For example this is the very simple format I'm currently using

WHERE terms LIKE '%$search_terms%'

If I have an item called "Kings Schools Flight Test DVD", if I enter "Kings schools" into the search box, it shows up. But if I type "Kings flight test" it doesn't because of the way the LIKE wildcard works.

Any idea how to code the search query so that it takes into account all the term but regardless of the order or composition of the terms? I know I can explode the terms into an array and then break it up into multiple OR WHERE conditions but how would I sort it by relevance this way?

Derokorian

$search_terms = "Kings flight test"; 
$terms = explode(' ',$search_terms); 
$sql = "SELECT FROM table WHERE "; 
for( $i=0; $i<count($terms); $i++ ) { 
   $sql .= "terms LIKE '%$terms[$i]%' AND "; 
} 
$sql = substr($sql,0,-4);

makes every term a required match but in no particular order.

HTH

emkay

Thanks for your reply, do you know how I can sort it in order of relevance?

johanafm

If you do not use InnoDB (which I'd usually recommend using since it can be ACID compliant) and instead set the table to be of MyISAM type (which isn't ACID compliant), you can create a full text index on the VARCHAR / TEXT field and use something like

SELECT field1, field2, MATCH(somefield, optionally_more_fields) AGAINST('word1 word2 word3' IN NATURAL LANGUAGE MODE) AS relevance
FROM tables
WHERE MATCH(somefield, optionally_more_fields) AGAINST('word1 word2 word3' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC

But if you want to stick to InnoDB table type, you'd have to use an external indexing mechanism such as Sphinx, or just retrieve the results using like=... and then post process those results yourself in php. Sphinx is (supposedly) faster than MySQL's full text indexing though, from what I've heard.

You could of course also use some other DBMS that is both ACID compliant and supports full text indexing, such as PostgreSQL.
A note on InnoDB's ACID compliance: It is only ACID compliant as long as you don't use triggers, since MySQL doesn't execute triggers on cascading foreign keys, which could result in data being inconsistent. See mysql 5.5, create trigger

Note
Currently, cascaded foreign key actions do not activate triggers.