How would I implement a simple site search with php and mySQL?

Question

I'm creating a site that allows users to submit quotes. How would I go about creating a (relatively simple?) search that returns the most relevant quotes?

For example, if the search term was "turkey" then I'd return quotes where the word "turkey" appears twice before quotes where it only appears once.

(I would add a few other rules to help filter out irrelevant results, but my main concern is that.)

Solution 1

Everyone is suggesting MySQL fulltext search, however you should be aware of a HUGE caveat. The Fulltext search engine is only available for the MyISAM engine (not InnoDB, which is the most commonly used engine due to its referential integrity and ACID compliance).

So you have a few options:

1. The simplest approach is outlined by Particle Tree. You can actaully get ranked searches off of pure SQL (no fulltext, no nothing). The SQL query below will search a table and rank results based off the number of occurrences of a string in the search fields:

SELECT
    SUM(((LENGTH(p.body) - LENGTH(REPLACE(p.body, 'term', '')))/4) +
        ((LENGTH(p.body) - LENGTH(REPLACE(p.body, 'search', '')))/6))
    AS Occurrences
FROM
    posts AS p
GROUP BY
    p.id
ORDER BY
    Occurrences DESC

edited their example to provide a bit more clarity

Variations on the above SQL query, adding WHERE statements (WHERE p.body LIKE '%whatever%you%want'), etc. will probably get you exactly what you need.

2. You can alter your database schema to support full text. Often what is done to keep the InnoDB referential integrity, ACID compliance, and speed without having to install plugins like Sphinx Fulltext Search Engine for MySQL is to split the quote data into it's own table. Basically you would have a table Quotes that is an InnoDB table that, rather than having your TEXT field "data" you have a reference "quote_data_id" which points to the ID on a Quote_Data table which is a MyISAM table. You can do your fulltext on the MyISAM table, join the IDs returned with your InnoDB tables and voila you have your results.

3. Install Sphinx. Good luck with this one.

Given what you described, I would HIGHLY recommend you take the 1st approach I presented since you have a simple database driven site. The 1st solution is simple, gets the job done quickly. Lucene will be a bitch to setup especially if you want to integrate it with the database as Lucene is designed mainly to index files not databases. Google custom site search just makes your site lose tons of reputation (makes you look amateurish and hacked), and MySQL fulltext will most likely cause you to alter your database schema.

Solution 2

Use Google Custom Site Search. I've heard they know a thing or two about searching.

Solution 3

Stackoverflow plans to use the Lucene search engine. There is a PHP port of this written for the Zend Framework but can be downloaded as a separate entity without needing all the ZF bloat. This is called Zend_Search_Lucene, documentation for which can be found here.

Solution 4

Your sql for that will look something like this (where you're trying to find quotes with 'turkey' in it):

SELECT * FROM Quotes
WHERE the_quote LIKE "%turkeyt%";

From there you can figure out what to do with whatever it spits out at you.

Be careful to properly handle cases where a malicious user might inject malicious SQL into your database, especially if you're planning on putting this on the www. If you're doing this for fun though, I guess it's just about what you want to learn.

If you're new to databases and sql, I recommend sqlite over mysql. Much easier to set up and work with, as in no set up. It'll get you around the potential headaches of having to install and set up mysql for the first time.

Solution 5

As an alternative to Sphinx and Lucene, a relatively simple search engine can be created using the Xapian library.

+ Supports many advanced search features (such as relevancy ranking)
+ Fast

- You would need to learn the API to create your interface
- Requires a php extension to be installed

Note also that Xapian stores its data in a separate index to mysql.

You might also be interested in Forage which is a wrapper for Solr, Xapian and Lucene.

The Xapian people also created the Omega search engine which is a frontend to Xapian, and can be called via cgi.

View more solutions