Multiple Word Search Error?

[RobertFullmen]

Hey All,

I'm trying to add multiple word search to my site and I'm getting the following error: "mysql_num_rows(): supplied argument is not a valid MySQL result resource " I know, my search is posting because I'm getting my search terms back with the "nothing found" message I have set up. Here's what I have:

  $search = $_POST["search"];  
  $arraySearch = explode(" ", $search);
  $arrayFields = array(0 => "title", 1 => "content");
  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $query = "SELECT * FROM table1 WHERE desc LIKE '$arraySearch'";
  $countFields = count($arrayFields);
  while ($a < $countFields)
  {
    while ($b < $countSearch)
    {
      $query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $query = $query." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $query = $query.") OR (";
    }
  }
  $query = $query.")";
  $query_result = mysql_query($query);

  
  if(mysql_num_rows($query_result) < 1)
   {
    echo '<p>No matches found for "'.$search.'"</p>';
  }
  else
  {
   Print "<table border cellpadding=3>"; 
    while($row = mysql_fetch_assoc($query_result))
    {
      Print "<th>Description:</th> <td>".$row['desc'].  "</td> ";
    }
Print "</table> ";   
}

 

You guys are the best, thanks so much.

 

[mikesta707]

You may have a mysql error. to debug, you could change this line

  $query_result = mysql_query($query);

to

  $query_result = mysql_query($query) or die(mysql_error())

'

 

Note, that using or die() is not a good idea in production, but is fine while you are working out the bugs.

 

A couple things I noticed. One of your column names is desc. This is a reserved word in Mysql, so if you want to use it, you need to surround it with backticks (`). The better option would probably be to change the column name but thats up to you.

 

This line

$query = "SELECT * FROM table1 WHERE desc LIKE '$arraySearch'";

and specifically the part where you do

LIKE '$arraySearch'";

doesn't quite make sense. $arraySearch is an array, and when you use it like that in a string, the whole array gets cast as a string. Arrays being cast as strings result in the string "Array", so for example, if I had some array, $array (doesn't matter whats in it) and I did

echo "Array to string conversion: ".$array

the result would be

Array to string conversion: Array

 

It may also help if you echo the whole query, and post it here. just add

echo $query;

 

after your loop that builds it. Once you report the mysql error and the query you are trying to run we can be of more help

[RobertFullmen]

Thanks for the quick response.  I didn't know that desc was a reserved word so I changed it. I modified the code as per your instruction and now I get the following message "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'title LIKE '%chair%') OR (content LIKE '%chair%')' at line 1" chair happens to be the search term.

$search = $_POST["search"];  
  $arraySearch = explode(" ", $search);
  $arrayFields = array(0 => "title", 1 => "content");
  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $query = "SELECT * FROM table1 WHERE Description LIKE '$search'";
  $countFields = count($arrayFields);
  while ($a < $countFields)
  {
    while ($b < $countSearch)
    {
      $query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $query = $query." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $query = $query.") OR (";
    }
  }
  $query = $query.")";

echo $query;
  
$query_result = mysql_query($query) or die(mysql_error());
  
  if(mysql_num_rows($query_result) < 1)
   {
    echo '<p>No matches found for "'.$search.'"</p>';
  }
  else
  {
   Print "<table border cellpadding=3>"; 
    while($row = mysql_fetch_assoc($query_result))
  
  echo $query;

{
      Print "<th>Description:</th> <td>".$row['Description'].  "</td> ";
    }
Print "</table> ";   
}

[mikesta707]

can you post the full query?

[RobertFullmen]

That's it up there.

[Pikachu2000]

Change the query execution line so that when it fails, it also echos the full query string. If the problem isn't obvious after doing that, post the full output here.

 

$query_result = mysql_query($query) or die( "<br>Query: $query<br>Cratered with error: " . mysql_error() );