Jump to content

Archived

This topic is now archived and is closed to further replies.

jeffrydell

ZIP Code & Field Attributes

Recommended Posts

I have a table of all US Zip Codes, and I have subscribers to my site who entered their Zip Codes when registering for a newsletter. The ZIPCODE table has a VARCHAR field for ZIP, as does the SUBSCRIBER table. All is good so far.

When I try to find out how many subscribers I have in each state, everyone on the east coast gets left out ... when looking for a Zip Code that starts with a 0, the result comes back without a match.

What to do so that PHP and MySQL recognize that the leading 0 should NOT be dropped?

Thanks for your help!

Share this post


Link to post
Share on other sites
[!--quoteo(post=386882:date=Jun 22 2006, 12:38 PM:name=jeffrydell)--][div class=\'quotetop\']QUOTE(jeffrydell @ Jun 22 2006, 12:38 PM) [snapback]386882[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I have a table of all US Zip Codes, and I have subscribers to my site who entered their Zip Codes when registering for a newsletter. The ZIPCODE table has a VARCHAR field for ZIP, as does the SUBSCRIBER table. All is good so far.

When I try to find out how many subscribers I have in each state, everyone on the east coast gets left out ... when looking for a Zip Code that starts with a 0, the result comes back without a match.

What to do so that PHP and MySQL recognize that the leading 0 should NOT be dropped?

Thanks for your help!
[/quote]

You just need to be sure that the zip code is always treated as a string throughout your code. Do you quote your zip code when searching? I.e., "00138" vs. 00138? Are you sure the zip codes have been entered into the database correctly -- strings with leading zeros? Perhaps there's an implicit type conversion occuring if you use some kind of numeric comparison (less than, greater than, etc).

VARCHAR is good! [img src=\"style_emoticons/[#EMO_DIR#]/smile.gif\" style=\"vertical-align:middle\" emoid=\":smile:\" border=\"0\" alt=\"smile.gif\" /]

Sorry, but I can't be more specific without more details of where things are going badly for your script.

Share this post


Link to post
Share on other sites
[!--quoteo(post=386904:date=Jun 22 2006, 01:32 PM:name=Wildbug)--][div class=\'quotetop\']QUOTE(Wildbug @ Jun 22 2006, 01:32 PM) [snapback]386904[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You just need to be sure that the zip code is always treated as a string throughout your code. Do you quote your zip code when searching? I.e., "00138" vs. 00138? Are you sure the zip codes have been entered into the database correctly -- strings with leading zeros? Perhaps there's an implicit type conversion occuring if you use some kind of numeric comparison (less than, greater than, etc).

VARCHAR is good! [img src=\"style_emoticons/[#EMO_DIR#]/smile.gif\" style=\"vertical-align:middle\" emoid=\":smile:\" border=\"0\" alt=\"smile.gif\" /]

Sorry, but I can't be more specific without more details of where things are going badly for your script.
[/quote]

Thanks for the leads. The zip contains the leading zero, I can see it if I browse the ZIPCODE table and if I browse the SUBSCRIBER table, so there's no issue with that.

Here's what I'm doing:

function getstate ($zip2find)
{
$fres = mysql_query("SELECT * FROM `zipcodes` WHERE `zipcode` LIKE $zip2find");
$frow = mysql_fetch_row($fres);

RETURN $frow[2]; // State Code
}


// Start with an array for the subscribers and open the active subscriber table.
$subscriptions = array();
$arrayrow = '0';

$res = mysql_query("SELECT * FROM `subscribers` WHERE `unregistered` IS NULL or `unregistered` LIKE '0000-00-00 00:00:00'");

// Walk through the list & add an array row for each active subscriber, which will include the state code in which they reside.

while ($row = mysql_fetch_array($res))
{
IF ($row['zipcode'] == "")
{
$subscriptions[$arrayrow] = "?";
echo "This one is a blank. <br>\n";
++$arrayrow;
}
ELSE
{
$subscriptions[$arrayrow] = getstate($row['zipcode']);
echo $row['zipcode'] . " is in the state of " . $subscriptions[$arrayrow] . " <br>\n";
++$arrayrow;
}
}

Do you see anything in this code?

Thanks again for your help!

Jeff

Share this post


Link to post
Share on other sites

Here's what I see:

1. (Not relevant) [i]$arrayrow = '0'[/i] should be [i]$arrayrow = 0[/i] (it's numeric, not string).
2. SELECT * FROM `zipcodes` WHERE `zipcode` LIKE $zip2find
This should probably be either:
[code]
"SELECT * FROM zipcodes WHERE zipcode='$zip2find'"
- or -
"SELECT * FROM zipcodes WHERE zipcode LIKE '$zip2find'"[/code]
(See the quotes around $zip2find?)

If $zip2find is always the exact zipcode, then you don't need to use LIKE; you can use the "=" operator. Regardless, you need to use quotes since it's a string.

Extra note: In fact, if you're searching on a substring of the zipcode (which would be, what, the first few digits?), then you'd need to use "LIKE '$zip2find%'". The '%' is part of the LIKE syntax and means zero or more characters (like the * filename wildcard).

Hope that's helpful.

Share this post


Link to post
Share on other sites
[!--quoteo(post=386944:date=Jun 22 2006, 03:48 PM:name=Wildbug)--][div class=\'quotetop\']QUOTE(Wildbug @ Jun 22 2006, 03:48 PM) [snapback]386944[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Here's what I see:

...

Hope that's helpful.
[/quote]


Indeed it WAS helpful ... the function is all better now, the Northeast has been reunited with the rest of the U.S.!

Thanks for your time.

Jeff

Share this post


Link to post
Share on other sites

×

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.