Create your own fulltext searchengine with PHP and MySQL

Do you want to search the text stored in your MySQL database, but hasn’t figured out how to do it efficiently? Then a  full-text search engine is the perfect solution for you. From version 4.0 MySQL has been supporting full-text search, which makes it very easy to build a good performing, robust and very accurate search engine.

In this post you’ll learn how to build a basic full-text search engine using PHP and MySQL.

What is full text search

So what is full-text search. A full-text search makes use of indexes.  These indexes are setup on specific fields of a MySQL table, and optimizes the way that MySQL stores the records for that particular table.

If you have a database that that contains: name, price, description and picture of some sporting goods. When users perform a search, they will most commonly enter part of the product name or description.

When you setup an full-text index on the product name and description field in your MySQL database, MySQL automatically stores the records in a indexed format, which means that MySQL can search the data in the fields very fast and efficient in terms of server resources. Compared to the traditional LIKE command in MySQL there is a lot performance to gain by switching to full-text search.

Other characteristics and benefits of full-text are:

  • Searches are not case sensitive
  • Short words are ignored, the default minimum length is 4 characters.
  • Very common words like “i”, “in”, “on”, also  called stopwords are ignored. You can see a list of the standard MySQL stopwords here.
  • You can disable stopwords by setting the variable in the MySQL configuration.
  • Fulltext searches can only be made on “text” fields.
  • If a word is present in more than 50% of the rows, it will have a weight of zero. This means that there will be no search results. This is mostly a problem if you’re testing with a limited dataset
  • MySQL requires that you have at least three rows of data in your result set before it will return any results.
  • By default, your search query must be at least four characters long and may not exceed 254 characters.

Creating the script

First we need to create and insert some MySQL test data in the table search_test:

CREATE TABLE IF NOT EXISTS `search_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`description` text NOT NULL,
`price` int(11) NOT NULL,
`img` text NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `search_test` (`id`, `name`, `description`, `price`, `img`) VALUES
(1, ‘Nike running shoe’, ‘Fantastic nike running shoe. Comes in both blue and white’, 100, ‘nike.png’),
(2, ‘Reebok basket shoe’, ‘Great basket shoe from Reebok. Unisex model. Comes in both red and white’, 90, ‘reebok.png’),
(3, ‘Nike basket shoe’, ‘Basket shoe from Nike. Comes in both white and red’, 80, ‘nike2.png’),
(4, ‘Reebok running shoe’, ‘Running shoe from Reebok. Comes in both white and red’, 75, ‘reebok2.png’),
(5, ‘New Nike basket shoe’, ‘New Basket shoe from Nike. Comes in both blue and red’, 80, ‘nike3.png’);

So now we got a basic MySQL table “search_test” with some test data. The next thing is to create the full-text index that enables the full-text search. In the example we’ll build a index that can search in the fields “name” and “description”.

ALTER TABLE `search_test` ADD FULLTEXT `search_index` (
`name` ,
`description`)

Please notice that you can only create a full-text index if the included fields contains the datatype “text”. If you’re using other data types, you need to change the data type to “text” before you proceeds.

With this little MySQL operation, we’re now ready to build the query for extracting data from the full-text index.

The basic syntax for a full-SQL query is:

SELECT * from <table> WHERE MATCH (<field 1>, <field n>) AGAINST(‘<search word>’)

This means, that if you want to search in our table created for this example for “running shoes”, you can use this SQL:

SELECT * from search_test WHERE MATCH (name, description) AGAINST(‘running shoes’)

To further refine the example, we’ll add a ranking of the results, so the best matches will be displayed first:

SELECT *,  MATCH(name, description) AGAINST (‘running shoes’) AS score from search_test WHERE MATCH (name, description) AGAINST(‘running shoes’) order by score desc

And finally here is a complete PHP script that searches our database, ranks the results by score and presents the results:

<?
mysql_connect(“hostname”, “username”, “password”);
mysql_select_db(“your_db”);
?>
<form action=”<? $_SERVER[‘PHP_SELF’]; ?>”>
<input type=”text” name=”q” value=”<? echo $q; ?>”>
<input type=”submit” value=”Search!”>
</form>
<hr>
<?
if (isset($q)) {
$res = mysql_query(“SELECT *, MATCH(name, description) AGAINST (‘$q’) AS score from search_test WHERE MATCH (name, description) AGAINST(‘$q’) order by score desc”);
$ant = mysql_num_rows($res);
if ($ant > 0) { // query provided results – display results
echo (“<br/><h2>Search results for \”$q\”:</h2>”);
while ($result = mysql_fetch_array($res)) {
echo (“<h3>{$result[‘name’]} ({$result[‘score’]})</h3>{$result[‘description’]}<br/><br/>”);
}
} else { // query provided 0 results – display 0 hit message
echo (“<br/><h2>Sorry – searching for \”$q\” gave no results</h2>”);
}
}
?>

If you’re using the sample data for testing, please notice:

Searching for “nike” or “shoes” provides 0 results even if there is data in the database. This is caused by the words beeing present in more than 50% of the rows.

So there you go, now you have the basics for building a great PHP and MySQL driven search engine for your website.

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS
fold-left fold-right
About the author
Jørgen Nicolaisen has been passionately interested in everything online since 1995. His experience is based on working with small hobby projects as well as high volume websites. Jørgen is currently focused on the PHP based programming framework - Codeigniter, and WordPress naturally

6 Replies to Create your own fulltext searchengine with PHP and MySQL

  1. Daniele says:

    “”Searching for “nike” or “shoes” provides 0 results even if there is data in the database. This is caused by the words beeing present in more than 50% of the rows.””
    And if I have to search the word in a table containing only 2 or less record?

  2. jhnidk says:

    Well – with so little data, a search engine might not be relevant anyway ;-). You can’t really bypass this issue, it’s part of the way that the full-text search is built for MySQL

  3. Daniele says:

    Ok. Thanks a lot for the answer. My application is for an exam where I have to built a Web Site. I think that this trouble will be not relevant because if there are very few data, the user can search the word reading the text directly in the article. :-). Thank you very much for your explanation!

  4. Koos Mooij says:

    In the script, “$q” is not declared as a “var”, nothing happens.
    At the top of the script, I’ve put:
    $var = @$_GET[‘q’];

    and further on “$q” replaced with “$var”

  5. Chad says:

    “MySQL requires that you have at least three rows of data in your result set before it will return any results.”

    So if you are too accurate in your search query and you find exactly the one result you are looking for… MySQL will not return that one result?

    That’s the dumbest limitation I’ve ever hear of for search.

Trackbacks for this post

  1. The perfect 404 page :: Tips4PHP

Comments are now closed for this article.