...

View Full Version : MySQL Query in PHP



WinDrop
12-14-2011, 08:45 PM
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:

SELECT * FROM `offers` WHERE `country` LIKE '%Kingdom%' OR `country` LIKE '%ALL%' AND `active`=1
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.

guelphdad
12-14-2011, 08:54 PM
order of precedence, just like in math. AND takes precedence over OR just like multiply/divide do over addition and subtraction.


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

WinDrop
12-14-2011, 08:56 PM
order of precedence, just like in math. AND takes precedence over OR just like multiply/divide do over addition and subtraction.


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:

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


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:

SELECT *
FROM `offers`
WHERE `country` LIKE '%Kingdom%'
AND active =1
OR (
`country` LIKE '%ALL%'
AND `active` =1
)

What do you think about that?

Old Pedant
12-14-2011, 09:09 PM
Think back to your elementary school math classes.

What is the value of the expression

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

( 2 + 3 ) * 4


***********

SAME THING IS TRUE when using logical operators!!! AND has a higher precedence than OR.


WHERE a OR b AND c

is, effectively,


WHERE a OR ( b AND c )

If you want to override the normal precedence, you need to explicitly code

WHERE ( a OR b ) AND c

And of course that is what you need to do here.



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

WinDrop
12-14-2011, 09:46 PM
Thank you, understood :)

Thanks for your help.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum