MySQL FULLTEXT Search

[tinmanbf]

Hi All

 

I want to update part of my site that has a basic sql search script o use the fulltext search feature.

 

At the moment I have:

 

//search on the given terms
if ($searchfor != "") {
    $searchitem = explode (" ", $searchfor);
      if ($stock_enabled == 1) { $searchquery = "WHERE `STOCK` > 0 AND ("; }
 else $searchquery = "WHERE (";
     $counter = 0;
     while (!$searchitem[$counter] == NULL){
$searchquery .= "((DESCRIPTION LIKE '%" . $searchitem[$counter] . "%') OR (PRODUCTID LIKE '%" . $searchitem[$counter] . "%'))";
$counter += 1;
if (!$searchitem[$counter] == NULL) { $searchquery .= " ".$searchmethod." "; }
   	     }
         $searchquery .= ")";
	 }
	 else { $searchquery = "WHERE (DESCRIPTION = 'never_find_me')"; } // just to cause that the searchresult is empty
         $query = "SELECT * FROM `".$dbtablesprefix."product` $searchquery ORDER BY `$orderby_field` ASC";
}

 

I want to change this to use MATCH and AGAINST commands...

 

In an SQL terminal by typing:

 

SELECT PRODUCTID,DESCRIPTION FROM product WHERE MATCH(PRODUCTID,DESCRIPTION) AGAINST ('keyword')

 

I get the result I want (keyword = the result from the search form)

 

My problem is I can't work out how to update the script to use this method rather than the the other which I find doesn't produce accurate search results.

 

I've tried loads of different way but have finally admitted defeat and need to ask for help.

 

Any help would be gratefully received.

 

Thanks

 

Ben

[fenway]

First, MATCH... AGAINST -- fulltext searching -- is fundamentally different than LIKE '%whatever%'.

 

You should consult the documentation thoroughly before making this choice.

 

Second, if you decide to use it, you'd better add a fulltext index -- which means no InnoDB.

 

Third, if you asking how to recode PHP, this is the wrong forum.

[tinmanbf]

Hi

 

First, yes I understand that MATCH...AGAINST, fulltext searching, is fundamentally different than LIKE, this is why I'm having problems understanding it.

 

Second, yes I have already added a fulltext index, as I understand this is needed to perform a full text search.

 

Third, I wasn't asking for someone to recode the PHP, I just wanted pointing in the right direction, I'm not a php expert which is why I have asked the help at the forum. I have tried a lot's of different ways to get the script to work, but haven't had any luck.

 

 

[fenway]

The AGAINST can take a list of space-separated words... it's as easy as that.