MySQL Fulltext Boolean Searches

Recently, the jQuery team released jQuery UI tools, a "fully themed interaction and widget library built on top of jQuery". I wanted to experiment with the new tools, and found tablesorter to be the most interesting initially. One thing I decided to create was a basic PHP search function on a MySQL database, with results able to be sorted by various field (column) elements with tablesorter.

MySQL has come a long way in its full-text boolean search capabilities, with v4.0+ having the most current feature set. Since I already had my data in MySQL, I simply had to run an ALTER statement on the table to add full-text indexing/searching: ALTER table ADD FULLTEXT(column1,column2,etc)

(Note: This works on MyISAM tables / VARCHAR, TEXT, and CHAR type fields)

To query the database and get boolean matches, you use WHERE MATCH(column1,column2,etc) AGAINST ('search text' IN BOOLEAN MODE) in your query.

If your desired search was in $search_string, the PHP code would look something like this:

$query = "SELECT * FROM table WHERE MATCH(column1,column2) AGAINST ('$search_string' IN BOOLEAN MODE)"
$result = mysql_query($query) or die("Query failed: " . mysql_error());

Leave a Reply

Your email address will not be published. Required fields are marked *