Pars Pars - 22 days ago 5
MySQL Question

Get most matched result on top in mysql LIKE query

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:


  1. Carolyn Wong Cakes
    // with
    2
    matching words

  2. Cupcaking Cackes Shop
    // with
    2
    matching words

  3. Wong Cackes Shop
    // with
    3
    matching words



How can I search words and get records with most matched result on top of other results ?

Like this:


  1. Wong Cackes Shop
    // with
    3
    matching words

  2. Cupcaking Cackes Shop

  3. 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.

Answer

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]