CODEWITHSUNDEEP
phpmysqlsql-order-bysql-like
justin
justin

Reputation: 3

Order SQL results by number of LIKE matches from multiple columns

SELECT id 
FROM table 
WHERE fistname LIKE CONCAT('%',?,'%') 
   OR secondname LIKE CONCAT('%',?,'%') 
   OR middlename LIKE CONCAT('%',?,'%')");

$stmt->bind_param("sss", $first, $second, $middle);

I would like to be able to sort the returned results so that they will appear in order of the total number of matches.

For example:

  id | firstname |  secondname   |  middlename    
------------------------------------------------    
  1  |  Thomas   | Smith       |   John    
  2  |  Bob      | Smith       |   John

For vars $first = "Thomas", $second = "Smith", $middle="John", the results would be sorted in the order 1,2

For vars $first = "Bob", $second = "Smith", $middle="Peter", the results would be sorted in the order 2,1

etc...

Thanks!

Upvotes: 0

Views: 57

Answers (2)

forpas
forpas

Reputation: 164099

In Mysql a Boolean expression like: name like '%bob%' is evaluated as 1(true) or 0(false), so you can add all the like expressions:

order by
  (fistname like concat('%', ?, '%')) +
  (secondname like concat('%', ?, '%')) +
  (middlename like concat('%', ?, '%')) desc

But since you don't want to repeat the parameters in the WHERE clause and in ORDER BY, you can write it like this:

SELECT t.* FROM (
  SELECT *,
    (fistname LIKE CONCAT('%', ?, '%')) +
    (secondname LIKE CONCAT('%', ?, '%')) +
    (middlename LIKE CONCAT('%', ?, '%')) as matches 
  FROM table
) as t 
WHERE t.matches > 0
ORDER BY t.matches DESC

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

ORDER BY
       CASE WHEN firstname  LIKE CONCAT('%',?,'%') THEN 1 ELSE 0 END +
       CASE WHEN secondname LIKE CONCAT('%',?,'%') THEN 1 ELSE 0 END +
       CASE WHEN middlename LIKE CONCAT('%',?,'%') THEN 1 ELSE 0 END DESC

Upvotes: 1

Related Questions