/    Sign up×
Community /Pin to ProfileBookmark

Programming zip codes, need some thoughts

Copy linkTweet thisAlerts:
Oct 29.2005

Hello,

I have been given the task to write the following program:

This company has a bunch of salesman that are each given areas of the country to sell to, based on zip codes. So that salesman 1 would get 10000 – 30000 range of zip codes or something like that. The owner wants a form that asks for the customers zip code and then based on that, sends the form info to the appropriate salesman in their area.

I was wondering if there is a way to get the zipcode and see if it falls in a certain range and if its possible to do that in MySQL. I hate to have to write every zip code into the database, there are way too many, not to mention the size that the table would get too.

Let me know your thoughts…

to post a comment
PHP

11 Comments(s) ↴

Copy linkTweet thisAlerts:
@LiLcRaZyFuZzYOct 29.2005 — indeed you could check if it is within a certain range
[code=php]
<?php
$zip_code = 21548;
switch($zip_code){
case $zip_code > 10000 && $zip_code <= 30000:
echo "Between 10000 and 30000";
break;
case $zip_code > 30000 && $zip_code <= 50000;
echo "Between 30000 and 50000";
break;
#etc...
}
?>
[/code]

[B]but then again, i have no idea of the zip code format you use[/B]
Copy linkTweet thisAlerts:
@ShrineDesignsOct 29.2005 — try[code=php]<?php
function between($int, $min, $max)
{
return ($int >= $min && $int <= $max);
}
$zip = 11111;

switch(true)
{
case between($zip, 10000, 29999):
$rep = 1;
break;
case between($zip, 30000, 59999):
$rep = 2;
break;
// etc.
}
?>[/code]
Copy linkTweet thisAlerts:
@mididelightauthorOct 29.2005 — thank you for your advice, the code helped. so would the database table column type be int or something else?
Copy linkTweet thisAlerts:
@ShrineDesignsOct 30.2005 — i would use a integer column type, zip codes are numeric
Copy linkTweet thisAlerts:
@tbirnsethOct 30.2005 — i would use a integer column type, zip codes are numeric[/QUOTE]

If you want to limit yourself to the older style zip codes in the US only then you'd use numeric. I'd store it as a string and let PHP do the type conversion when it does the comparisons.

To do this correctly, you need to also handle a zip+4 style format. I.e. 97201-3795. Now the problem gets more complicated. Additionally if your salespeople may ever deal with Canada or Mexico, then you need to handle alpha characters in your comparison. You would then take a different approach.

I'd strongly suggest your company simply subscribe to a zip code database (USPS had the most complete and up-to-date one). They will send you updates monthly/quarterly that you can simply import into your database. Cost is nominal. It will also give you city/state information as well some other stuff (don't remember exactly what).

The other thing you could do is determine a method for allocation. I.e. Salespeople are mapped to the first two characters of the POSTAL code (versus zip code). Then you can dynamically assign them to salespeople and build that table as you encounter them.

cheers,

tony
Copy linkTweet thisAlerts:
@NogDogOct 30.2005 — i would use a integer column type, zip codes are numeric[/QUOTE]
Well, they are in the US if you just use the first 5 digits, but you normally have a hyphen if you add the optional extra 4 digits. And postal codes in other countries often have letters as well as numbers. If you know that your sales people will only be in the US and that you'll only need the basic 5-digit zip code, an integer will work fine, except you may have to left pad them with zeros if you output them in any manner (e.g. '08077' will be stored as '8077').
Copy linkTweet thisAlerts:
@mididelightauthorOct 30.2005 — big thanks for all your advise. i will have to consult with the boss and see what format of zip codes they will be using. By any chance how big would of a table would a database of zip codes be? In terms of row count?
Copy linkTweet thisAlerts:
@chazzyOct 30.2005 — US only would be under 10,000 rows.

canada's much smalller as well. but it has higher possibilities, i think its usually @@#### but i might be wrong.
Copy linkTweet thisAlerts:
@SpectreReturnsNov 01.2005 — Canada is @#@ #@#, and Mexico is %%%%%.

@ Alpha

# Numeric


% Alphanumeric
Copy linkTweet thisAlerts:
@LiLcRaZyFuZzYNov 01.2005 — Germany is

D-#####

France is

F-#####

#: Numeric

hehehe ?
Copy linkTweet thisAlerts:
@mididelightauthorNov 08.2005 — thanks for the help, much appreciated
×

Success!

Help @mididelight spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.25,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...