Jump to content

select individual items from comma separated list?


Mike521

Recommended Posts

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

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

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).
Link to comment
Share on other sites

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..
Link to comment
Share on other sites

[!--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.
Link to comment
Share on other sites

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.