How do I search based on zipcodes — OZONE Asylum, home of the Mad Scientists

Welcome to the OZONE Asylum FAQWiki! Frequently Asked Questions Server Side Coding General server-side coding
How do I search based on zipcodes $Pages that link to <a href="https://ozoneasylum.com/backlink?for=21781" title="Pages that link to How do I search based on zipcodes" rel="nofollow" >How do I search based on zipcodes\$
This has popped up from time to time and basically you need this to get started: A database table holding zipcodes and coordinates or lat/long for each zipcode (city/area name doesn't hurt) Serverside scripting of some sort An sql-script with a list of appr 40.000 US-zipcodes can be found here: http://deanspace.org/download/zipcodes.mysql Formulas for calculating distances based on lat/long can be found here: http://www.meridianworlddata.com/HTML7/distances-from-city-to-city-2.asp?guid=B5DF02C4-82FA-4141-9136-55D911A840DB An example on a simple page that does the calculations on the fly (in php) is here: First generate a db in mysql called "uszipcodes" then run the sql script linked above. code: <?php ////////////////////// DB-Access ///////////////////////// /Generates the connection with the database and returns a valid connection for the one calling. / function hookUpDb(){ Global $connection; $host = 'localhost'; $user = ''; $password = ''; $db = 'uszipcodes'; if(mysql_connect($host,$user,$password)){ $connection = mysql_connect($host,$user,$password); mysql_select_db($db,$connection); return $connection; }else{ print("<b>Sorry, cannot connect to the database.</b>"); } } ///////////////////////////////////////////////////// ////////////////////// Generic QueryAskers/////////// /Use when no returned result is excepted. returns true or mysql_error. / function doQuery($sql){ Global $connection; hookUpDb(); if(!mysql_query($sql,$connection)){ return mysql_error(); } return true; } /Use this when you need to use custom made SQL-queries on the database. Returns a 2 dimensional array with the result. / function doQueryResult($sql){ Global $connection; hookUpDb(); if($sql != ""){ $result = mysql_query($sql,$connection); $item = array(); while($item = mysql_fetch_array($result)){ $items[] = $item; } return $items; mysql_free_result($result); } else { print("Need correctly formed SQL that returns a resultset."); } } ///////////////////////////////////////////// //Calculator... if(isset($_POST['doItNow']) && !empty($_POST['userZip']) && !empty($_POST['radius'])){ $userZip = $_POST['userZip']; $userRadius = $_POST['radius']; $sql_1 = "SELECT * FROM zipcodes WHERE zip = '".$userZip."'"; $result = doQueryResult($sql_1); $userLat = $result[0]['latitude']; $userLong = $result[0]['longitude']; $userLocation = $result[0]['city']." - ".$result[0]['state']; $sql_2 = "select * from zipcodes as z where (SQRT( (69.1 * (".$userLat." - z.latitude)) * (69.1 * (".$userLat." - z.latitude)) + (53.0 (".$userLong." - z.longitude)) (53.0 *(".$userLong." - z.longitude))) <= ".$userRadius." )"; $finalResult = doQueryResult($sql_2); } ?> <!doctype html public "-//W3C//DTD HTML 4.0 //EN"> <html> <head> <title>Get zips within distance</title> </head> <body> <form action="#" method="post"> enter zipcode: <input type="text" name="userZip" size="20" value="<? echo $_POST['userZip'] ?>"><br> enter radius in miles: <input type="text" name="radius" size="20" value="<? echo $_POST['radius'] ?>"><br> <input type="submit" name="doItNow" value="Search"> </form> <?php if(is_array($finalResult)){ foreach($finalResult as $location){ print("<fieldset><legend>Location within ".$_POST['radius']." miles from ".$userLocation.".</legend>\n"); print("City: ".$location['city']."<br>State: ".$location['state']."<br>zipcode: ".$location['zip']); print("</fieldset>"); } } ?> </body> </html> Note that this is only one way of doing it and that it probably will draw a lot of power from the server with a lot of traffic. In this thread a lot of useful variations on this is discussed: http://www.ozoneasylum.com/21687 Have fun/Dan (Created by DmS on 05-13-2004 11:12)

This has popped up from time to time and basically you need this to get started:

code:

<?php

////////////////////// DB-Access /////////////////////////

/*Generates the connection with the database and returns a

valid connection for the one calling. */

function hookUpDb(){

    Global $connection;

    $host = 'localhost';

    $user = '';

    $password = '';

    $db = 'uszipcodes';

    if(mysql_connect($host,$user,$password)){

       $connection = mysql_connect($host,$user,$password);

       mysql_select_db($db,$connection);

       return $connection;

    }else{

       print("<b>Sorry, cannot connect to the database.</b>");

    }

}



/////////////////////////////////////////////////////

////////////////////// Generic QueryAskers///////////

/*Use when no returned result is excepted.

returns true or mysql_error. */



function doQuery($sql){

 Global $connection;

    hookUpDb();



    if(!mysql_query($sql,$connection)){

    return mysql_error();

    }



return true;

}



/*Use this when you need to use custom made SQL-queries on the database.

Returns a 2 dimensional array with the result. */

function doQueryResult($sql){

Global $connection;

    hookUpDb();

    if($sql != ""){

    $result = mysql_query($sql,$connection);

        $item = array();

        while($item = mysql_fetch_array($result)){

        $items[] = $item;

        }

    return $items;

    mysql_free_result($result);

    } else {

    print("Need correctly formed SQL that returns a resultset.");

    }

}

/////////////////////////////////////////////



//Calculator...

if(isset($_POST['doItNow']) && !empty($_POST['userZip']) && !empty($_POST['radius'])){

    $userZip = $_POST['userZip'];

    $userRadius = $_POST['radius'];

    $sql_1 = "SELECT * FROM zipcodes WHERE zip = '".$userZip."'";

    $result = doQueryResult($sql_1);

    $userLat = $result[0]['latitude'];

    $userLong = $result[0]['longitude'];

    $userLocation = $result[0]['city']." - ".$result[0]['state'];

    $sql_2 = "select * from zipcodes as z where (SQRT( (69.1 * (".$userLat." - z.latitude)) * (69.1 * (".$userLat." - z.latitude)) + (53.0 *(".$userLong." - z.longitude)) * (53.0 *(".$userLong." - z.longitude))) <= ".$userRadius." )";

    $finalResult = doQueryResult($sql_2);

}

    

    



?>

<!doctype html public "-//W3C//DTD HTML 4.0 //EN">



<html>

<head>

       <title>Get zips within distance</title>

</head>

<body>



<form action="#" method="post">

enter zipcode: <input type="text" name="userZip" size="20" value="<? echo $_POST['userZip'] ?>"><br>

enter radius in miles: <input type="text" name="radius" size="20" value="<? echo $_POST['radius'] ?>"><br>

<input type="submit" name="doItNow" value="Search">

</form>

<?php

if(is_array($finalResult)){

    

    foreach($finalResult as $location){

        print("<fieldset><legend>Location within ".$_POST['radius']." miles from ".$userLocation.".</legend>\n");

        print("City: ".$location['city']."<br>State: ".$location['state']."<br>zipcode: ".$location['zip']);

        print("</fieldset>");

    }

}

?>

</body>

</html>

Note that this is only one way of doing it and that it probably will draw a lot of power from the server with a lot of traffic.
In this thread a lot of useful variations on this is discussed:
http://www.ozoneasylum.com/21687

Have fun/Dan

(Created by DmS on 05-13-2004 11:12)