Page 1 of 1
searching a mysql database for the first three zip code digi
Posted: Mon Apr 24, 2006 3:44 pm
by hjones21
Hey all,
Hope someone out there can help a new up and coming php guy

i have a mysql database that contains a numerous amount of zip codes. Right now i have a zip code searching going and it is working just fine. But how can i do that same zip code search by searching with only the first three digits of the zip code entered by the user?
thanks
Posted: Mon Apr 24, 2006 3:48 pm
by hawleyjr
Are you storing the zipcode as an INT field or a string?
If string you can use a wildcard: "%"
Posted: Mon Apr 24, 2006 4:04 pm
by hjones21
Right now i am posting as a string?
WHERE zip = '$_POST[zip]' ORDER BY zip
thanks
Posted: Mon Apr 24, 2006 4:07 pm
by hawleyjr
Try this:
Code: Select all
$zip = $_POST['zip'];////YOU NEED TO VALIDATE HERE!!!!!!!!!
WHERE zip like '$zip%' ORDER BY zip
Posted: Mon Apr 24, 2006 5:27 pm
by RobertGonzalez
You could also do an exact match using the LEFT() mysql function. Not sure what the results would be as far as performance, but it could be done.
Code: Select all
<?php
$userzip = $_POST['zip']; // Validate this for security reasons
$sql = "SELECT LEFT('zip', 3) AS zipcode FROM my_table WHERE zipcode = $userzip";
?>
Posted: Mon Apr 24, 2006 6:39 pm
by hawleyjr
Everah wrote:You could also do an exact match using the LEFT() mysql function. Not sure what the results would be as far as performance, but it could be done.
Code: Select all
<?php
$userzip = $_POST['zip']; // Validate this for security reasons
$sql = "SELECT LEFT('zip', 3) AS zipcode FROM my_table WHERE zipcode = $userzip";
?>
Another thing to keep in mind is some zip code databases will have zip codes with leading zeros not show the leading zero. so 00564 would be 564

Posted: Mon Apr 24, 2006 8:05 pm
by RobertGonzalez
I was thinking on the way home that the query I put submitted was not correct. It will truncate the zip field and return only the first three chars of the thing. That is probably not what the poster wants. I think this might be in order...
Code: Select all
<?php
$userzip = $_POST['zip']; // Validate this for security reasons
$sql = "SELECT zip, LEFT('zip', 3) AS zipcode FROM my_table WHERE zipcode = $userzip";
?>
hawleyjr wrote:Another thing to keep in mind is some zip code databases will have zip codes with leading zeros not show the leading zero. so 00564 would be 564.
I store my zip codes as string values to keep this from happening. My very first zip code app ran into the very problem that you mentioned. It didn't take long for me to change the field type.
Posted: Mon Apr 24, 2006 9:50 pm
by hjones21
Fantastic.....thank you for your input!! GREAT HELP!