Jump to content


Photo

select individual items from comma separated list?


  • Please log in to reply
7 replies to this topic

#1 Mike521

Mike521
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 31 January 2006 - 06:03 PM

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:

$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";

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

#2 fenway

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

Posted 31 January 2006 - 06:08 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Mike521

Mike521
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 31 January 2006 - 06:22 PM

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

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 31 January 2006 - 09:26 PM

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).

#5 Mike521

Mike521
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 31 January 2006 - 10:21 PM

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..


#6 fenway

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

Posted 31 January 2006 - 10:50 PM

[!--quoteo(post=341537:date=Jan 31 2006, 01:22 PM:name=Mike521)--][div class=\'quotetop\']QUOTE(Mike521 @ Jan 31 2006, 01:22 PM) View Post[/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.

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 01 February 2006 - 03:44 AM

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

#8 Mike521

Mike521
  • Members
  • PipPip
  • Member
  • 29 posts

Posted 01 February 2006 - 02:27 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users