Help: Multiple conditions in the WHERE clause

Hi guys,

Can anybody point me in the right direction on how to structure my query below, to check the REGION field in my database for one of two conditions

ie: The region field must contain any PHP region variable passed to it and find it, and it should also find all entries where the region value = national, and obviously the product and enabled fields need to match...

eg: REGION = 'DALLAS' OR REGION = 'NATIONAL' should return all entries containing the region stored as DALLAS as well as all entries containing a value of NATIONAL

I have tried with the following query without any luck:


$sql = "SELECT * FROM products WHERE REGION = '".$region."' OR REGION = 'NATIONAL' AND PRODUCT = 'Engine Block' AND ENABLED='1' order by PRODUCT_ID asc";


I am guessing that it is failing because of the combination of conditions OR and the AND's in the where clause... I am a beginner as far as MYSQL goes, and I have search high and low for a better way to structure this query where it actually does what I need, but couldn't find anything with my limited knowledge.

Any help will be greatly appreciated!

Thanks
Grant

First off,
This line "...stored as DALLAS as well as all entries containing..." means you should use and AND not the OR your using.

I also suggest you check each WHERE separately and combine them one at a time once you are sure they work.

Sunfighter is way off base.

There is NO POSSIBLE WAY that *any* record will *EVER* be found with the query

SELECT * FROM products WHERE REGION = 'DALLAS' AND REGION = 'NATIONAL'

There is no way for a single record to be *BOTH* 'DALLAS' *AND* 'NATIONAL'.

No, the problem is PRECEDENCE OF OPERATORS.

Consider this problem:

What is 3 + 2 * 5 ?

If you answered 25, gp back to primary school math class.

Multiplication has higher PRECEDENCE than addition, so the answer is 13. As if it had been written
What is 3 + ( 2 * 5 ) ?

Multiply first, then add.

SAME THING with the AND and OR operators! AND has higher precedence than OR.

So all you needed was a set of parentheses:

$sql = "SELECT * FROM products "
. " WHERE ( REGION = '".$region."' OR REGION = 'NATIONAL' ) "
. " AND PRODUCT = 'Engine Block' "
. " AND ENABLED='1' "
. " order by PRODUCT_ID asc";

Notice the parentheses around the OR'ed terms.

Also, there's an alternative (and very marginally faster) way to do this:

$sql = "SELECT * FROM products "
. " WHERE REGION IN ('".$region."', 'NATIONAL') "
. " AND PRODUCT = 'Engine Block' "
. " AND ENABLED='1' "
. " order by PRODUCT_ID asc";

After all, the IN( ) operator is implicitly the OR'ing of multiple possible values.

Old Pedant: Thank you very much for the exellent explanation. It definitely taught me something VERY useful...

Thanks
Grant

If it makes you feel better, even "old hands" at SQL (and other languages!) will occasionally forget about precedence. And some languages even have oddball precedences (C/C++ notably). So when in doubt, add parentheses.

Old Pedant: Thanks, I will remember what you have said, your advice and help has been extremely helpful, thank you...

If you answered 25, gp back to primary school math class.


I'd better try to enrol then :D