The PHP Anthology Volume I, Chapter 3 - PHP and MySQL

How do I search my table?

Some people are just impatient; rather than trawling your site with the friendly navigation system you've provided, they demand information now! Hence PHP developers like you and I are required to implement search features to provide visitors a "short cut" to find the information they want. In the days of storing all content in the form of HTML files, this could be quite a problem, but now that you're using a database to store content, searching becomes much easier.

Select What You LIKE

The most basic form of search occurs against a single column, with the LIKE operator:

SELECT * FROM articles WHERE title LIKE 'How %'

The % is a wildcard character. The above statement will select all articles in which the title begins with the word "How." MySQL also has support for POSIX regular expressions (the same as PHP's ereg functions). Using the RLIKE operator, we can compare a column using a regular expression:

SELECT * FROM articles WHERE title RLIKE '^How '

The above statement also selects every article in which the title begins with "How" followed by a space.

With some work, these operators provide everything needed to explore your data. Where the above approach becomes a burden is in performing a search against multiple columns. For example,

SELECT * FROM articles          
WHERE title LIKE '%how%' OR body LIKE '%how%'

For larger tables, this can require you to write some very complicated and unpleasant queries.

FULLTEXT Searches

MySQL provides an alternative that does most of the work for you—the FULLTEXT index. Indexes in a database are much like the index of a book; they provide a means to locate information within the database quickly from an organized list. A FULLTEXT index allows you to search a table for particular words.

FULLTEXT indexes were introduced to MySQL with version 3.23. The implementation at this point was fairly limited but still useful for basic searching, which is what I'll demonstrate here. In MySQL version 4.0.1, this functionality was extended to provide a full Boolean search mechanism that gives you the ability to build something like Google™'s advanced search features. FULLTEXT indexes also allow each result to be returned with a "relevance" value so that, for example, the results of multiple word searches can be displayed in terms of how well each result matches that user's particular search.

To take advantage of FULLTEXT indexes, you first need to instruct MySQL to begin building an index of the columns you want to search:

ALTER TABLE articles ADD FULLTEXT art_search (title, body, author)

Once you've done that, you need to INSERT a new record (or modify an existing one) to get MySQL to build the index. You also need at least three records in the database for FULLTEXT searches to work, because non-Boolean searches will only return results if the search string occurred in less than 50% of the rows in the table (if there are only two rows in the table, and your search matches one row, that makes 50%). One final thing to be aware of is that FULLTEXT searches will only match searches of more than three letters; the indexing mechanism ignores words of three characters or less, to avoid having to build a massive index. This is much like the index of a book; you'd be pretty surprised to discover in a book's index exactly which pages the word "the" appeared on!

Here's a basic FULLTEXT search:

SELECT * FROM articles          
WHERE MATCH (title,body,author) AGAINST ('MySQL');

This search will return all rows where either the title, body or author contained the word "MySQL."

Another use for FULLTEXT indexes is in a search which returns the relevance for each result. For example:

Example 3.26. 19.php (excerpt)          
         
// Select all rows but display relvance          
$sql = "SELECT          
         *, MATCH (title, body, author)          
       AGAINST          
         ('The PHP Anthology Released Long Word Matching')          
       AS          
         score          
       FROM          
         articles          
       ORDER BY score DESC";          
         
// Run the query, identifying the connection          
$queryResource = mysql_query($sql, $dbConn);          
         
// Fetch rows from MySQL one at a time          
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {          
 echo 'Title: '  . $row['title']  . '<br />';          
 echo 'Author: ' . $row['author'] . '<br />';          
 echo 'Body: '   . $row['body']   . '<br />';          
 echo 'Score: '  . $row['score']  . '<br />';          
}

The alias score now contains a value that identifies how relevant the row is to the search. The value is not a percentage, but simply a measure; 0 means no match was made at all. Matching a single word will produce a value around 1. The more words that match, the bigger the number gets, so a five word match ranking will produce a relevance score around 13. MySQL's relevance algorithm is designed for large tables, so the more data you have, the more useful the relevance value becomes.

Overall, MySQL's FULLTEXT search capabilities provide a mechanism that's easy to implement and delivers useful results.