Match .. against query and FULLTEXT search of MySQL table

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
`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`)
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
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'>
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>";
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 .
Searching using Match against query

Match Against Query download script

Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net

    Post your comments , suggestion , error , requirements etc here .

    SQL Video Tutorials

    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 All rights reserved worldwide Privacy Policy Disclaimer