Jump to content

Archived

This topic is now archived and is closed to further replies.

nathanb

Large SELECT statement w/many OR's

Recommended Posts

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:
[code]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[/code]
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 [u]only one result is returned[/u].

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!

Share this post


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

[code] ... WHERE zip IN ( '41017' , '41011' , '41012' , '41018' , '41019' , '41016' ) ... [/code]

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.

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.