php mysql fulltext search order by relevance

Question

How to make a php fulltext search order by relevance?

SELECT * FROM table
WHERE MATCH (col1,col2,col3) 
AGAINST ('+$boolean' IN BOOLEAN MODE)
Order By relevance

I want to set the relevance, first should match col1, col2 then then match col3, for more words if col1, col2 finished match, then turn in col3.

Maybe I should set a percentage, like col1, col2 with 66% relevance and col3 with 34% relevance...

You might wanna take a look at http://stackoverflow.com/questions/547542/how-can-i-manipulate-mysql-fulltext-search-relevance-to-make-one-field-more-valu http://stackoverflow.com/questions/7115221/mysql-query-weight-based-search-engine — swordfish, Aug 29 '11 at 07:02

score 1 · Accepted Answer · answered Aug 29 '11 at 07:18

You can try something like this:

SELECT *, (MATCH(col1, col2) AGAINST('+$boolean' IN BOOLEAN MODE) * 0.66 + MATCH(col3) AGAINST('+$boolean' IN BOOLEAN MODE) * 0.34) AS relevance
FROM table
WHERE MATCH(col1, col2, col3) AGAINST ('+$boolean' IN BOOLEAN MODE)
ORDER BY relevance DESC

php mysql fulltext search order by relevance

1 Answers1