Want MySQL FullText Search with InnDB Huh?

MySQL FullText indexing is a popular way to search data from table. FullText indexing is only available if database table type is MyISAM. But, MyISAM table type does not support Foreign keys. InnoDB table type support Foreign keys but does not support FullText indexing. So I have to use a trick to implement fulltext search for a InnoDB type table. I have created a temporary MyISAM table which is identical to my InnoDB table and deleted that table after search complete. check this out……

Provide the search term and limit for search result in function’s parameter.


function fulltxt_search($q,$lim1,$lim2){ 

$terms =trim(stripslashes(strip_tags($q)));

   $rhtml.= "\n".'<p>No search term entered.</p>';


   // SQL injection prevention (double quote mark is allowed here but should probably be disallowed)

   $terms = strtr($terms, ',/\*&()$%^@~`?;', '               ');

   $terms = trim($terms);

   $terms = str_replace('#180', '', $terms);

   $rhtml.= "\n".'<p>Searching for <i>'.htmlspecialchars($terms).'</i>... </p>';

   $key = 'text_index';

   // Beware of the re-definition of $s in this query.

   $terms = html_entity_decode($terms, ENT_QUOTES);
	mysql_query('CREATE TEMPORARY TABLE `tbl_mirror` (FULLTEXT INDEX (`info`)) Engine=MyISAM SELECT * FROM `values`');
   if(!($res = @mysql_query('SELECT * FROM `tbl_mirror` WHERE MATCH(`info`) AGAINST ("'.$terms.'") LIMIT '.$lim1.', '.$lim2.';')or die(mysql_error()))){
	  mysql_query('DROP TEMPORARY TABLE tbl_mirror;')or die(mysql_error());
      echo "\n".'<p>No match for: <i class="red">'.$terms.'</i></p>';

	mysql_query('DROP TEMPORARY TABLE tbl_mirror;')or die(mysql_error());
      if(@mysql_num_rows($res) == 0){

        $rhtml.="\n".'<p>The search engine cannot find the page you are looking for.</p>';

        $rhtml.="\n".'<p>This could be because it could not find any pages containing <i>'.$terms.'</i>';

        $rhtml.="\n".'<p>Alternatively, it might have found too many pages, and could not decide which one you wanted.</p>';


         $i = 0;

         while($row = mysql_fetch_array($res)){

            $s = substr(stristr(strip_tags($row['info']), $terms), 0, 120);		
            $rhtml.="\n".'<p>'.$i.'.) <a href="results.php?rez_id='.$row['f_id'].'">'.htmlentities($row['info']).' - '.$row['f_id'].'</a><br />... '.$s.'...'.$row['id'].'/'.$row['e_id'].'...</p>';




return $rhtml;