CODEWITHSUNDEEP
phpmysql
user1079160
user1079160

Reputation: 851

mysql: order by match

for looking up matching keywords in mysql i use

SELECT * FROM `test` WHERE `keywords` REGEXP '.*(word1|word2|word3).*' LIMIT 1

I want to order them by the most matching keywords in the keywords column to give the best answer.For example

Keywords /////////////// Response
word1,word2 /////////// test1
word1,word2,word3 / test2

I want the response to be test2 with the query given.
How can i order the results my the most matching keywords?

Upvotes: 1

Views: 1026

Answers (1)

Johan
Johan

Reputation: 76567

SELECT 
  (keywords REGEXP '.*(word1).*')
  +(keywords REGEXP '.*(word2).*')
  +(keywords REGEXP '.*(word3).*') as number_of_matches
  ,keywords
  ,field1
  ,field2
FROM test 
WHERE keywords REGEXP '.*(word1|word2|word3).*'
ORDER BY number_of_matches DESC 
LIMIT 20 OFFSET 0

Upvotes: 2

Related Questions