I'm working on a project which requires the user to search for certain categories of products. The products have tags such as: T-shirt, Jumper, hat and I am currently displaying the results ordered by the amount of likes. Here's my SQL statement:
SELECT *
FROM products
WHERE keywords LIKE '%$query%'
ORDER BY likes DESC LIMIT 50;
But the issue is that if let's say someone searched "hat" but a more popular word was "chatterbox" in the database, it would come up with that first because it has more likes.
So to put it in pseudo code:
SELECT *
FROM products
WHERE keywords LIKE '%$query%'
ORDER BY "BEST MATCH" LIMIT 50;
and the result would ideally be:
2 results:
Hat: 26 likes,
Chatterbox: 234 likes,
Instead of:
2 results:
Chatterbox : 234 likes,
Hat: 26 likes,
So is there a way to do this?
In the ORDER BY clause check first if there is an exact match or (if you want it) keywords starts with '$query':
SELECT *
FROM products
WHERE keywords LIKE '%$query%'
ORDER BY keywords = '$query' DESC, -- 1st keywords with exact match
keywords LIKE '$query%' DESC, -- 2nd keywords which start with '$query'
likes DESC -- 3d likes
LIMIT 50;
You can try this:
(SELECT * FROM products WHERE keywords LIKE '$query')
union
(SELECT * FROM products WHERE keywords LIKE '%$query%' order by likes desc LIMIT 50);
With first query you get the exact match and with second, others matches order by likes.