How to order results based on number of search term matches?
- by Travis
I am using the following tables in mysql to describe records that can have multiple searchtags associated with them:
TABLE records
ID
title
desc
TABLE searchTags
ID
name
TABLE recordSearchTags
recordID
searchTagID
To SELECT records based on arbitrary search input, I have a statement that looks sort of like this:
SELECT
recordID
FROM
recordSearchTags
LEFT JOIN searchTags
ON recordSearchTags.searchTagID = searchTags.ID
WHERE
searchTags.name LIKE CONCAT('%','$search1','%') OR
searchTags.name LIKE CONCAT('%','$search2','%') OR
searchTags.name LIKE CONCAT('%','$search3','%') OR
searchTags.name LIKE CONCAT('%','$search4','%');
I'd like to ORDER this resultset, so that rows that match with more search terms are displayed in front of rows that match with fewer search terms.
For example, if a row matches all 4 search terms, it will be top of the list. A row that matches only 2 search terms will be somewhere in the middle. And a row that matches just one search term will be at the end.
Any suggestions on what is the best way to do this?
Thanks!
