Yash Sharma
Yash Sharma

Reputation: 31

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?

Upvotes: 0

Views: 1448

Answers (3)

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

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

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can easy use this query:

$SQL = "SELECT * FROM users WHERE username LIKE :username OR name LIKE :name ORDER BY name LIKE :name DESC, uniqueid DESC LIMIT 6"; –

Upvotes: 0

Eugene Yarmash
Eugene Yarmash

Reputation: 149776

You could order the results by the index of $query in username and name:

SELECT * FROM users
    WHERE username LIKE :username OR name LIKE :name
ORDER BY uniqueid DESC, INSTR(:query, username), INSTR(:query, name) LIMIT 6

Note, however, that ordering by results of a function requires full table scan in MySQL.

Upvotes: 1

Related Questions