Search code examples
phpmysqlsearchsearch-engine

MySQL search in PHP based on keyphrases

I am writing a prototype auction/listings project in PHP (MySQL backend).

I have a listing table (listings), and a keyphrase table (listings_keyphrases) which has multiple keyphrases describing each item. Each listing has multiple keyphrases (as many as necessary), but each keyphrase is character-limited to 20 characters.

So in listings_keyphrases there could be, say, 6 rows for a single listing, each with a different keyphrase. Keyphrases in the listings_keyphrases table are ordered.

I want to implement a search function that:

  1. Searches all the keyphrases related to a particular item
  2. Sorts results based on:
    • phrasing (if a search phrase matches exactly with a specific keyphrase)
    • keyphrase order (I presume that if a search matches a keyphrase at position 1 that is is more relevant).

I've looked into MySQL FULLTEXT and a few other options but haven't worked out what's the best way forward. Any ideas?

Solution

  • I would try one of the following search engines:

    http://lucene.apache.org/solr/

    http://sphinxsearch.com/

    Both of them are open source, well documented and easy to use.