select individual items from comma separated list?

[Mike521]

Hi all, I'm using the following PHP / MySQL code to grab zip codes from our database. this is for a "search by zip code" function. Right now the zip field contains only one zip code, but I'd like to make it a comma separated list of zips, and have mySQL search each of the zips, when there is more than one present. Is that possible? Here's the existing statement:

[code]
$selection = "some columns to select";
$zipcode = "11787"; //this can be any zip code, it's not really hardcoded like this.
$low = $zipcode - 2500; //the lowest number we'll consider a close enough match
$high = $zipcode + 2500; //the highest number we'll consider a close enough match

//the ABS stuff below is just to order the results by the closest to $zipcode

$query = "SELECT $selection, ABS(zipcode-$zipcode) AS distance FROM ourDB WHERE zipcode BETWEEN $low AND $high ORDER BY distance";
[/code]

so we have $zipcode which is a php variable, and zipcode which is a column in our DB. right now the column only has one zip, but I'd like to have multiple zips and still do the whole search in mysql. the alternative is to have a comma separated list, grab ALL the zipcodes into php, explode, and loop through with php.. what a pain..

any ideas? thanks everyone

[fenway]

Huh? Don't even think about storing a "list" in a single field in your table -- that's a very bad idea. I don't see what you're trying to accomplish; try and explain what you need to do.

[Mike521]

too late I already thought about it :) if it's a bad idea then I guess I'm out of luck though

we're picking out items from the DB that have a zipcode within 2,500 of the zipcode defined by $zipcode

problem is the items in the DB *might* have two different zipcodes, in which case I'd like to separate them by commas, hence my first post

I don't know how to make this work in mysql though, I know I can do it in php but I don't like that idea. will do it if I have to

[wickning1]

Putting in more zip codes with comma separation is a very dirty solution. The "correct" way to solve your problem is to use two tables:

ZipInfo (id, whatever, info, you, have)
ZipCodes (zip, infoid)

Where infoid maps to an id in ZipInfo. This way an infinite number of zip codes can point at the info in one row of ZipInfo.

With this structure you can also have a single zip code point to multiple rows of ZipInfo. To avoid this, make zip your primary key (enforces uniqueness).

[Mike521]

thanks for the help, I'm a little confused though

if we have a separate table that has zipcodes and id numbers, how will I store multiple zip codes for one id?

once that's done I'll need to modify how I search through them also..

I'm not a mysql expert so my apologies if I'm skipping over the obvious..

[fenway]

[!--quoteo(post=341537:date=Jan 31 2006, 01:22 PM:name=Mike521)--][div class=\'quotetop\']QUOTE(Mike521 @ Jan 31 2006, 01:22 PM) [snapback]341537[/snapback][/div][div class=\'quotemain\'][!--quotec--]
problem is the items in the DB *might* have two different zipcodes[/quote]

I don't understand what you mean by this.

[wickning1]

Let's say for instance that the ZipInfo table is storing states:

1 -> Texas
2 -> Iowa
3 -> California

You can insert data into the ZipCodes table to look something like this:

78655 -> 1
78666 -> 1
50322 -> 2
90210 -> 3

now because 78655 and 78666 are both pointing to 1, and 1 is pointing to Texas, you know that both 78655 and 78666 are in Texas. But the word "Texas" is only in the database once. This is called normalizing your data and is generally good design.

As far as SQL to get the data out, you need to join the tables together. For this database, it'd be something like:
SELECT i.state, c.zip FROM ZipCodes c, ZipInfo i WHERE c.infoid=i.id

[Mike521]

ahh I see what you mean, that does look like a much better option. Thanks! I'm gonna get to work on this and see how it goes