Reputation: 31
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
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
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
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