...

View Full Version : Help: Multiple conditions in the WHERE clause



grant_p
07-30-2011, 04:29 PM
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

sunfighter
07-30-2011, 05:53 PM
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.

Old Pedant
07-30-2011, 09:53 PM
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.

grant_p
07-31-2011, 10:57 PM
Old Pedant: Thank you very much for the exellent explanation. It definitely taught me something VERY useful...

Thanks
Grant

Old Pedant
08-01-2011, 12:01 AM
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.

grant_p
08-01-2011, 11:02 AM
Old Pedant: Thanks, I will remember what you have said, your advice and help has been extremely helpful, thank you...

bazz
08-02-2011, 03:04 AM
If you answered 25, gp back to primary school math class.


I'd better try to enrol then :D



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum