Trouble with MySQL: CONCAT_WS(' ', name_first, name_middle, name_last) like '%keyword%'
- by AJB
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.
