Search Query Help Please

[BoarderLine]

I am using the following query as a DB search for a key word ($trimm).  The other variables passed to the query are a category (the first %s which defaults to % if value doesn't exist) and City the second (the second %s which also defaults to % if value doesn't exist).  The query is nearly working fine however if a match is found for the variable $trimm, and values exist in the query for the category and City, I am needing the result to be filtered to match all variables.  As the query is at present if a match is achieved for $trimm it disregards the need for category and City to also match the hit.  I hope this makes some sort sense to someone.  Any help really appreciated. Ta.

 

SELECT categories.Cat, cities.City, attrac.AttracID, attrac.AttracName, attrac.Add, attrac.Sub, attrac.List, attrac.Default, SUBSTRING(attrac_discrip.Discrip,1,350) AS Discription FROM (((attrac LEFT JOIN categories ON categories.CatID=attrac.List) LEFT JOIN cities ON cities.CityID=attrac.City) RIGHT JOIN attrac_discrip ON attract_discrip.AttracID=attrac.AttracID) WHERE attrac.List LIKE %s AND attrac.City LIKE %s AND attrac.Status='1' AND categories.Cat LIKE '$trimm' OR attrac.AttracID LIKE '$trimm' OR MATCH(attrac.AttracName) AGAINST ('$trimm') OR MATCH(attrac_discrip.Discrip) AGAINST ('$trimm')) ORDER BY attrac.AttracName

[BoarderLine]

Some additional info.

 

Both Cat and City Variables are numeric.

 

$cat_rsAttrac = "%";
if (isset($_GET['Cat'])) {
  $cat_rsAttrac = (get_magic_quotes_gpc()) ? $_GET['Cat'] : addslashes($_GET['Cat']);
}
$city_rsAttrac = "%";
if (isset($_GET['City'])) {
  $city_rsAttrac = (get_magic_quotes_gpc()) ? $_GET['City'] : addslashes($_GET['City']);
}

mysql_select_db($database);

$query_rsAttrac = sprintf("SELECT categories.Cat, cities.City, attrac.AttracID, attrac.AttracName, attrac.Add, attrac.Sub, attrac.List, attrac.Default, SUBSTRING(attrac_discrip.Discrip,1,350) AS Discription FROM (((attrac LEFT JOIN categories ON categories.CatID=attrac.List) LEFT JOIN cities ON cities.CityID=attrac.City) RIGHT JOIN attrac_discrip ON attract_discrip.AttracID=attrac.AttracID) WHERE attrac.List LIKE %s AND attrac.City LIKE %s AND attrac.Status='1' AND categories.Cat LIKE '$trimm' OR attrac.AttracID LIKE '$trimm' OR MATCH(attrac.AttracName) AGAINST ('$trimm') OR MATCH(attrac_discrip.Discrip) AGAINST ('$trimm')) ORDER BY attrac.AttracName", GetSQLValueString($cat_rsAttrac, "text"),GetSQLValueString($city_rsAttrac, "text"));

$query_limit_rsAttrac = sprintf("%s LIMIT %d, %d", $query_rsAttrac, $startRow_rsAttrac, $maxRows_rsAttrac);
$rsAttrac = mysql_query($query_limit_rsAttrac) or die(mysql_error());
$row_rsAttrac = mysql_fetch_assoc($rsAttrac);

[kickstart]

Hi

 

Problem appears to be operator procedence. Basically MySQL evaluates the ANDs before the ORs.

 

Add some brackets to force the ORs to be evaluated first.

 

SELECT categories.Cat, cities.City, attrac.AttracID, attrac.AttracName, attrac.Add, attrac.Sub, attrac.List, attrac.Default, SUBSTRING(attrac_discrip.Discrip,1,350) AS Discription

FROM (((attrac LEFT JOIN categories ON categories.CatID=attrac.List)

LEFT JOIN cities ON cities.CityID=attrac.City)

RIGHT JOIN attrac_discrip ON attract_discrip.AttracID=attrac.AttracID)

WHERE attrac.List LIKE %s

AND attrac.City LIKE %s

AND attrac.Status='1'

AND ( categories.Cat LIKE '$trimm'

OR attrac.AttracID LIKE '$trimm'

OR MATCH(attrac.AttracName) AGAINST ('$trimm')

OR MATCH(attrac_discrip.Discrip) AGAINST ('$trimm'))

ORDER BY attrac.AttracName

 

All the best

 

Keith

[BoarderLine]

You Keith my friend are a legend.  Thank you very much that worked a treat! :-)