CODEWITHSUNDEEP
phpmysqlsqldatabasesql-like
Charlie C
Charlie C

Reputation: 190

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?

Upvotes: 1

Views: 1428

Answers (2)

forpas
forpas

Reputation: 164089

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;

Upvotes: 3

TimLer
TimLer

Reputation: 1360

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.

Upvotes: 2

Related Questions