filter mySQL database with multiple words in any order in Concatenated field

Question

I have Concatenated in mySQL to produce a field that I can search with. This contains animal as well as owner names and addresses for a client database. I need to be able to search by animal name, owner names and postcode in any order. Thus if if my name is john smith and i own a dog called Fido and live in postcode AB1 2CD, I want the search to be yield results if any of the following are entered:

"john fido AB1" "john AB1" "AB1 john" "AB1 fido" "fido 2CD" "2CD" "fido" etc... i.e any of the fields in any order, and also not complete words either so "john fido AB1" should yield the same result as "jo fi AB1"

I currently have this PHP code, taking a single text field on my search page, exploding then imploding it to add % between the search terms:

$list = explode(' ',$_GET["q"]);  
$q = implode('%%', $list);
if (!$q) return;  
$sql = "SELECT DISTINCT owner.AddressPrim, owner.PostcodePrim, 
owner.OwnerSurnamePrim,owner.OwnerForenamesPrim,owner.OwnerID
FROM owner
Inner Join patient ON owner.OwnerID = patient.OwnerID
WHERE CONCAT_WS(' ',owner.AddressPrim, owner.PostcodePrim,
owner.OwnerForenamesPrim,owner.OwnerSurnamePrim,patient.AnimalName) LIKE '%$q%'";

This works for "AB1 john" and "john fido" but not "fido john" as it is out of order in the concatenated field.

Any help greatly appreciated

Answer 1

I think you're going to have to split the keywords and add a query for each keyword in the string of keywords.

So first (in PHP), split the query string and dynamically generate your SQL query, then send it to the database. Here's some pseudocode to show you what I mean:

$keywords = explode(' ', $q);
$sql = "SELECT DISTINCT owner.AddressPrim, owner.PostcodePrim, 
    owner.OwnerSurnamePrim,owner.OwnerForenamesPrim,
    owner.OwnerID

    FROM owner 
    Inner Join patient ON owner.OwnerID = patient.OwnerID";

$first = true;

foreach($keyword in $keywords):
    if($first):
        $sql += " WHERE ";
        $first = false;
    else:
        $sql += " AND ";

    $escaped = mysql_real_escape_string($keyword);
    $sql += " CONCAT_WS(' ',owner.AddressPrim, owner.PostcodePrim,
        owner.OwnerForenamesPrim,owner.OwnerSurnamePrim,patient.AnimalName) 
        LIKE '%$escaped%'";

But do beware, this is not going to be anywhere near fast for the size of tables you'll probably encounter in daily operation. You may want to look into a better way of doing fulltext search, whether it means using a library or making a cross-reference table of keywords maintained by triggers.

Answer 2

MySQL's fulltext search (MyISAM tables only!) could be useful to you.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Answer 3

You can try this http://www.sphinxsearch.com/

You need dedicated server to run this thing, but if you have one, sphinx will easily solve your problem and your queries won't load database.