1

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?

Mild Fuzz
  • 29,463
  • 31
  • 100
  • 148

1 Answers1

3

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.

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
  • It says that full text must be turned on at table creation, is there a way around that? – Mild Fuzz Oct 04 '11 at 09:08
  • NO of course not, how can you do a fulltext search on a table that does not have a fulltext index. Do an `alter table add index ....` to add the fulltext indexes. – Johan Oct 04 '11 at 09:15
  • Sorry, so I can add an index after the table has been created? – Mild Fuzz Oct 04 '11 at 09:17
  • @MildFuzz, Yep no problem, it may take a long time for the `alter table` to finish on a big table, but you can change everything on the fly. – Johan Oct 04 '11 at 09:24
  • Yes, you can query the `information_schema` database about your table. See this question: http://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table – Johan Oct 04 '11 at 15:05