15Kanuraj Beri 15Kanuraj Beri - 5 months ago 14
MySQL Question

How do I sort MySQL search results be relevance?

I'm using PDO to search a MySQL table for search results. My query looks like this

$sql = "SELECT * FROM users WHERE username LIKE :username OR name LIKE :name ORDER BY uniqueid DESC LIMIT 6";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':username', '%'.$query.'%');
$stmt->bindValue(':name', $query.'%');


What I'm trying to accomplish is that in my results array, the results that match like this:

$query.'%'


Should appear before results that match like this:

'%'.$query.'%'


Is there a way to sort results by such relevance without two queries, preferably in pure SQL?

Answer

Well, Eugene's answer seems the most appropriate in this case. Provided below is an alternate solution to the problem in general which allows further customizations in terms of sorting the result :

If you have data like this :

Id  Name
1   Aabbee
2   Aabriella
3   Aada
4   bahlelah
5   cailelah
6   daielah
7   gaisha
8   Aisha
9   Aishath
10  dalelh
11  Eleasha

Then, you can use the following query:

select '1' as Relevance,ID, Name from MyTable where Name like 'el%' /*The results with the priority (or relevance) should be first */
union
select '2' as Relevance,ID, Name from MyTable where Name like '%el%' and Name not like 'el%' /* The lower priority results go here*/
order by Relevance

This will result in:

Relevance   ID    Name
    1       11    Eleasha
    2       2     Aabriella
    2       4     bahlelah
    2       5     cailelah
    2       6     daielah
    2       10    dalelh