Say I have a table like this:
Table: businesses
+----+------------------------+
| id | name |
+----+------------------------+
| 1 | Carolyn Wong Cakes |
| 2 | Cupcakin Cackes Shop |
| 3 | Wong Cackes Shop |
| 4 | Indie Cakes & Pastries |
+----+------------------------+
I want to get the best matched result
while I search for some words inside name.
for example I want to search: Wong Cackes Shop
which is 3 words.
I use this query for above search:
SELECT * FROM businesses WHERE (
name LIKE '% Wong %' OR
name LIKE '% Cackes %' OR
name LIKE '% Shop %'
)
I expect record 3
to be the first matched result but the result is:
Carolyn Wong Cakes
// with 2
matching words Cupcaking Cackes Shop
// with 2
matching words Wong Cackes Shop
// with 3
matching wordsHow can I search words and get records with most matched result on top of other results ?
Like this:
Wong Cackes Shop
// with 3
matching words Cupcaking Cackes Shop
Carolyn Wong Cakes
EDIT: My word-boundaries
LIKE method also has a problem. it won't get results which start or end with one of 3 words. because of those spaces
in LIKE '% word %'
for example:
Wong[space]
// does not match
[space]Wong[space]
match
[space]Wong
// does not match
Thanks.
following should do the trick
SELECT
name,
(( name LIKE '%Wong%')+( name LIKE '%Cackes%') +(name LIKE '%Shop%')) as total
FROM businesses WHERE (
name LIKE '%Wong%' OR
name LIKE '%Cackes%' OR
name LIKE '%Shop%'
)
ORDER BY total DESC
You can even delete the where clause here, since you're just interested in the total number of hits.
In order to overcome the problem of the spaces, just eliminate them in the like-clauses. %wong%
will match [space]wong
OR [space]wong[space]
or wong[space]