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

FAQ

How do I search based on zipcodes

This page's ID: 21781

You are editing "How do I search based on zipcodes"

Who can edit an FAQ?	Anyone registered may edit an FAQ.

Your User Name:
Your Password:
Login Options:	Remember Me On This Computer

Your Text: Insert Slimies » Insert UBB Code » Close Last Tag \| All Tags UBB Help	This has popped up from time to time and basically you need this to get started: [ulist] * A database table holding zipcodes and coordinates or lat/long for each zipcode (city/area name doesn't hurt) * Serverside scripting of some sort [/ulist] 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> [/code] 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 [small](Created by [internallink=582]DmS[/internallink] on 05-13-2004 11:12)[/small] Loading...
Options:	Enable Slimies Enable Linkwords

« Backwards — Onwards »

Show Forum Drop Down Menu