zip code proximity

[slimjim]

I have a database that is like this :

CREATE TABLE `zip_code` (
  `zip_code` int(10) unsigned NOT NULL default '0',
  `lattitude` float NOT NULL default '0',
  `longitude` float NOT NULL default '0',
  `city` varchar(25) NOT NULL default '',
  `state_prefix` char(2) NOT NULL default '',
  PRIMARY KEY  (`zip_code`)
) TYPE=MyISAM;

Then of course I have a user table table that has
id|user|pass|city|state|zipcode

How can I search the user table first to get the users zipcode and then with that info, search the zip_code table to get all zipcodes within proximity of 50 miles within that users zipcode.

Then take all of those zipcodes and query the users table and show all users that have those zipcodes.

Please someone help me, I have looked and searched all over for some good examples and nothing.

I need somone to help me out with a good example on starting this...

[Kudose]

http://www.dbazine.com/oracle/or-articles/weeg9

http://tutorial153.easycfm.com/ <- This is CouldFusion, but the equation is all the same.

http://www.webmonkey.com/templates/print_template.htmlt?meta=/webmonkey/05/32/index4a_meta.html

Try searching "zip code equation" rather than being all specific about the language. You will get lots of junk web boards post with advertisements if you get too specific on Google nowadays ... although I suppose that does defeat the purpose of a seach engine ... but that's another topic.

[slimjim]

I have looked and searched that way, without even comming close to understanding it all
Cant anyone actually do the code for me so that I may learn from it, I know my tables and what I am looking for, the other peoples code I can figure it out based on what I am wanting. And trust me I have looked at and practiced just about all of it.
Tired of searching now after 3 days of it...

[slimjim]

Please read the comments as I have been at this for a long long time and tried many different things, this is the closest I have been...
If you can help on any part of this that would be great, instead of sending me on search's

	  $query="select user, zipcode from login where user='1'";
	  $result = mysql_query($query) or die( "ERROR: " . mysql_error() . "\n");	    

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

		$zipcode=$myrow["zipcode"];
}
//I cant get the results above to flow down to next query unless I uncomment the below		
//$radius="5";
//$zipcode="30338";
	  $query="select zip_code, lattitude, longitude, city, state_prefix from zip_code where zip_code='$zipcode'";
	  $result = mysql_query($query) or die( "ERROR: " . mysql_error() . "\n");	    

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

		$zip=$myrow["zip_code"];
		$lat=$myrow["lattitude"];
		$lng=$myrow["longitude"];
		$city=$myrow["city"];
		$state=$myrow["state_prefix"];	
	}

echo "$zip<BR>";	
echo "$lat<BR>";	
echo "$lng<BR>";	
echo "$city<BR>";	
echo "$state<BR>";	
//now if I uncomment the radius and zipcode above
//then the below query does show the zip codes that are within the radious
        $query = "SELECT zip_code, lattitude, longitude,
        (3958*3.1415926*sqrt((lattitude-$lat)*(lattitude-$lat) + cos(lattitude/57.29578)*cos($lat/57.29578)*(longitude-$lng)*(longitude-$lng))/180) AS zipdistance
        FROM zip_code HAVING zipdistance <= $radius
        ORDER BY zipdistance ASC;";
        $result = mysql_query($query) or die( "ERROR: " . mysql_error() . "\n");	    

 	      while ($myrow = mysql_fetch_array($result)) {	
	 	    $zipp=$myrow["zip_code"];
echo $zipp; 	   	 	   

      }
//other problem is all of the $zipp when I echo them show up as
//30338303503035830346303603007630328
//so I need to figure out how to sperate them..
//which I know I would do a foreach as bla bla bla
//but then it causes a problem if I were to add pagnation..

// then once they are seperated, query the database profile for all profiles that have those zipp
	  $query="select * from profile where zipcode='$zipp'";
	  $result = mysql_query($query) or die( "ERROR: " . mysql_error() . "\n");	    

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

		$mzip_code=$row2["zipcode"];
		$user=$row2["user"];

//then put results here
echo $user;
echo $mzip;
	}
//I also need to add pagination to this.....	

[slimjim]

Is there a way to add both bottom querys into one so that displaying the results would be easier and so that some form of pagination will work ?

[Houdini]

Search these forums for pagination and you should get plenty of ideas.

[slindstr]

I'm trying to do essentially the same thing as you, slimjim and I stumbled across this on experts-exchange:

You'll want to use spherical geometry using arccos like so:

3959acos ( (sin(startZip.latitude/57.3) sin(endZip.latitude/57.3)) + (cos(startZip.latitude/57.3) cos(endZip.latitude/57.3) cos((startZip.longitude - endZip.longitude)/57.3)))

Second, SQL supports this, so just do it right in the statement:

SELECT TOP 25
3959acos
(
(sin(startZip.latitude/57.3) sin(endZip.latitude/57.3))
+ (cos(startZip.latitude/57.3) cos(endZip.latitude/57.3) cos((startZip.longitude - endZip.longitude)/57.3))
) AS exactDistance
FROM
ZipCodes startZip
INNER JOIN
Dealers d
ON d.DealerZip = startZip.ZipCode,
ZipCodes endZip
WHERE endZip.zipcode = #endZipCode#

I found that at this link:
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20418667.html

I would definately read the post though since corrections are made here and there.

Hope that helps, and if you come up with an easier solution can you let me know?

Thanks
Steve

[slimjim]

Thank you, I will look at that cause its an issue I cant figure out just yet. I will let you know if I figure it out.

[extrovertive]

I made it work!

Go here for the class
http://www.zend.com/codex.php?id=1486&single=1

and go here for the zip database
http://www.cfdynamics.com/cfdynamics/zipbase/index.cfm

[Kudose]

Nice!