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 Differences $Pages that link to <a href="https://ozoneasylum.com/backlink?for=21781" title="Pages that link to How do I search based on zipcodes Differences" rel="nofollow" >How do I search based on zipcodes Differences\$
+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)

How do I search based on zipcodes Differences

How do I search based on zipcodes Differences $Pages that link to <a href="https://ozoneasylum.com/backlink?for=21781" title="Pages that link to How do I search based on zipcodes Differences" rel="nofollow" >How do I search based on zipcodes Differences\$