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 9 of 9
  1. #1
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts

    correct AND, OR query syntax

    Hi not sure where to post, here or in the php forum

    anyone know why this works
    PHP Code:
    mysql_query("SELECT * FROM hits WHERE vc=1 AND country = 'us' AND ua NOT LIKE '%bot%'"); 
    but not this? I also tried OR, vc and country need to match exact, but not like either bot or spider
    PHP Code:
    mysql_query("SELECT * FROM hits WHERE vc=1 AND country = 'us' AND ua LIKE '%bot%' || ua LIKE '%spider%' || state NOT LIKE 'Florida'"
    Thanks
    Sonny
    Last edited by sonny; 11-28-2012 at 12:29 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,333 Times in 4,299 Posts
    Have you heard of PRECEDENCE of operators?

    If you took junior high school math, you should have.

    For example, what is the value of
    Code:
         3 + 4 * 2
    ??

    If you said 14, then back to school for you!

    Multiplication has a higher precedence than addition, so that expression is evaluate as if you had coded
    Code:
        3 + ( 4 * 2 )
    for an answer of 11.

    If you WANTED the answer of 14, you should have written
    Code:
        ( 3 + 4 ) * 2
    ***********

    Guess what? SAME THING applies to LOGICAL operators. Yes, in SQL. And in PHP and in JavaScript and in every other standard computer language.

    AND has a higher precedence than OR.
    && has a higher precedence than ||.
    & has a higher precedence then |.

    So... What you need is:
    Code:
    $sql = "
        SELECT * FROM hits 
        WHERE vc=1 AND country = 'us' 
        AND ( ua LIKE '%bot%' || ua LIKE '%spider%' || state NOT LIKE 'Florida')  
        ";
    
    mysql_query( $sql )
    Incidentally: Personally, I would use AND and OR in SQL, because ANSI SQL doesn't support && and || even if MySQL does. Be prepared for the day you may use some other DB than MySQL.
    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.

  • #3
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Good point about using "OR" I will change that tonight,

    Just curious why will that not work with any more || at the end ?
    PHP Code:
    ua LIKE '%spider%' || state NOT LIKE 'Florida' || state NOT LIKE 'Maine' || state NOT LIKE 'MNew York' 
    Thanks
    Sonny

  • #4
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,376
    Thanks
    263
    Thanked 32 Times in 31 Posts

    Guess what? SAME THING applies to LOGICAL operators. Yes, in SQL. And in PHP and in JavaScript and in every other standard computer language.

    AND has a higher precedence than OR.
    && has a higher precedence than ||.
    & has a higher precedence then |.
    Wow i never knew that either, i knew about the math operators, (i could google it but lets see if i remember) order is division, multiplication, addition, subtraction i think. lol now i will google see if im wrong lol..

    That blows my mind a bit about the logical operators.

    That is just one of many reasons folks the really good coders charge a good price for their work, they earn it, they deserve it, and they need all that cash to stuff in their ears to keep this kind of stuff from leaking out lol..

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,333 Times in 4,299 Posts
    Actually, we need all that cash so we can burn it to keep warm. We are too busy coding to find some other source of heat.

    ... OR ...

    Actually, we need all that cash to pay our psychiatrists who keep us from going crazy.

    ... OR ...

    Actually, we need all that cash so our wives will go spend it and keep out of our hair while we are coding.

    ... OR ...

    Actually, I've never seen a pile of cash from programming. *sigh*
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,333 Times in 4,299 Posts
    Quote Originally Posted by sonny View Post
    Just curious why will that not work with any more || at the end ?
    PHP Code:
    ua LIKE '%spider%' || state NOT LIKE 'Florida' || state NOT LIKE 'Maine' || state NOT LIKE 'MNew York' 
    TCH! You aren't thinking logically!

    Let's say that state *IS* Maine.

    (And for the sake of argument, us is not like spider.)

    Okay, so now evaluate that condition:
    Code:
    ( ua LIKE '%spider%' || state NOT LIKE 'Florida' || state NOT LIKE 'Maine' || state NOT LIKE 'New York' )
    Clearly, state is NOT LIKE either Florida or New York if it *is* like Maine, right?

    So those conditions then evaluate to
    Code:
    ( false || true || false || true )
    and the overall condition is thus TRUE.

    Now assume that state is Florida. *NOW* those conditions will evaluate to
    Code:
    ( false || false || true || true )
    and AGAIN the overall condition is TRUE.

    TRY IT WITH ANY STATE YOU LIKE. Heck, let's say that state is Confused!

    So those conditions evalueat to
    Code:
    ( false || true || true || true )
    and AGAIN the overall condition is TRUE.

    IN FACT, no matter WHAT state you use, the overall condition WILL AND MUST BE ALWAYS TRUE!!!
    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.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,333 Times in 4,299 Posts
    If what you are trying to do is exclude all 3 of those states you *MUST* do it thus:

    Code:
    ( ua LIKE '%spider%' || NOT ( state LIKE 'Florida' || state LIKE 'Maine' || state LIKE 'New York' ) )
    *** HOWEVER ***

    You should *NEVER* use LIKE (or NOT LIKE) unless you have wild card characters (e.g., % character[s]) in the string being checked.

    So you *REALLY* should code that as:
    Code:
    ( ua LIKE '%spider%' || NOT ( state = 'Florida' || state = 'Maine' || state =  'New York' ) )
    Except NOW you can collapse that down to the simpler and more efficient
    Code:
    ( ua LIKE '%spider%' || state NOT IN ( 'Florida','Maine','New York' ) )
    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.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,333 Times in 4,299 Posts
    Quote Originally Posted by durangod View Post
    lets see if i remember) order is division, multiplication, addition, subtraction
    Actually, multiplication and division have the same precedence. And addition and subtraction have the same precedence.

    And why Google when MySQL spells it out carefully for you:
    http://dev.mysql.com/doc/refman/5.5/...recedence.html
    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.

  • #9
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Actually, multiplication and division have the same precedence. And addition and subtraction have the same precedence.

    And why Google when MySQL spells it out carefully for you:
    http://dev.mysql.com/doc/refman/5.5/...recedence.html
    That's good to know, if I ever get reincarnated one day.

    as for that query I so elequantly mentioned above, I did see my screwed up logic after I
    woke up, its amazing what sleep can do sometimes.

    Sonny


  •  

    Posting Permissions

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