Need help with forming a query

zzz

Hi,

I need to write a query which will allow selection of particular user types based on a few criteria.

One of the fields in my t2 contains list of zipcodes (t2.zip) in the following format:

24566,24569,24574 etc.

Basicaly if I have a zip code submitted, say $_POST['zipcode'] = 24574 I need to select my records from my tables that may have that zip code sored in a string in t2.zip among other zip codes.

The quiry needs to look something like:

SELECT t1.value1, t1.value2, t2.value1, t2.value2
FROM t1
LEFT JOIN t2 ON (t1.ID = t2.ID)
WHERE t1.value3 = 'Y'
AND (somehow match zip codes here )

Not sure how to do that...

alimadzi

AND t2.zip IN ('24566', '24569', '24574')

zzz

Thanks for suggestion! Hmm... I think I confused a few things here.

t2.zip is a table cell containing zip codes in a string, like 24566,24569,24574

If I get a value of ZIP code, say $_POST['zipcode'] = 24574 I am afraid the query would have to be a bit different...
Shouldn't it be something like:

AND $_POST['zipcode'] IN (t2.zip)

I don't have access to my file to test at the moment...

alimadzi

You have it backwards. Here's the documentation...

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

zzz

It seems like I have it backwards... it's just the value that I receive externaly has one zip code and each user has a string of zipcodes save in his profile. I need to select the user if the given zip code matches one of the zip codes in user profile.

alimadzi

That's not a good database design as you are storing values that are not atomic. You should have a separate table called something like UsersToZips with each row containing a UserID and an individual zip code.

laserlight

Indeed. At the very least you might want to the read the PHP Builder article on Database Normalization and Design Techniques.

alimadzi

I know that normalizing your database probably sounds like a lot of work, but it's definitely worth it in the long run. You'll be happy you took the time to do it. Let us know if you have any questions...

bradgrafelman

I'm in no way saying you shouldn't normalize your database, but note that there is a function that does what you're trying to do: FIND_IN_SET().

zzz

Yes, db design is not my strength, but after reading the article suggester by laserlight I can pat myself on the back, I did pretty well. not bad for a guy with no formal technical education.

My question sounds like I have a lot of redundant data in my table but it is not. I'll explain. I have a table with over 45,000 lines that contain zip code info. Each user can setup a range that can be anywhere from 25 to 300 miles. Imagine a circle drawn around you with a radius of 300 miles. Every zipcode that is touched by this radius is unique to this particular user. Think of it as a unique geo signature. I decided to add a cell in a table containing user information and store all zips in it separated by commas. Granted, I can have multiple users in the same zip code area, but their ranges could be quite different. I suppose I could attmpt to create a separate table with many-to-many relationships but I've never really done anything like this. It would probably look something like this:

User
------------------------------------------------------
ID    |   Name     |  Geo
1     |   bob      |  2
------------------------------------------------------


Geo
------------------------------------------------------
ID    |   Zip_Range    |   Zips
2     |   12345_100    |   12345, 12346.....
3     |   12345_25     |   12345, 12346.....
------------------------------------------------------

At least this way if I have 40 users from zip code 12345 and 20 of them set the same range of 100 miles all I would have to do is refer them to Geo.Zip_Range = 12345_100.

Am I still on the right track?

By the way, there could be a lot of zip codes withing a 100 miles radius. Is there a way to compact this data into db as opposed to leaving it in text format?

Oh, and not to worry, I won't be creating table with 45K zips time the number of possible ranges. Before I'll assign an id to reference a record from geo table I can take user zip and the range specified, create a var by putting those together and query geo table. If there is no such record, I can create and store it and then take last created id from geo and plug it into user table. In fact I think I'll consider expanding on this idea and store most of the my user zips that way instead of making a cal and compiling the zips each time they do some task. Although... it may need to be thought out properly.

Too bad it's already Friday, I'm just warming up!

ahundiak

$zip = '12345';
SELECT ... WHERE t2.zip LIKE '%$zip%'

zzz

dah... The obvious is not very clear sometimes... Thanks.