Order mysql search results by # of times words occur
Ok, I have a large set of data in my table which is as follows (its actually larger)
userid year model color transmission interiorcolor wheels ------------------------------------------------------------------- 1 2008 Fiesta Red MT Black Stock 2 2008 Monaro Red AT Black Stock 3 2005 F-150开发者_StackOverflow中文版S Blue AT Black + Red Stock 4 2008 Fiesta Green MT Black Stock
Now, there is a search box which allows users to input any keywords they want, be it the year, model, color, transmission, etc.
Right now my search can find all results which hit on any keyword the user enters, but it's not very relevant as Mysql will sort based on hits alone.
I need to have the search results sorted by the # of words in the search query that hit on items.
For example if someone types in "2008 Fiesta", he should get record #1 and #4 in his results before #2 --- because records #1 and #4 contain two of his keywords vs. record #2 which only contains one of his keywords.
Thanks!
You should consider using Sphinx alongside MySQL for relevance ranking.
精彩评论