ZIP Code & Field Attributes

[jeffrydell]

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!

[Wildbug]

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

[jeffrydell]

[!--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

[Wildbug]

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.

[jeffrydell]

[!--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