Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi All

I am sure this problem is due to the mysql query, so I hope it is ok to post it here, apologies if not ..

I am trying to receive results from my database, based on several conditions, however - I keep getting unexpected results 

SELECT * FROM users WHERE state = "FL OR state = "NY" OR state = "KY" 
+OR state = "Invalid" AND active != "0" ORDER BY user_id ASC LIMIT 0, 
+20

[download]


The key element in that query is the active != "0" because it is returning users regardless of that value, however the below query works fine - it is just a bit smaller 

SELECT * FROM users WHERE state = "FL OR state = "NY" AND active != "0
+" ORDER BY user_id ASC LIMIT 0, 20

[download]


The last one will return users where active != "0" but the first one will not .. I'm really unsure why.

Any tips would be greatly appreciated Thanks

Replies are listed 'Best First'.
Re: Perl/MySQL Problem
by vagnerr (Prior) on Feb 23, 2002 at 16:47 UTC
    I think your problem is that you need to tell SQL what you mean more specificaly. Right now you have 
    
... WHERE <condition1> OR <condition2> OR <condition3> AND <condition4
+>

    [download]
    like this if conditions 1 or 2 are true 3 and 4 won't even be checked as they are irrelivent you already have a true try re-writing your statement with brackets, something like this. 
    ... WHERE (<condition1> OR <condition2> OR <condition3>) AND <conditio
+n4>

    [download]
    Written like this its saying any of the first 3 conditions may be true but must be combined with condition 4 being true (ie you active state check).
    Hope this helps

    ---If it doesn't fit use a bigger hammer
[reply]
[d/l]
[select]
Re: Perl/MySQL Problem
by impossiblerobot (Deacon) on Feb 23, 2002 at 16:47 UTC
    Putting the word 'Perl' in the title does not make this into a Perl question. :-)

    However, your problem is almost certainly caused by precedence. Look at the rules for operator precedence in the MySQL documentation. You will probably need to use parentheses to force the conditions to be evaluated in the order you intend.

    Impossible Robot
[reply]
      Yes "precedence" was the word I was trying to remember, I just had to revert to a code example :-) (++) to you

      ---If it doesn't fit use a bigger hammer
[reply]
Re: Perl/MySQL Problem
by George_Sherston (Vicar) on Feb 23, 2002 at 18:01 UTC
    You're missing a closing quote after FL

    If you want to avoid this kind of typo (and also make your problem a Perl problem ;) ) you could use DBI;. Among the many benefits this confers are placeholders - handy little question marks you'd put instead of FL etc, which handle all the quoting issues for you.

    § George Sherston
[reply]
[d/l]
[select]
Re: Perl/MySQL Problem
by justinNEE (Monk) on Feb 24, 2002 at 16:14 UTC
    select * from users where state in ('FL','NY','KY','Invalid') AND active <> '0'
[reply]
Re: Perl/MySQL Problem
by Cody Pendant (Prior) on Feb 24, 2002 at 11:58 UTC
    I don't like the look of this bit:

    active != "0"

    isn't it supposed to be "<>" for not equals in SQL?

    also, use brackets when you've got complicated ANDs and ORs, it'll help you see it better, like: 

    SELECT * FROM users WHERE 
  (state = "FL" OR state = "NY")
AND 
 (active <> 0)

    [download]
    and are you supposed to be quoting number values?

    I've now reached the end of my SQL knowledge, but is there a difference between active=0 and active="0"? If it's the contents of a number-type field?
    --

[reply]
[d/l]
[select]
      In MySQL, you can use either <> or != interchangeably in the where statement. Ref=pg 253 of MySQL & msql (O'Reilly).

      I have not found that it makes a difference putting quotes around a number or not in my experience, but who's to say I haven't just been lucky?

      -jenn

[reply]

Back to Seekers of Perl Wisdom