Zip Code to City/State and Vice-Versa in a Database

Zip Code to City/State and vice-versa in a database?

You have a couple options. You can buy a bulk zip-code library from somebody which will list zip codes, cities, counties, etc. by state, or you can pay someone to access a web service which will perform the same function on a more granular level.

Your best bet would be to go with the zip-code library option, as it'll cost you less than the web service and will provide better performance. How you query or pre-process this library is up to you. You mention SQL Server, so you'd probably want State, Zipcode, and City tables, and include the relevant relationships between them. You'll also need to have provisions for cities that span multiple zipcodes, or for zipcodes that have multiple cities - but none of these issues are insurmountable.

As far as dealing with the vagarities of user input, you may consider enlisting the help of an address validation web service, although most of them require a full shipping address in order to validate.

Edit: looks like there's a SourceForge project offering free zip-code data, including lat/lon data, etc. Not sure how correct or current it is.

Edit 2: After some cursory looking on that SourceForge project's site it looks like this is a dead project. If you use this data, you'll need to provide some allowance for zipcodes / cities that don't exist in your database. Purchased bulk libraries usually come with some sort of guarantee of updates, or a pricing plan for updates, etc., and are probably more reliable.

Auto populate city textfield by entering zip and vice versa

AJAX can do the trick. You may try something like this: LINK.

I suggest you to use jQuery, it's much cleaner code for AJAX than coding it yourself.

Auto fill country and city from zip code, and the oposite

*This is very easy what u need to do is just get the zip code from the user and with that you can get the $country and $city using google api.here i give you an example of how i am getting longitude and latitude from the postalcode i.e. zip code.

$postcode=$_POST('postcode');
                if($postcode)
                {

                    $address = urlencode($postcode);
                    $url='http://maps.googleapis.com/maps/api/geocode/json?address='.$address.'&sensor=false';

                    $ch = curl_init(); 
                    curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
                    curl_setopt($ch, CURLOPT_URL, $url);
                    $data = curl_exec($ch);
                    curl_close($ch);
                    $source = $data;
                    $obj = json_decode($source);
                    $lat = $obj->results[0]->geometry->location->lat;
                    $long = $obj->results[0]->geometry->location->lng;

                }
$longitude=$long;
$latitude=$lat;

than you can insert the above two variables $longitude and $latitude in your database.simple as that :) .go to the following link https://developers.google.com/maps/documentation/geocoding/?csw=1

now for getting the country name from longitude and latitude use reverse geocoding.
supply a comma-separated latitude/longitude pair in the latLng parameter.

 var geocoder;
  var map;
  var infowindow = new google.maps.InfoWindow();
  var marker;
  function initialize() {
    geocoder = new google.maps.Geocoder();
    var latlng = new google.maps.LatLng(40.730885,-73.997383);
    var mapOptions = {
      zoom: 8,
      center: latlng,
      mapTypeId: google.maps.MapTypeId.ROADMAP
    }
    map = new google.maps.Map(document.getElementById("map-canvas"), mapOptions);
  }

  function codeLatLng() {
    var input = document.getElementById("latlng").value;
    var latlngStr = input.split(",",2);
    var lat = parseFloat(latlngStr[0]);
    var lng = parseFloat(latlngStr[1]);
    var latlng = new google.maps.LatLng(lat, lng);//here you will get your country and city name
    geocoder.geocode({'latLng': latlng}, function(results, status) {
      if (status == google.maps.GeocoderStatus.OK) {
        if (results[1]) {
          map.setZoom(11);
          marker = new google.maps.Marker({
              position: latlng,
              map: map
          });
          infowindow.setContent(results[1].formatted_address);
          infowindow.open(map, marker);
        }
      } else {
        alert("Geocoder failed due to: " + status);
      }
    });
  }

read about reverse geocodng here and modify the code according to your needs https://developers.google.com/maps/documentation/javascript/geocoding#ReverseGeocoding

Use : getLocations(latlng:GLatLng, callback:function)

This method performs reverse-geocoding, the conversion of a latitude/longitude pair into human-readable addresses. getLocations() sends a request to the Google geocoding service, asking it to return the address for the given latlng and pass the response in the given callback.

As this method requires a call to a Google server, you must also pass a callback method to handle the response. This response will contain a Status code, and if successful, one or more Placemark objects.

Note that this method may instead pass an addressable String, as indicated above; in that case, the service will do a standard geocode. If however, the first argument contains a GLatLng, the service will do a reverse-geocode.

filter out new york city zip codes

$db = new mysqli('localhost', 'NAME', 'PASSWORD', 'DATABASENAME');
if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}
$query = "SELECT something FROM zipcodes WHERE zipcode BETWEEN 10000 AND 10163";
$result = mysqli_query($db, $query);
while ($row = mysqli_fetch_assoc($result))
{
      echo "Something =".$row["something"];
}

SQL query to get state,then city and address and zip

I think you need the SEPERATOR argument in the GROUP_CONCAT() function.
https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT state, GROUP_CONCAT('(',address,';)' SEPARATOR '') as        
address,GROUP_CONCAT('(',zip,';)' SEPARATOR '') as zip FROM location Where   
username='ABC@gmail.com' GROUP BY state,city"

---

Related Topics