Hi Matt,
Thanks for getting back to me with your solution. I'm having a little bit of trouble following the php syntax for the final statement (I don't know php). Could you please write the last statement in pseudocode?
Thanks, Stefan
At 10:07 AM 4/27/2005, you wrote:
Stefan,
I use the following MySQL Query for a PHP site I run (plug: www.lifeknot.com :) to determine all results within N miles of the zip code the user has entered.
And, yes, you will need to procure a zip code database. I got mine from ZipCodeWorld. http://www.zipcodeworld.com/
FIRST I get the latitude and longitude for the zip code they entered
// get city, state, latitude and longitude of zipcode entered $zipQuery = " SELECT city, state, latitude, longitude FROM zipcodes WHERE zipcode = '" . $_POST['zipcode'] . "'";
$zipResult = mysql_query ($zipQuery) or trigger_error("SQL ERROR ....", ERROR); $zipEntered = mysql_fetch_assoc($zipResult);
DO THE MATH to find all entries in the database where (in my case user's zipcode where they live) is within N miles of the zipcode user performing search has entered.
$SQL= "Select username, age, blah, blah, blah FROM a bunch of tables WHERE a bunch of joins and SUBSTRING(user_addresses.zip,1,5) = zipcodes.zipcode and sqrt(power(69.1*(zipcodes.latitude - " . $zipEntered['latitude'] . "),2) + power(69.1*(zipcodes.longitude-" . $zipEntered['longitude'] . ") * cos(zipcodes.latitude/57.3),2)) < " . $_POST['within'] ";
There may be a better way to do this, but I do recall scouring the web when setting this up two years back and coming to find this method suggested over and over again.
- Matt "another shamless plug below" Muro ___________________________________________________ Share your interests and passions in any activity imaginable http://www.lifeknot.com
-------------------------------------------------- Hi All,
Does anyone know how to do a zip code distance search in a database? For instance, a customer enters their zip code, and you return the closest stores within a 20 mile radius. I assume that some kind of zip code table has to be procured as well. Does anyone know where to get this?
Thanks, Stefan
________________________________________________________ Matt Muro [EMAIL PROTECTED] Harvard University phone: 617.998.8522 Division of Continuing Education fax: 617.495.9176
________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
=====================================================
Database WebWorks: Dynamic web sites through database integration
http://www.DatabaseWebWorks.com
________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
