I am trying to code the relevency features of a basic search engine that searches the database of a website. Lets take the example of Yelp. (Direct link to page http://www.yelp.com/search?find_desc=chinese&ns=1&find_loc=New+York%2C+NY)

Question 1开发者_运维百科

How do they determine a 'Best Match'? I'm guessing they search for all entries containing the keyword at least once, then sort by the number of times the keyword appears in the title/tag/description.

Question 2

For sorting by 'Highest Rated' and 'Most Reviewed', they search for all entries containing the keyword, then sort by average ratings and number of reviews respectively.

Question 3

I'm using CodeIgniter with MySQL and Active Records. Lets say my table 'products' has the columns 'id, name, categories, tags, description'.

• How do I do a query that returns all rows with the keyword appearing in one of these columns, and
• how do I count count the number of times the keyword appear in one of these columns in the MySQL query.

Sorting by relevancy, specially when it involves keywords/texts is not a simple task. When you deal with really large datasets like Yelp, you need specialized full text search engines like Sphinx http://sphinxsearch.com/

Sphinx and similar search engines would allow you to sort (among other things) your records based on number of keyword matches in the full record

If your recordset is small then i think you can get away with doing a rather simple relevancy search by using a complex order by clause which uses LIKE