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

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
