PHP / MySQL排序项首先如果“word”存在，那么其余的ASC？

Question

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 :

• the keyword may or may not be present
• the keyword may be anywhere
• I must also find the search keyword alone, in this example "carrot" so that "carrot cake" is still in my results.

Thanks a lot!

Joel

Answer 1

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.