CODEWITHSUNDEEP
mysqlsql-like
Bob van Luijt
Bob van Luijt

Reputation: 7588

Mysql sortation on multiple LIKE's

There are a lot of topics on sortation (like: Order Results By Occurrence) but these are all for one value.

I have a search field that people use with keywords; simply said the queries generated look like:

1 word: 

SELECT * 
FROM  `meta` 
WHERE (`keywords` LIKE  '%bike%')

2 words: 

SELECT * 
FROM  `meta` 
WHERE (
`keywords` LIKE  '%bike%'
OR  `keywords` LIKE  '%yellow%'
)

etc...

What I would like to do is sort the result on the most found keywords. How would I do this for an unknown amount of keywords LIKE's

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Here is the general way to sort by the number of keyword matches in MySQL (using like):

SELECT * 
FROM `meta` 
ORDER BY ((`keywords` LIKE  '%bike%') +
          (`keywords` LIKE  '%yellow%') +
          . . .
         ) desc;

If you want to handle a flexible number of keywords, then you should use an appropriate relational data structure. Storing keywords in a single field (probably comma-separated) is not the best approach. You should have a separate table with one row per keyword.

EDIT:

To add in the number of keywords found, the expression can be put in the select statement:

SELECT m.*,
       ((`keywords` LIKE  '%bike%') +
        (`keywords` LIKE  '%yellow%') +
        . . .
       ) as NumKeywordsFound
FROM `meta` m
ORDER BY NumKeywordsFound desc;

You can also add a having clause to specify that at least one is found:

SELECT m.*,
       ((`keywords` LIKE  '%bike%') +
        (`keywords` LIKE  '%yellow%') +
        . . .
       ) as NumKeywordsFound
FROM `meta` m
HAVING NumKeywordsFound > 1
ORDER BY NumKeywordsFound desc;

If you want to find the number of times a keyword is found in each expression:

 select m.*,
        length(replace(keywords, 'bike', 'bike1')) - length(keywords) as NumBikes,
        length(replace(keywords, 'yellow', 'yellow1')) - length(keywords) as NumYellows
FROM `meta` m

Upvotes: 1

Related Questions