Clever Geek Handbook

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')

+2


source to share


3 answers


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.

+2


source


Is your table full text constrained ?



0


source


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.

0


source






More articles:


All Articles