Order results by “Best Match” SQL

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?

Answer

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;