As we discussed in class, there are two ways of implementing search functionality on your sites using built-in MySQL commands.
The first technique takes advantage of the WHERE LIKE clause in SQL. The second technique takes advantage of the FULLTEXT search feature built into MySQL.
Both these methods, of course, presume that the content you want to be searching is stored in a MySQL database. If the content you want to search is hard-coded in an XHTML page, rather than stored in a database, then these methods will not be useful to you, and you would do better to use Google Custom Search, Yahoo Search, or some other service that provides search functionality for regular XHTML documents.
Search using WHERE LIKE
This is the simplest form of MySQL search. You can see this example live in your browser here.
Let’s assume that we have a table, called “abloomberg_blogposts” that stores a bunch of blog posts. The table has the fields, “id”, “title”, “message”, and “created”.
As you know, the SQL query to read all the rows from the table would be:
SELECT * FROM abloomberg_blogposts WHERE 1
The “WHERE 1″ part of the query tells MySQL to return all the rows in the table.
To read only those rows that had the title “cat”, we could run this query:
SELECT * FROM abloomberg_blogposts WHERE title LIKE '%cat%'
In this case, the “WHERE title LIKE ‘%cat%’” part of the query tells MySQL to return only those rows in the table where the “title” field contains the string, “cat”. The “%” percent signs indicate wild card that matches any character or bunch of characters.
Because of the use of the wild cards before and after the search term, this query will match any of the following strings:
cat
catamaran
bobcat
concatenate
If we only wanted to match those rows where the “title” field began with the word, “cat”, we could use the following query with only a wild card after the search term:
SELECT * FROM abloomberg_blogposts WHERE title LIKE 'cat%'
Likewise, if we wanted to only match those rows where the “title” field ended with the word, “cat”, we could use this query:
SELECT * FROM abloomberg_blogposts WHERE title LIKE '%cat'
Finally, if we wanted to search more than one field, we can combine multiple queries together using the UNION keyword in SQL. For example, if we wanted to search both the “title” and the “message” fields at once, we could use the following query:
SELECT * FROM abloomberg_blogposts WHERE title LIKE '%cat%' UNION SELECT * FROM abloomberg_blogposts WHERE message LIKE '%cat%'
We could chain together as many queries as we like using the UNION keyword, and MySQL will return the combined results of all of them.
Assuming you are running queries like this in PHP, rather than directly in SQL, you will probably want to store the search term in a variable rather than hard-code it in the SQL command. Replacing the search term with a PHP variable called $searchTerm will make your queries look something like this:
$myQuery = "SELECT * FROM abloomberg_blogposts WHERE title LIKE '%{$searchTerm}%' UNION SELECT * FROM abloomberg_blogposts WHERE message LIKE '%{$searchTerm}%'";
Search using FULLTEXT search
The alternative method of searching in MySQL is to use the FULLTEXT search feature built into MySQL. You can see this example live in the browser here.
This method allows you to search more than one field in a single query. It also allows you to be a bit more flexible with your search, and it is smart enough to ignore common words like “a”, “the”, “of”, etc.
Here is an example of using MATCH AGAINST to search for the word “cat” in both the “title” and “message” fields of our “abloomberg_blogposts” table:
SELECT * FROM abloomberg_blogposts WHERE MATCH (title, message) AGAINST ('+"cat"' IN BOOLEAN MODE)
As you can see, the fields you want to search go inside the MATCH(…) section, and the search term you want to find goes inside of the AGAINST(…) section of the query.
The plus sign, “+”, in the query indicates that you want to search for all rows that contain the word “cat”. If you replaced this with a minus sign, “-”, the query would return all rows that did not contain that word.
Assuming you are running this query in PHP, rather than directly in SQL, you will most likely want to replace the search term with a variable. And your PHP code would look something like this:
$myQuery = "SELECT * FROM abloomberg_blogposts WHERE MATCH (title, message) AGAINST ('+\"{$searchTerm}\"' IN BOOLEAN MODE)";
Important Note: In order to use this FULLTEXT search feature, MySQL requires you to create a FULLTEXT index on any text field (including varchar and text fields) that you want to use in your search. The FULLTEXT index is a way of optimizing those fields for search.
If you look at the structure of the abloomberg_blogposts table in phpMyAdmin, you will see that there is a FULLTEXT index on the title and message fields, which allows this example search to work.
Indexes used by the abloomberg_blogposts table
Creating a FULLTEXT index in phpMyAdmin
You must create such an index on the fields you intend to search in your own code in order to use the FULLTEXT search technique. Creating such an index is simple in phpMyAdmin. First go to the “Structure” tab of the table you will be searching. In the Indexes box on that page, in the “Create an index on … columns” text field, fill in how many fields you wish to create the index for (i.e. how many fields you will be searching), and then click Go. The next page will ask you which fields you want to index (i.e. which fields you will be searching), and it also asks you to name this index. You can name the index anything, but it’s probably best to use a name that includes the field names the index uses. In my example, the index is named, “title_message”.
More information on FULLTEXT search
http://www.onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html
Related posts:
- Class 10 – Debugging PHP and MySQL
- Class 9 – Adding search functionality to your site
- Class 11 – Introduction to Search Engine Optimization