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 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL Query in PHP

    Hi all, Would like to ask a question, I'm making a query but can't sort out how to write it correctly.

    I have a table, and there are two fields, one of them is `country`, other one `active`
    My Task is to get all rows that contain:
    country LIKE '%United Kingdom%'
    or
    country LIKE '%ALL%'
    AND
    active=1
    so, for example if country fields will be: United Kingdom, United States, India, France. It should print it.
    If country field will be: ALL. It should print it.
    Also it should print all rows where country is '%United Kingdom%' OR '%ALL%' AND where active=1
    I tried this query:
    PHP Code:
    SELECT FROM `offersWHERE `countryLIKE '%Kingdom%' OR `countryLIKE '%ALL%' AND `active`=
    But this query also print all rows where active = 0
    So now i'm a bit stuck with this...
    Please help me with it
    Thanks.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    order of precedence, just like in math. AND takes precedence over OR just like multiply/divide do over addition and subtraction.

    Code:
    WHERE `country` LIKE '%Kingdom%' 
    OR (`country` LIKE '%ALL%' AND `active`=1)

  • #3
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    order of precedence, just like in math. AND takes precedence over OR just like multiply/divide do over addition and subtraction.

    Code:
    WHERE `country` LIKE '%Kingdom%' 
    OR (`country` LIKE '%ALL%' AND `active`=1)
    So maybe it would be better in my case to do it like that:
    PHP Code:
    WHERE (`country LIKE '%United Kingdom%' AND active=1) OR (`country` LIKE '%ALL%' AND active=1) 
    ?


    EDIT: Just tried out your code, and it's still taking rows with active=1 as well.
    So i edited it a bit and made it like:
    PHP Code:
    SELECT *
    FROM `offers`
    WHERE `countryLIKE '%Kingdom%'
    AND active =1
    OR (
    `
    countryLIKE '%ALL%'
    AND `active` =1

    What do you think about that?
    Last edited by WinDrop; 12-14-2011 at 07:59 PM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Think back to your elementary school math classes.

    What is the value of the expression
    Code:
         2 + 3 * 4
    ???

    If you said 20, then time to go back to school.

    Multiplication has a higher *PRECEDENCE* than addition. so you should multiply first and then add. The correct answer is thus 14.

    If you *wanted* the answer of 20, you would rewrite the expression as
    Code:
        ( 2 + 3 ) * 4
    ***********

    SAME THING IS TRUE when using logical operators!!! AND has a higher precedence than OR.
    Code:
    WHERE a OR b AND c
    is, effectively,
    Code:
    WHERE a OR ( b AND c )
    If you want to override the normal precedence, you need to explicitly code
    Code:
    WHERE ( a OR b ) AND c
    And of course that is what you need to do here.

    Code:
    SELECT * FROM offers 
    WHERE (country LIKE '%Kingdom%' OR country LIKE '%ALL%' )
    AND active=1
    (And there is no reason at all for the `...` around any of those names. You only need the backticks when the table/field name is a MySQL keyword or is a name that isn't a legal variable name.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, understood

    Thanks for your help.


  •  

    Posting Permissions

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