Correcting Zipcodes In Mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
idotcom
Forum Commoner
Posts: 69
Joined: Thu Mar 04, 2004 9:24 pm

Correcting Zipcodes In Mysql

Post by idotcom »

Hi

I have a database of records with zipcodes, and some of the zipcodes are only 4 digits long. I think this is because of the program I used, but I need to know if there is any way to modify the records in mysql.

To make a zipcode that looks like this: 8401 Look like this: 08401

Is this possible with a query? Do I have to use php in a loop?

Any info/samples would be super!


Thanks in advance.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

What kind of zip cpdes do you want to store? Do they all have 5 digits? (e.g. Australian zipcodes have 4 digits).

In mysql you can declare a numeric field with a specific length and the attribute ZEROFILL, see http://dev.mysql.com/doc/refman/4.1/en/ ... types.html
or you format the "number" with LPAD(str,len,padstr), see http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html
In php you can use str_pad to get a string of a specific (min) length, see http://de2.php.net/strpad

You may also store the zip code in a text field ;)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You are going to have to either

a) Store the zip codes as varchar(5) ( or varchar(10) depending if you want zip+4) or
b) Manipulate the string code side so that it becomes 5 digits long.

If you are doing calculations on proximity, you are probably going to want to store them as varchar fields so that your selects work properly without a lot of DB side manipulation in your queries.
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Post by daedalus__ »

zip+4 signifies a PO box.. i thought

is every user really going to have a PO box?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Negative, +4 is the postal service way of making deliveries more efficent by zoning delivery areas with the current ZIP (Zone Improvement Plan). All addresses have a +4, not just Boxes.
Post Reply