PHP MySql Store locator by lat/lon coordinates

[tagtekin]

I have the following code I have spent almost a week to get it to work it worked for while with a inaccurate distances now i dotn get anything at all here is my script
Help would be great before I go crazy

Thanks
<?php
//designed to calculate closest range 50 miles of offices for HIR
//two db tables and one database connections requred
$getzip = $_GET['getzip'];

$dbh=mysql_connect ("localhost", "root", "") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("phpziplocator");

$query1 = "Select * from zip_code where zip_code= '" . $getzip . "'";
$zip1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_assoc($zip1);


$query2 = "Select * from offices";
$zip2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_assoc($zip2);

//$result = mysql_query($query2,$dbh);
//while($row3=mysql_fetch_array ($result)) {
//echo ($row3['office_name']) ,"<BR>";
//}

do {
if (distance($row1['lon'], $row1['lat'], $row2['lon'], $row2['lat']) <= 5) {

echo $row2['city'] . " --- " . $row2['state'] . " --- " . $row2['zip'] . " --- <b>" . distance($row1['lon'], $row1['lat'], $row2['lon'], $row2['lat']) . "</b> --- " . $row2['lat'] . " --- " . $row2['lon'] . "<br>";



}

} while ($row2 = mysql_fetch_assoc($zip2));

//echo distance($row1['lat'], $row1['lon'], $row2['lat'], $row2['lon'], "M") . " Miles<br>";

mysql_free_result($zip1);
mysql_free_result($zip2);

function distance($lat1, $lon1, $lat2, $lon2)
{
$lat1 = floatval($lat1);
$lon1 = floatval($lon1);
$lat2 = floatval($lat2);
$lon2 = floatval($lon2);

// Formula for calculating distances
// from latitude and longitude.
$dist = acos(sin(deg2rad($lat1))
* sin(deg2rad($lat2))
+ cos(deg2rad($lat1))
* cos(deg2rad($lat2))
* cos(deg2rad($lon1 - $lon2)));


$dist = rad2deg($dist);
$miles = (float) $dist * 69.1;

// To get kilometers, multiply miles by 1.61
$km = (float) $miles * 1.61;

// This is all displaying functionality
$display = sprintf("%0.2f",$miles);

//$display = sprintf("%0.2f",$miles).' miles' ;
//$display .= ' ('.sprintf("%0.2f",$km).' kilometers)' ;

return $display ;
}
?>

[falko]

Originally Posted by tagtekin

$query2 = "Select * from offices";
$zip2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_assoc($zip2);

//$result = mysql_query($query2,$dbh); 
//while($row3=mysql_fetch_array ($result)) {
//echo ($row3['office_name']) ,"<BR>"; 
//}

do {
     if (distance($row1['lon'], $row1['lat'], $row2['lon'], $row2['lat']) <= 5) {   
	   
echo $row2['city'] . " --- " . $row2['state'] . " --- " . $row2['zip'] . " --- <b>" . distance($row1['lon'], $row1['lat'], $row2['lon'], $row2['lat']) . "</b> --- " . $row2['lat'] . " --- " . $row2['lon'] . "<br>";     
     
	
	
	 }
	
  } while ($row2 = mysql_fetch_assoc($zip2));

I think the problem is that you do
$row2 = mysql_fetch_assoc($zip2);
twice...

[Ben]

@Falko: Really?

Because the loop runs always at least one time because the condition is at the bottom...

if it would be a
while( $row...)
it would be twice.

How about some var_dumps after fetching the data, or fater the call of distance, to manually check if there are any logical problems you did not see yet?

[tagtekin]

ok here is the code i made some changes and also here is the link whati t gives me. I get some return results where

http://70.46.28.11/zip/tester.php?getzip=34744

zip code 34744 at least 400 miles from the miami location
and 34744 should be 0 miles from the 34744 location

thanks guys
<?php
//designed to calculate closest range 50 miles of offices for HIR
//two db tables and one database connections requred
$getzip = $_GET['getzip'];

$dbh=mysql_connect ("localhost", "root", "") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("phpziplocator");

$query1 = "Select * from zipdata where zipcode = '" . $getzip . "'";
$zip1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_assoc($zip1);


$query2 = "Select * from offices";
$zip2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_assoc($zip2);


do {
if (distance($row1['lon'], $row1['lat'], $row2['lon'], $row2['lat']) <= 100) {
echo $row2['city'] . " --- " . $row2['state'] . " --- " . $row2['zip'] . " --- <b>" . distance($row1['lon'], $row1['lat'], $row2['lon'], $row2['lat']) . "</b> --- " . $row2['lat'] . " --- " . $row2['lon'] . "<br>";



}
} while ($row2 = mysql_fetch_assoc($zip2));

//echo distance($row1['lat'], $row1['lon'], $row2['lat'], $row2['lon'], "M") . " Miles<br>";

mysql_free_result($zip1);
mysql_free_result($zip2);

function distance($lat1, $lon1, $lat2, $lon2)
{
$lat1 = floatval($lat1);
$lon1 = floatval($lon1);
$lat2 = floatval($lat2);
$lon2 = floatval($lon2);

// Formula for calculating distances
// from latitude and longitude.
$dist = acos(sin(deg2rad($lat1))
* sin(deg2rad($lat2))
+ cos(deg2rad($lat1))
* cos(deg2rad($lat2))
* cos(deg2rad($lon1 - $lon2)));

$dist = rad2deg($dist);
$miles = (float) $dist * 69;

// To get kilometers, multiply miles by 1.61
$km = (float) $miles * 1.61;

// This is all displaying functionality
$display = sprintf("%0.1f",$miles);

//$display = sprintf("%0.2f",$miles).' miles' ;
//$display .= ' ('.sprintf("%0.2f",$km).' kilometers)' ;

return $display ;
}
?>

[Ben]

What happens if you do the calculation for distance() manually with a calculator? So just echo the values you read from the db, an then
calculate or do what you except your script should do next.

If you finished correctly see, whats the difference in you manual process and in your code is.

I'm sorry that this answer is more theoretical but on the other hand the problem is "too complex" to just understand what this all shall do with any data from the db, and how to fix the "problem"...

[tagtekin]

Yes even i did that the same zip codes should give me zero value. since both 34744 and 34744 located in the same coordinates. I am using the formula which i got from

$distance = sin(deg2rad($lat_A))
* sin(deg2rad($lat_B))
+ cos(deg2rad($lat_A))
* cos(deg2rad($lat_B))
* cos(deg2rad($long_A - $long_B));

$distance = (rad2deg(acos($distance))) * 69.09;

i also found similar link
http://www.dmxzone.com/forum/topic.asp?topic_id=14260
but still no good
thanks

[tagtekin]

I got it i use different formula and i get very close milage results and here is my code anybody is more than welcome to use it and letm e know if there are questions about hte db and so on.

<?php
//designed to calculate closest range 50 miles of offices for HIR
//two db tables and one database connections required
$getzip = $_GET['getzip'];

$dbh=mysql_connect ("localhost", "root", "") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("phpziplocator");

$query1 = "Select * from zipdata where zipcode = '" . $getzip . "'";
$zip1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_assoc($zip1);


$query2 = "Select * from offices";
$zip2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_assoc($zip2);



$istartlat=81.3139;
$istartlong=28.66146;
$iRadius = 150;
$latrange = $iradius / ((6076 / 5280) * 60);
$LongRange = $iRadius / (((cos($iStartLat * 3.141592653589 / 180) * 6076.) / 5280.) * 60);

$LowLatitude = istartlat - $LatRange;
$HighLatitude = istartlat + $LatRange;
$LowLongitude = istartlong - $LongRange;
$HighLongitude = istartlong + $LongRange;

echo $HighLongitude;
echo("<br>");
echo $LowLongitude;
echo("<br>");
echo $HighLatitude;
echo("<br>");
echo $LowLatitude;
echo("<br>");

do {
if (distance($row1['lon'], $row1['lat'], $row2['lon'], $row2['lat']) <= 10000) {
echo $row2['city'] . " --- " .$row2['office_name'] . " --- " . $row2['state'] . " --- " . $row2['zip'] . " --- <b>" . distance($row1['lon'], $row1['lat'], $row2['lon'], $row2['lat']) . "</b> --- " . $row2['lat'] . " --- " . $row2['lon'] . "<br>";



}
} while ($row2 = mysql_fetch_assoc($zip2));

//echo distance($row1['lat'], $row1['lon'], $row2['lat'], $row2['lon'], "M") . " Miles<br>";

mysql_free_result($zip1);
mysql_free_result($zip2);

function distance($lat1, $lon1, $lat2, $lon2)
{
$lat1 = floatval($lat1);
$lon1 = floatval($lon1);
$lat2 = floatval($lat2);
$lon2 = floatval($lon2);

// Formula for calculating distances
// from latitude and longitude.

$dist = 3959*acos(sin(deg2rad($lat1/57.3))* sin(deg2rad($lat2)/57.3)+ cos(deg2rad($lat1)/57.3)* cos(deg2rad($lat2)/57.3)* cos(deg2rad($lon1 - $lon2)/57.3));

//$dist = rad2deg($dist);
$miles = (float) $dist * 69;

// To get kilometers, multiply miles by 1.61
$km = (float) $miles * 1.61;

// This is all displaying functionality
$display = sprintf("%0.1f",$miles);

//$display = sprintf("%0.2f",$miles).' miles' ;
//$display .= ' ('.sprintf("%0.2f",$km).' kilometers)' ;

return $display ;
}
?>

[jokerjoe]

Could you please post the database structure for both databases. Thank you in advance.