PDA

View Full Version : zipcode radius search php/mysql


underzen
06-07-2004, 03:54 AM
I just finished a website the other day that I am getting ready to launch that allowed a user to enter their zipcode and then select a radius range to pull database inquiries that fell within this range. I searched on the net for awhile and found a few sites that had some code that didn't work and then I came across a site that someone posted the code that I am now using on my site so I thought I would share.

$sql=@mysql_query("SELECT * FROM area WHERE ZIPCode LIKE '$zipcode'");
//$zipcode is sanitized in the header of the page from a user submission.

$row = mysql_fetch_array($sql);
$lat = $row["lat"]; //pulls latitiude from the zipcode database.
$lon = $row["long"]; // pulls the longitiude from the zipcode database.


$sql1=@mysql_query("SELECT DISTINCT(area.City), area.StateCode, listing.* FROM area INNER JOIN listing ON area.City = listing.city WHERE (POW((69.1*(`long`-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(`lat`-\"$lat\")),\"2\"))<($radius*$radius) ORDER BY 'City' ASC ");
// This sql statment does an inner join and matches the city listed in the zipcode database to the city listed in the listings database. The city is submitted by the user. I got to thinking most people can't type worth a dam and would misspell their city name so as part of the user submission into the listings database the user is required to find their city name based on what state they live in. This is done by using a step system during the submission to the listings database. The first step is to select your state. When you select your state the php script selects all the citys in the state that are listed in the zipcodes database. This way the user can't misspell their city name and we can do a accurate inner join on the listing and zipcodes tables.

I got my zipcodes database from http://www.zipcodedownload.com ('http://www.zipcodedownload.com') .

I don't know if I can or not since its a website of mine, but if you want I can post the website so that you can the script in action.


Eric