Match against search query with FULLTEXT index of mysql table with PHP script

MySQL provides search facility by using match .. and against query.

Here is the syntax

SELECT * FROM table_name where match ( column1, column2.. ) against ( 'keyword1 keyword2')

Adding Index to columns

Before using match against search query to MySQL table we have to index the columns. There are three types of Indexes

FullText Index can be applied to char, varchar and text columns.

We can add fulltext index while creating the table or we can use alter command to add fulltext index.

Changing the structure to add FULTEXT index

ALTER TABLE `sql_tutorial`.`student` ADD FULLTEXT `my_ind` (`name`)

Creating table with FULLTEXT Index

CREATE TABLE IF NOT EXISTS `student` (

  `id` int(2) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) NOT NULL DEFAULT '',

  `class` varchar(10) NOT NULL DEFAULT '',

  `mark` int(3) NOT NULL DEFAULT '0',

  `sex` varchar(6) NOT NULL DEFAULT 'male',

  UNIQUE KEY `id` (`id`),

  FULLTEXT KEY `my_ind` (`name`,`class`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=36 ;

To add FULLTEXT index your table type must be of MyISAM. Table type InnoDB doesn't not support FULLTEXT index. However MySQL version 5.6 onwards support for FULLTEXT index is available.

You will get message like this

The used table type doesn't support FULLTEXT indexes

Changing the structure to add FULLTEXT index

ALTER TABLE `sql_tutorial`.`plus2_file_php` ADD FULLTEXT `my_ind` (`title` ,`des`)

How to use Query

We can apply query to get the matching data. Here is the sql

SELECT * FROM `student` WHERE match(name) against('alex')

Sample script using match .. against

We have developed a simple script where SQL is developed using search string. First we will show a search box. On Submit of the form the string is collected and sql is developed .

For this first we will connect to database

require "config.php"; // Database Connection

Then we will collect the search string

$search_string=trim($_REQUEST['keyword']);

Now using the above search string we will develop the query.

$sql="select * from $table_name where match(name,class) against('$search_string') ";

Now using the same query we will display the records returned.

echo "<table class='t1'>
<th>ID</th><th>NAME</th><th>CLASS</th><th>SEX</th><th>MARK</th>";
foreach ($dbo->query($sql) as $row) {$m=$i%2;
echo "<tr class='r$m'><td>$row[id]</td><td>$row[name]</td><td>$row[class]</td><td>$row[sex]</td><td>$row[mark]</td></tr>";
$i=$i+1;}
echo "</table>";

We used student table for this example. You can use any other table with more data.

How to Search

Search for more than 3 char. Any word up to 3 char is ignored or uses as stop word.

Search for the name John and you should see result like this .

Subscribe to our YouTube Channel here

Match .. against query and FULLTEXT search of MySQL table

Adding Index to columns

Changing the structure to add FULLTEXT index

How to use Query

Sample script using match .. against

How to Search

Subscribe