Fulltext search only returns exact matches

Question

I have the following script

select c.id 
from ".TBL_COUPONS." as c 
inner join ".TBL_BUSINESS." as b 
on c.business_id = b.business_id 
inner join ".TBL_BLOCATION." as l 
on c.business_id = l.business_id 
where 
(match(c.name) against ('$search') 
    or 
match (b.name,b.category,b.subcat) against ('$search')) 
and l.zip = '$zip'

why would this only return exact matches? For example, if i $search for locksmith nothing comes up. but if i search for locksmiths I'd get two results. (both searches included $zip = '75061')

Answer 1

Is your table ok with the fulltext restrictions?

Answer 2

This is just how full text match works. It doesn't know about plurals and matches entire words. The issue comes up from time to time. Occasionally when you see a generated suggestion "Did you mean ..." it's a page trying to solve this exact problem.

Answer 3

One way to do it is to replace the last few characters with a wild card and do the MATCH () AGAINST in boolean mode

The search term "locksmith" should be changed in php to "locksmith*" and your code would be something like this

match(c.name) against ('$search' IN BOOLEAN MODE)

For general purposes you should remove 's', 'ed', 'ing', etc. from the words in the original search term and add the wild card * to the end.