Full text search only returns exact matches
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 $ look for a locksmith, nothing comes up. but if I was looking for locksmiths I would get two results. (both requests included $ zip = '75061')
One way to do this is to replace the last few characters with a wild card and execute MATCH () AGAINST in boolean mode
The search term "locksmith" should be changed in php to "locksmith *" and your code will be something like this
match(c.name) against ('$search' IN BOOLEAN MODE)
For general purposes, you should remove the words 's', 'ed', 'ing', etc. from the words in the original search query and add the wildcard * to the end.
Is your table full text constrained ?
This is how full-text matching works. He doesn't know about plurals and matches whole words. The problem comes up from time to time. Sometimes when you see the generated sentence "Did you mean ..." this is a page trying to solve this exact problem.