I have a food database and I'm trying to implement a simple search that would put first items that are "raw" or "least processed".
For example, when I'm searching for "carrot", I really don't want to have to sort through all the processed food (carrot cake, etc), fast food (Wendy's carrot big mac, etc). I want the first items to be "carrots, raw", "carrot, boiled", "carrot frozen", "carrot, canned", etc.
Is there a way to do that in MYSQL so that I sort my items by "presence of keyword" first (let's say : "raw", "fresh", "boiled", "frozen"...) and then all other occurrences after?
Note :
Thanks a lot!
Joel
This might work for you:
SELECT * FROM foods
WHERE foods.title LIKE '%carrot%'
ORDER BY foods.title LIKE '%raw%' DESC, foods.title
The reason it's DESC is because that will return 0 or 1, and you want the results that returned 1 to be sorted first.
Why not have and additional field called "processed" that stores a 1 if the food is processed. then just sort by processed and name.
A natural word search returns records in order according to relevancy, so if you added a FULLTEXT index to the foods.title column, you could simply perform a search like this:
SELECT *
FROM foods
WHERE MATCH (title) AGAINST ('carrot raw fresh frozen canned')
AND title LIKE 'carrot%'
The additional LIKE criteria filters out results that don't have the word carrot at the beginning like 'fish, canned'.
FULLTEXT indexes can only be used with MyISAM tables currently, though soon (v5.6+) you can use them with InnoDB also.