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 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.
Tutorial Categories:
It is showing an error like
#1191 – Can’t find FULLTEXT index matching the column list
Did you altered your table for full index?
CREATE FULLTEXT INDEX searchindex ON TableName(title);
When I create the TableName, it shows in mySQL as, tablename…!?…No capitals..
How can i delete full text index on some column?
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.
sir plz make totrial on how to make report in php plzzzz
mine wouldn’t show result if i’m searching text with 4 characters below, how to configure that default?