Order by Relevance

eagles396

I was wondering if anyone knows how to order a mysql search result by relevance?

For example I have a database that contains a list of over 1000 retailers and if someone was to search for 'sky' I would like sky to appear before skype, kaspersky on the results page.

Another example would be if someone searched for 'Play' then I would expect Play to appear before Gameplay, Powerplay Direct etc

Any help would be appreciated

Thanks

bradgrafelman

Are you using full-text indexing/searching? Using the 'MATCH .. AGAINST ..' syntax should allow you to select the relevancy of each row returned (which you can then use in an ORDER BY clause).

eagles396

I've just tried the following sql and it appears to work apart from displaying gameplay in the results. I think that is because there is no space between game and play. Do you know why that is?

SELECT *, ( (1.3 * (MATCH(retailer) AGAINST ('play' IN BOOLEAN MODE))) + (0.6 * (MATCH(description) AGAINST ('play' IN BOOLEAN MODE))) ) AS relevance FROM retailers WHERE ( MATCH(retailer,description) AGAINST ('play' IN BOOLEAN MODE) ) HAVING relevance > 0 ORDER BY relevance DESC