Simple Example of Full Text Search with MySQL

Full text search is very important searching mechanism when you are searching a keyword into string instead into a word,Normally when somebody asking search a word in database column in this case we are thinking about LIKE but this is not ideal search when we are searching a word which is related to keyword.
Let me explain whats is difference between LIKE and FULL TEXT Search.

We need to search ‘test’ string into name column and i need to find all combination test word in ‘name’ column, so if we use LIKE then we will not get ‘Hi test’ value, we will get get only ‘pre’test’post’ value , the LIKE does not work with full string, in that case we will use FULL Text search.

Please keep in mind FULL text works only with MyISAM table.

Select Code
1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `tbl_test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  FULLTEXT KEY `Name` (`Name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

Now we will define sql query, the sql query used match MYSQL function to search word into string.

Select Code
1
$countSQL = "SELECT ID,Name FROM tbl_test where  MATCH (Name) AGAINST ('".$options['keyword']."*' IN BOOLEAN MODE)";