searching a mysql database for the first three zip code digi

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hjones21
Forum Newbie
Posts: 4
Joined: Mon Apr 24, 2006 3:33 pm

searching a mysql database for the first three zip code digi

Post by hjones21 »

Hey all,
Hope someone out there can help a new up and coming php guy :D
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
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Are you storing the zipcode as an INT field or a string?

If string you can use a wildcard: "%"
hjones21
Forum Newbie
Posts: 4
Joined: Mon Apr 24, 2006 3:33 pm

Post by hjones21 »

Right now i am posting as a string?



WHERE zip = '$_POST[zip]' ORDER BY zip

thanks
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Try this:

Code: Select all

$zip = $_POST['zip'];////YOU NEED TO VALIDATE HERE!!!!!!!!!
WHERE zip like '$zip%' ORDER BY zip
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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";
?>
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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 :!:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
hjones21
Forum Newbie
Posts: 4
Joined: Mon Apr 24, 2006 3:33 pm

Post by hjones21 »

Fantastic.....thank you for your input!! GREAT HELP!
Post Reply