Compatibility issues???

[crazylegseddie]

I just tested my script on the real server and just realised they use a version of sql that does not support boolean mode searches is it possible to edit the script below to not use boolean searches? If I remove the 'BOOLEAN MODE' from the script i receive the following message:

'Can't find FULLTEXT index matching the column list '

Any advise on this new problem will be great?

$searchwords = (isset($_GET['words']) ? htmlspecialchars(stripslashes($_REQUEST['words'])) : ''); 
   echo '<div align="right">'; 
   echo '<form method="get" action="'.$_SERVER['PHP_SELF'].'">'; 
   echo '<input type="hidden" name="cmd" value="search" />'; 
   echo '<input type="text" name="words" value="'.$searchwords.'" /> '; 
   echo '<input type="submit" value="Search" class="box" />'; 
  echo '</form>'; 
  echo '</div>';
} 

// Create the navigation switch 
$cmd = (isset($_GET['cmd']) ? $_GET['cmd'] : ''); 
switch($cmd) 
{ 
  default: 
      searchForm(); 
     break; 
     echo '<b>Search Results:<br><br>'; 

$searchstring = mysql_escape_string($_GET['words']); 

    $sql = "SELECT pd_id, pd_name, pd_more, pd_more2,  
           MATCH(pd_name, pd_more, pd_more2)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) FROM tbl_product
           WHERE MATCH(pd_name, pd_more, pd_more2)  
           AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY pd_name DESC"; 
 }    
 $result = mysql_query($sql) or die (mysql_error()); 
$num_of_rows = mysql_num_rows($result);

if ($num_of_rows > 0)
{


   while($row = mysql_fetch_assoc($result)) 
    { 
      echo "<a href='categories.php?c=0&p=". $row['pd_id']. "'>".stripslashes(htmlspecialchars($row['pd_name'])).'</a><br />'; 
      } 
	}
else
{
echo "<i>No results found, please try another search";
}


?>

[bradgrafelman]

Is there a FULLTEXT index in the tbl_product table for the three fields you're trying to match against? Not 3 individual indeces, but one single FULLTEXT index that encompasses all 3 fields?

[crazylegseddie]

yes i used a turorial to help with the search and added to my table:

CREATE FULLTEXT INDEX full_index ON tbl_product(
pd_more ,
pd_more2
);

[bradgrafelman]

Okay, but what about pd_name? You didn't include that in the index.

[crazylegseddie]

no i didnt think i needed to because im not using pd_name for the search but just as a link to the correct product through the results found in my pd_more fields.

[bradgrafelman]

MATCH(pd_name, pd_more, pd_more2)

You're listing 3 columns in the FULLTEXT search.

CREATE FULLTEXT INDEX full_index ON tbl_product(
pd_more ,
pd_more2
);

That index only has 2 columns, therefore a proper FULLTEXT index does not exist.

[crazylegseddie]

Cool thx. I will try it out when I get home! How comes this did not effect the search when adding BOOLEAN MODE in the query, as I received correct results using this mode.

[bradgrafelman]

I'm not sure, it could be a different issue altogether. Since this is strictly a database question, I've moved this thread to the Database thread.

If any other SQL gurus out there know more and care to comment, please do... I'm working with limited knowledge here :p