根据关键字外观对搜索结果进行排名

Question

I am creating a search function in PHP/mySQL.

I need to be able to search for several keywords in several fields, and then rank the results by how many keywords match, and where the keywords match (for example, it should rank higher for keywords appearing in the post_title when compared to the post_content, all other things being equal.

I then need it to fall back onto ascending post date, all other things being equal.

Ideally, I could do this in the mySQL query. What methods are available to me?

Answer 1

If you use MyISAM you can set a fulltext index on your text fields.

See this question for a howto: Keyword search using PHP MySql?

Now you can do a query like:

SELECT 
  MATCH(`data`) AGAINST('word1 word2 word3') AS relevance  
  , field1
  , field2
FROM table1
WHERE MATCH(`data`) AGAINST('word1 word2 word3')
ORDER BY relevance DESC

You can repeat the MATCH AGAINST clause in your select, where it will give you a relevence number.
You need to restate it in your where clause. But luckily you can use an alias in your group by, having and order by clauses.