Page 1 of 1

WHERE query help

Posted: Sat Aug 14, 2004 3:48 pm
by AliasBDI
I get an error of:
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 '''%company1%'') OR (city LIKE ''%company1%'') OR (state LIKE ''
It is a search query with multiple WHEREs. Any ideas?

Posted: Sat Aug 14, 2004 4:02 pm
by markl999
What is the code that's producing the query? It looks like you have escaping problems with your quotes.

here you go.

Posted: Sat Aug 14, 2004 4:20 pm
by AliasBDI
Here it is. Thanks for your help.

Code: Select all

<?php
$colname_searchRESULTS = "1";
if (isset($_POST['search'])) {
  $colname_searchRESULTS = (get_magic_quotes_gpc()) ? $_POST['search'] : addslashes($_POST['search']);
}
mysql_select_db($database_content, $content);
$query_searchRESULTS = sprintf("SELECT * FROM con_company WHERE con_company.company LIKE '%%%s%%' OR con_company.city LIKE '%%%s%%' OR con_company.`state` LIKE '%%%s%%' OR con_company.zip LIKE '%%%s%%'", $colname_searchRESULTS,$colname_searchRESULTS,$colname_searchRESULTS,$colname_searchRESULTS);
$searchRESULTS = mysql_query($query_searchRESULTS, $content) or die(mysql_error());
$row_searchRESULTS = mysql_fetch_assoc($searchRESULTS);
$totalRows_searchRESULTS = mysql_num_rows($searchRESULTS);
?>
**How do I make it colored coded?
Use

Code: Select all

tags. I've done it for you - markl999[/color]

Posted: Sat Aug 14, 2004 4:26 pm
by markl999
Hmm..weird. Your code doesn't look like it would produce that error as the error has ('% ... %') OR ('% ... %') etc but you code has no brackets in it, eg the code looks like it would produce '% ... %' OR '% ... %' .. so i'm not sure where the problem is as the error doesn't seem to match the code :o

Hmm.

Posted: Sat Aug 14, 2004 4:41 pm
by AliasBDI
Do you know of a better way to search multiple columns each record? Better than this one?

Posted: Sat Aug 14, 2004 4:51 pm
by markl999
I prefere to use a method such as:

Code: Select all

$sqladd = array();
if(!empty($_POST['search'])){
  //the columns to search
  $searchCols = array(
    'company', 'city', 'state', 'zip'
  );
  foreach($searchCols as $scol){
    $sqladd[] = $scol." LIKE '%".$_POST['search']."%'";
  }
  $sql = 'SELECT * FROM con_company WHERE '.join(' OR ',$sqladd);
  echo $sql; //test line to see all is well
  $searchRESULTS = mysql_query($sql) or die(mysql_error());
}
I'm sure you can tweak it to suite ;)