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.

Advertisements
By Rz Rasel Posted in Php

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s