Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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:

    Code:
    $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

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,197
    Thanks
    23
    Thanked 605 Times in 604 Posts
    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.
    Last edited by sunfighter; 07-30-2011 at 05:55 PM.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    Sunfighter is way off base.

    There is NO POSSIBLE WAY that *any* record will *EVER* be found with the query
    Code:
    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:
    Code:
        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
    Code:
        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:
    Code:
    $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:
    Code:
    $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.

  • The Following 2 Users Say Thank You to Old Pedant For This Useful Post:

    grant_p (07-31-2011), hernantz (07-31-2011)

  • #4
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Old Pedant: Thank you very much for the exellent explanation. It definitely taught me something VERY useful...

    Thanks
    Grant

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    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.

  • #6
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Old Pedant: Thanks, I will remember what you have said, your advice and help has been extremely helpful, thank you...

  • #7
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    If you answered 25, gp back to primary school math class.
    I'd better try to enrol then
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •