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 like Play to appear before Gameplay, Powerplay Direct etc

 

Any help would be appreciated

 

Thanks

[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. Does anyone 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

[JustLikeIcarus]

You are going to need to use wildcards in the AGAINST clause if you want to return words that contain the specified string such as doing the following to return records that contain "play" either as a word its self or as part of a word like "gameplay".

 

AGAINST ('*play*' IN BOOLEAN MODE)

[eagles396]

Thanks for your help but I tried using the wildcards and it does not seem to display gameplay, if I put a space between game & play then it displays for some reason. Do you know any way to overcome this problem?