[SOLVED] MYSQL query using AND and multiple ORS

[colby07]

Hi guys,

I'm having trouble querying my database.  I have a table with 5 columns.  Houseid, city, material 1, material 2, material3.  Either of the 3 materials fields can have the same entered materials.

 

I am trying to buid a query with a format similar to the following:

 

$query  = "SELECT * FROM houses WHERE city = '$city' AND material1 = '$material1' or material2 = '$material1' or material3 = '$material1'";

 

The results include houses that don't have $material1 but are in the designated $city AND houses with the materials but not in the designated city designated by the $city variable.

 

I would like the query to restrict the results to those that are in the $city AND have material 1.  Any insight would be greatly appreciated!

 

Thank you!

[kickstart]

Hi

 

Problem is that AND is evaluated before OR, so material1 must = $material1 if the city matches, but the rest don't care about the city.

 

You need to force the order they are evaluated with brackets:-

 

$query  = "SELECT * FROM houses WHERE city = '$city' AND (material1 = '$material1' or material2 = '$material1' or material3 = '$material1')";

 

Even when not required (ie, you have something where the evaluation order is the order that you want) it is good practice to use brackets to make it obvious to those who have to modify the code in the future.

 

All the best

 

Keith

[colby07]

THANK YOU!!!

 

SOLVED!!!