correct AND, OR query syntax

sonny · 11-28-2012, 12:03 AM

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

Old Pedant · 11-28-2012, 01:51 AM

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.

sonny · 11-28-2012, 03:55 AM

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

durangod · 11-28-2012, 05:02 AM

Quote:

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..

Old Pedant · 11-28-2012, 05:45 AM

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*

Old Pedant · 11-28-2012, 05:53 AM

Quote:

Originally Posted by sonny

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!!!

Old Pedant · 11-28-2012, 05:57 AM

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' ) )

Old Pedant · 11-28-2012, 06:03 AM

Quote:

Originally Posted by durangod

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

sonny · 12-01-2012, 07:27 AM

Quote:

Originally Posted by Old Pedant

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