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.
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.
$countSQL = "SELECT ID,Name FROM tbl_test where MATCH (Name) AGAINST ('".$options['keyword']."*' IN BOOLEAN MODE)";