SQL Name and Sort
I have a website with store databases. A visitor to my site can search for stores by name, currently the SQL query is executed like this:
SELECT *
FROM tbl_shop
WHERE LOWER(`name`) LIKE LOWER(`%text1%`)
OR LOWER(`name`) LIKE LOWER(`%text2%`)
.....
OR LOWER(`name`) LIKE LOWER(`%textN%`)
This works fine, but the problem is that the results are not sorted by best match. Some store names contain very common words (for example, "Na Lavce"), and if you enter this string, all stores containing "% na%" (which is a lot) are shown in the results.
Because of this, the visitor can get a lot of results and sometimes cannot find what they are looking for because the correct result is, for example, on the fifth page of results. Any ideas how to fix this?
+3
source to share
1 answer
SELECT *
FROM tbl_shop
WHERE
LOWER(`name`) LIKE LOWER(`%text1%`)
OR LOWER(`name`) LIKE LOWER(`%text2%`)
.....
OR LOWER(`name`) LIKE LOWER(`%textN%`)
ORDER BY
CASE WHEN LOWER(`name`) LIKE LOWER(`%text1%`) THEN 1 ELSE 0 END +
CASE WHEN LOWER(`name`) LIKE LOWER(`%text2%`) THEN 1 ELSE 0 END +
.....
CASE WHEN LOWER(`name`) LIKE LOWER(`%textN%`) THEN 1 ELSE 0 END
DESC
If you want to rank by term order:
ORDER BY
CASE WHEN LOWER(`name`) LIKE LOWER(`%text1%`) THEN 1024 ELSE 0 END +
CASE WHEN LOWER(`name`) LIKE LOWER(`%text2%`) THEN 512 ELSE 0 END +
CASE WHEN LOWER(`name`) LIKE LOWER(`%text2%`) THEN 256 ELSE 0 END +
.....
CASE WHEN LOWER(`name`) LIKE LOWER(`%textN%`) THEN 1 ELSE 0 END
DESC
+1
source to share