[SOLVED] Query multiple criteria syntax - help

[Henry2200]

I have a query that was working fine, but I recently switched hosts. I couldn't tell you what version of MySQL was on the old host, but I am now on version 5.0.67. The query asks for multiple fields using AND and LIKE. My error message is telling me there may be something wrong with my syntax:

"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 'Loop LIKE '%') AND (Site_Type LIKE '%') AND (Electricity LIKE '%') AND (Handica' at line 2"

Anyone care to help me out? Here's what I got for code...

<?

  // Get the search variable from URL

  $num = @$_GET['num'] ;
  $loop = @$_GET['loop'] ;
  $type = @$_GET['type'] ;
  $elec = @$_GET['elec'] ;
  $handi = @$_GET['handi'] ;
  $trimmednum = trim($num); //trim whitespace from the stored variable
     
  $var = $num . $loop . $type . $elec . $handi ;
  
// trying to get rid of %
if ($loop=="%") 
  {  $l="Any Loop";  }
if ($type=="%") 
  {  $t="Any Type";  }
if ($elec=="%") 
  {  $e="Both Electric and Non-electric";  }
if ($handi=="%") 
  {  $h="Both Accessible and Not Accessible";  }
  
if (!isset($l))
  {$l=$loop;}
if (!isset($t))
  {$t=$type;}
if (!isset($e))
  {$e=$elec;}
if (!isset($h))
  {$h=$handi;}

// rows to return
$limit=20; 

// check for an empty string and display a message.
if ($var == "")
  {
  echo "<p>Please enter a search...</p>";
  echo "</td></tr></table>";
  include('../footer.php');
  echo "</body></html>";
  exit;
    
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }

mysql_connect(...); //(host, username, password)

mysql_select_db(...) or die("Unable to select database"); //select which database we're using

// Build SQL Query  
$query = "SELECT * FROM table1 WHERE (Number LIKE '%$num') 
AND (Loop LIKE '$loop') AND (Site_Type LIKE '$type') 
AND (Electricity LIKE '$elec') AND (Handicap like '$handi') 
ORDER BY Loop, Number";

$numresults=mysql_query($query) or die(mysql_error());
$numrows=mysql_num_rows($numresults);

[PFMaBiSmAd]

loop is a reserved mysql keyword.

 

You should rename your column to something else. You can also surround it in back-tacks `loop` everywhere it appears in your code. Since you would need to go through your code and do a search/replace in either case, it would be better to rename it.

[Henry2200]

It works!

 

Thanks so much!