Boolean Search Made Easy By MySQL

Call me naive, but I never dreamed that one day I would be able to set up my own Boolean search — let alone call it easy. However, once you have the basics of PHP and MySQL down, it turns out it’s just that. My inner librarian basks in wonder.

There are two essential parts.

Part one is adding fulltext indexing to the columns you will be searching. In MySQL use a query such as:

ALTER TABLE table ADD FULLTEXT INDEX (column1);

Part two is the query in PHP, which looks something like this:

$result = mysql_query(
  "SELECT *, " .
  "MATCH (" .
    "fulltextcolumn1, " .
    "fulltextcolumn2, " .
    "fulltextcolumn3)" .
    "AGAINST ('$keyword' IN BOOLEAN MODE) " .
    "AS Rank " .
  "FROM tablename " .
  "WHERE MATCH (" .
    "fulltextcolumn1, " .
    "fulltextcolumn2, " .
    "fulltextcolumn3)" .
    "AGAINST ('$keyword' IN BOOLEAN MODE)" .
  "ORDER BY Rank DESC");

The first MATCH is actually just to rank the results in the ORDER BY. You don’t really need any of those lines, but they are just too cool to skip in my opinion. Also, to get $keyword, chances are you will use a very simple form and POST the results.

For the particular Boolean commands to use, see the MySQL Reference Manual.

And for those wanting to learn more, this fabulous technique from Chapter 5 my all-time favorite PHP/MySQL book: Larry Ullman’s PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition).