How to sort MYSQL fulltext search results by relevancy

full-text-search, mysql, php, sorting

Solution

SELECT * from vocabulary 
WHERE translation like 'word'  
union all
SELECT * from vocabulary 
WHERE translation LIKE '%word%' and translation not like 'word'

will list exact matches first

Problem

I am relatively new to MYSQL and have had an issue that has been bugging me for a while. I've tried googling all over the place for the answer, but have unable to find an acceptable solution as of yet. Here is the query I am running currently to find the best possible match for a given search term: ``` $query="SELECT * from `vocabulary` WHERE translation = 'word' OR translation LIKE '%word%'"; ``` The results it returns are comprehensive in that they include all relevant rows. However, they are not sorted in any particular order, and I would like to have the ones with an exact match displayed first when I print results in PHP. Like this: 1 | word <-exact match 2 | crossword <- partial matches sorted alphabetically / 3 | words 4 | wordsmith Thank you very much in advance for your assistance. -macspacejunkie

Original source