Jump to content


Photo

Large SELECT statement w/many OR's


  • Please log in to reply
2 replies to this topic

#1 nathanb

nathanb
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 10 April 2006 - 11:46 PM

Alright, so I have a PHP script that helps me select the zip codes in a certain radius of a certain zip code. This works perfect, and gives me an array including the valid zip codes. I want to use this array to select users who have these zip codes, so I used some more PHP to help me make a query like this:
SELECT * FROM users WHERE (zip = '41017' OR zip = '41011' OR zip = '41012' OR zip = '41018' OR zip = '41019' OR zip = '41016') AND valid_email = '1' ORDER BY username ASC LIMIT 0, 20
So, in theory, this would select any users who have one of the above zip codes. It appears to find the right users correctly, but only one result is returned.

For example, I have 2 users with the zip '41017'. This query only returns the first of these users (depending on if the ORDER BY is ASC or DESC), instead of both.

Any ideas? Also, is there a better way to do this? Because as the radius increases, the number of applicable zip codes increases exponentially.

Thanks for any help!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 April 2006 - 01:40 PM

Well, you should be using a IN() clause, or possibly a BETWEEN condition, depending on how you're getting this range. That is:

... WHERE zip IN ( '41017' , '41011' , '41012' , '41018' , '41019' , '41016' ) ...

As far as the number of rows returned, your LIMIT clause should be returning the first 20 records; try running a COUNT() with no ORDER / LIMIT clauses to make sure that you're getting the number you expect.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 nathanb

nathanb
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 11 April 2006 - 08:01 PM

Ah, thank you! It turned out to be an error in my PHP code, but the IN() clause sure looks like a smarter way to accomplish this. Thanks a lot!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users