April 15, 2015 3:43 pm

How to Search in MySQL using full-text indexing in PHP

Hello friends after a long time today I am going to write a post on few of my readers request on How to quick Search in MySQL using full-text indexing in PHP. Many developers make a big mistake during searching from MySQL use wildcard queries “LIKE %string%” which is a very slow query to search records so today we are going to learn full-text indexing search and its hundreds or thousands of times fast then wildcard wildcard queries.

How to Search in MySQL using full-text indexing in PHP

How to get it to work:

1. Create Table:

CREATE TABLE TableName (title CHAR(100));

Insert Data:

INSERT INTO TableName (`title`) VALUES ('MySQL databases are helpful for store data');
INSERT INTO TableName (`title`) VALUES ('PHPGang is a helpful website');
INSERT INTO TableName (`title`) VALUES ('This website is the best');

2. Make sure that your database table uses MyISAM storage engine, if not then use below query to alter your table.

ALTER TABLE TableName ENGINE=MyISAM;

3. Create full-text index

CREATE FULLTEXT INDEX searchindex ON TableName(title);

4. Search it!

<?php
$search = mysqli_real_escape_string($connection,$search);
$titles = mysqli_query($connection,"SELECT title FROM TableName 
    WHERE MATCH(title) AGAINST('$search')");
while($row = mysqli_fetch_assoc($titles)) {
    $result[] = $row['title'];
}
?>

This will search records from database more faster then your wildcard queries and show you exact results you want for example:

You have data in your Table and you run below queries:

SELECT * FROM TableName WHERE title LIKE '%helpful website%';

It will return only 1 record.

If you use full-text search it will return any row that matches “helpful” or matches “website”:

SELECT * FROM TableName WHERE MATCH(title) AGAINST ('helpful website');

Boolean mode searching popular with internet search engines – allow you to proceed words with a + or a to force it to be present (+) or not present ().

SELECT * FROM TableName WHERE MATCH(title) AGAINST ('helpful -website' IN BOOLEAN MODE);

This Boolean query search only helpful in the search not website as we add – sign before website.

SELECT * FROM TableName WHERE Match(title) AGAINST ('"helpful website"' IN BOOLEAN MODE);

Return only one record as our wildcard query we are forcing it to search complete string in database.

All of these examples are possible Boolean queries:

helpful website Match either helpful, website, or both
+helpful +website Match both helpful and website
+helpful -website Match helpful but not website
+helpful ~website Match helpful, but mark down as less relevant rows that contain website
+help* Match nice, helpful, helpfully, help website, etc
“helpful website” Match the exact term “nice website”
+helpful +(website blog) Match either “nice website” or “nice blog”
+helpful +(>website <blog) Match either “helpful website” or “helpful blog”, with rows matching “helpful website” being considered more relevant

So this is a simple and very useful tutorial I hope it helps you in your projects and make theme fast and more reliable in search.

Author Huzoor Bux

I am Huzoor Bux from Karachi (Pakistan). I have been working as a PHP Developer from last 5+ years, and its my passion to learn new things and implement them as a practice. Basically I am a PHP developer but now days exploring more in HTML5, CSS and jQuery libraries.


Tutorial Categories:

7 responses to “How to Search in MySQL using full-text indexing in PHP”

  1. aswin says:

    It is showing an error like

    #1191 – Can’t find FULLTEXT index matching the column list

  2. Mariano says:

    When I create the TableName, it shows in mySQL as, tablename…!?…No capitals..

  3. Lão Còi says:

    How can i delete full text index on some column?

  4. Ayyaz says:

    You used wrong words in your table.
    I left column you are using word “helpful” but in right column you are using word “nice” for example “helpful website” left but in right column you used “nice website”. This might make reader confused so please fix it.

    Thanks for such useful article.

  5. Hayat Hussain says:

    sir plz make totrial on how to make report in php plzzzz

  6. kelvin barsana says:

    mine wouldn’t show result if i’m searching text with 4 characters below, how to configure that default?

Leave a Reply

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