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
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 doest 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 upto 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
Visitors Rating
Your Rating




Google+

Post Comment This is for short comments only. Use the forum for more discussions.








HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer