Recently I've been stumped with searching a mysql database for exact words. I've tried a number of strategies I found online, but none worked exactly as needed.
The REGEXP didn't work for me at all (probably because I didn't really understand it in the first place). The MATCH/AGAINST would work, but only sometimes. The LIKE worked, but it included more words than what I wanted.
The solution I came up with was to create a chain of subqueries. The first uses the LIKE query to gather up all the rows that are close to the word and then does a REGEX on those rows to find exact matches. This solution feels a bit heavy so I only recommend it where necessary.
My solution is as follows:
SELECT * FROM $table AS allrows INNER JOIN ( SELECT `id`,`text` FROM ( SELECT `id`,`text` FROM $table WHERE (text LIKE '%$word%') ) AS `likerows` WHERE (`text` REGEXP('^.*$word .*$')) ) AS `regexrows` ON allrows.id=regexrows.id
Here are my previous attempts that may work for you. I suggest trying them out as they are a bit lighter weight and will certainly help out where they can.
WHERE text REGEXP '[[:<:]]($word)[[:>:]]'
WHERE MATCH(text) AGAINST('$word') // using mysql fulltext on text
WHERE text LIKE '%$word%'
Hope this helps someone. Drop a comment or email me if this doesn't work for you.