Trouble with MySQL: CONCAT_WS(' ', name_first, name_middle, name_last) like '%keyword%'

Question

hey folks, I'm setting up a keyword search across multiple fields: name_first, name_middle, name_last but I'm not getting the results I'd like. Here's the query:

    "SELECT accounts_users.user_ID, users.name_first, users.name_middle, users.name_last, users.company
   FROM accounts_users, users
   WHERE accounts_users.account_ID = '$account_ID' AND accounts_users.user_ID = users.id AND CONCAT_WS(' ', users.name_first, users.name_middle, users.name_last) LIKE '$user_keyword%'
   ORDER BY users.name_first ASC"

So, if I've got three names in the DB:

Aaron J Ban
Aaron J Can
Bob L Lawblaw

And if the user_keyword == "bob lawblaw" I get no result. If user_keyword == "bob L" then it returns Bob L Lawblaw. Obviously I can't force people to include the persons middle name in their keyword search but I'm stuck for the proper way to do this.

All help is greatly appreciated.

Answer 1

Potentially look into creating a full text index on those 3 columns and then doing a full text search on that.

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

##UPDATE##

if using INNODB, then maybe use php's split function on the string;

$myVars = split(" ",$user_keyword);

then add a select statement for each word (I would also use the concat in the select statment so youu dont have to reapeat it for each where statement)

$extraSQL = '';
foreach($myVars as $search) {
    $extraSQL .= " AND `my_concat` LIKE '%".$search."%'";
}

SELECT 
    `your_fields`, 
    CONCAT_WS(' ', users.name_first, users.name_middle, users.name_last) as `my_concat` 
FROM `your_tables`
WHERE `accounts_users`.account_ID = '$account_ID' . $extraSQL;