CODEWITHSUNDEEP
phpmysqlsqlsearchsql-order-by
user2415992
user2415992

Reputation: 521

How can I make a SQL query that selects all rows matching a certain string then weights them based on the number of matches

How can I create an SQL query that matches multiple strings with the LIKE operator, then ORDERS results by the number of matches it gets?

Also, is there a way to then store the number of matches, so that I can later do other calculations with that number in PHP?

I'm currently working with this code from another Stack Overflow answer

SELECT searchtopics_topicid, searchtopics_content FROM searchtopics 
    WHERE searchtopics_content
         LIKE '%string1%' OR searchtopics_content 
         LIKE '%string2%' OR searchtopics_content
         LIKE '%string3%' 
    ORDER BY 
         case when searchtopics_content LIKE '%string1%' then 1 else 0 end + 
         case when searchtopics_content LIKE '%string2%' then 1 else 0 end + 
         case when searchtopics_content LIKE '%string3%' then 1 else 0 end 
    DESC

Upvotes: 1

Views: 93

Answers (1)

Hart CO
Hart CO

Reputation: 34774

The above code looks like it should work just fine, to store the number you'd just add the ORDER BY case logic to the select list.

SELECT searchtopics_topicid
     , searchtopics_content 
     , case when searchtopics_content LIKE '%string1%' then 1 else 0 end + 
       case when searchtopics_content LIKE '%string2%' then 1 else 0 end + 
       case when searchtopics_content LIKE '%string3%' then 1 else 0 end AS hit_ct 
FROM searchtopics 
WHERE searchtopics_content LIKE '%string1%' 
   OR searchtopics_content LIKE '%string2%' 
   OR searchtopics_content LIKE '%string3%' 
ORDER BY 
     case when searchtopics_content LIKE '%string1%' then 1 else 0 end + 
     case when searchtopics_content LIKE '%string2%' then 1 else 0 end + 
     case when searchtopics_content LIKE '%string3%' then 1 else 0 end 
DESC

In MySQL you can simplify this all a bit:

    SELECT searchtopics_topicid
         , searchtopics_content 
         , searchtopics_content LIKE '%string1%' 
           + searchtopics_content LIKE '%string2%' 
           + searchtopics_content LIKE '%string3%' AS hit_ct 
    FROM searchtopics 
    WHERE searchtopics_content LIKE '%string1%' 
        + searchtopics_content LIKE '%string2%' 
        + searchtopics_content LIKE '%string3%' > 0
    ORDER BY searchtopics_content LIKE '%string1%' 
           + searchtopics_content LIKE '%string2%' 
           + searchtopics_content LIKE '%string3%' DESC

Upvotes: 2

Related Questions