Another mysql_num_rows(): error

[dantrujillo]

I am getting the following error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/champion/public_html/search.php on line 34 when doing a search for text in a description or product name.  The error only happens when I do a search for multiple words.

Here is the code:

if ( $st ) {

include("admin/include/db.php");
  mysql_select_db ("dbname");

# remove multiple spaces
   $st = ereg_replace(" +"," ",$st);
# remove unwanted spaces and split words
   if ( strstr($st,",") ) {
      $st = str_replace(" ", "", $st);
      $w_ar = split(",",$st);
   } else {
      $w_ar = split(" ",$st);
   }
   $ac = count($w_ar);
# construct search pattern
   if ( $ac > 0 ) {
      for ( $i=0; $i<$ac; $i++ ) {
         if ( $i>0 && $i<$ac ) $cmp_str .= " ";
         $cmp_str .= "$w_ar[$i]";
         if ( $ac == 1 ) {
            $cmp_str .= "*"; # add wildcard
            $bmode = "IN BOOLEAN MODE";
         }
      }
   }

  $query = "SELECT *,MATCH(pdescription,pname,pclass,pgroup,manu) AGAINST ('$cmp_str' $bmode) AS score 
            FROM products WHERE MATCH(pdescription,pname,pclass,pgroup,manu) AGAINST ('$cmp_str' $bmode)";

  $result = mysql_query($query);
  $num_result = mysql_num_rows($result);


  if ( $num_result == 0 ) {
     $html_out = "<tr><td colspan=6>No results were found for $st.</td></tr>";
  } else {
     $count = 1;
     $html_out .= "<tr><td colspan=6>&nbsp;Search found $num_result matches for <b>$st</b><br><br></td></tr>\n";
     for ( $i=0; $i<$num_result; $i++ ) {
       $row = mysql_fetch_object($result);
       if ( $count == 1 ) { $html_out .= "<tr>"; }
       $html_out .= "<td align=center width=160 class=bodytext valign=top><a href=products/$row->pclass/$row->prod_id><img src=images/products/sm_$row->pimage";
       if ( $row->filename == "" ) $html_out .= ""; else $html_out .= $row->filename;
       $html_out .= " border=0></a><br><b>$row->pname</b></td>\n";
       $html_out .= "<td width=30><img src=/images/clear.gif width=30 height=1></td>\n";
       if ( $count == 3 ) { $html_out .= "</tr><tr><td colspan=6><br></td></tr>"; $count = 0;}
       $count++;
     }
  }
  $html_out .= "<tr><td align=center><br><br><a href=javascript:history.back()><font color=black>back</font></a><br></td></tr>";

Any ideas?

[Troy]

This may seem too obvious, but the problem is that $result is not a valid MySQL resource. So why is that? Since your other queries work, we have to assume your connection is good. So the almost sure candidate is your query. Something is wrong with your query. Try changing your code to this:

if (!$result = mysql_query($query)) {
  die(mysql_error());
}
$num_result = mysql_num_rows($result);

This should print an error message that indicates what is wrong with the query.

Refer to http://us3.php.net/manual/en/function.mysql-error.php

Another thing I like to do when I'm having trouble with a query is run it directly against the database rather than in a PHP page. Do you have access to run the query directly against the database? For example, command line or phpMyAdmin? These tools give you nice feedback telling you what's wrong.

[dantrujillo]

It gives me the following:

Can't find FULLTEXT index matching the column list

which according to the the error handling list is:

Error: 1191 SQLSTATE: HY000 (ER_FT_MATCHING_KEY_NOT_FOUND)

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

I'm just drawing a blank. I think I've just looked at it too long......

Thank you! You've given me another avenue to investigate.

[Troy]

I don't have much experience setting up fulltext indexes, but I think your issue is that not all (or any?) of the columns you are passing to the MATCH() function have fulltext indexes setup on them.

[dantrujillo]

I will check that too. Thank you!

[dantrujillo]

I changed the index of the columns to FULLTEXT and only perform the MATCH() on the pdescription column. It works great if I only use 1 column. If I add any other column to the MATCH(), I get the mysql_num_rows() error.

So, has anyone dealth with tokenizing the words of the search to see if there is a match in any of the columns for the token and not the word phrase?

Would that be the next thing to try?