phpziplocator - need from nearest to farthest

I need to be able to display the dealers from nearest to farthest.

[danielm28]

I am building a dealer locator. When a user inputs a zip code, they are presented with all dealers within a X mile radius. I am using a class that has several functions. One of them takes the arguments radius and user input zip. The class does a lookup into a storelocator table that contains the longitude and latitude of each zip in the radius. The result is a list of zips within the X mile radius.
I match this list with my list of dealers. I then get the info of the dealers where dealer.zip = storelocator.zip.

I need to be able to display the dealers from nearest to farthest. What do I need to modify in the code?

Here's the class i'm using:

class zipLocator
{
function distance($zipOne,$zipTwo)
{
global $db;
$query = "SELECT * FROM storelocator WHERE zipcode = $zipOne";

$db->query($query);
if(!$db->nf()) {
return "First Zip Code not found";
}else{
$db->next_record();
$lat1 = $db->f("lat");
$lon1 = $db->f("lon");
}

$query = "SELECT * FROM storelocator WHERE zipcode = $zipTwo";

$db->query($query);
if(!$db->nf()) {
return "Second Zip Code not found";
}else{
$db->next_record();
$lat2 = $db->f("lat");
$lon2 = $db->f("lon");
}

/* Convert all the degrees to radians */
$lat1 = $this->deg_to_rad($lat1);
$lon1 = $this->deg_to_rad($lon1);
$lat2 = $this->deg_to_rad($lat2);
$lon2 = $this->deg_to_rad($lon2);

/* Find the deltas */
$delta_lat = $lat2 - $lat1;
$delta_lon = $lon2 - $lon1;

/* Find the Great Circle distance */
$temp = pow(sin($delta_lat/2.0),2) + cos($lat1) * cos($lat2) * pow(sin($delta_lon/2.0),2);

$EARTH_RADIUS = 3956;
$distance = $EARTH_RADIUS * 2 * atan2(sqrt($temp),sqrt(1-$temp));

return $distance;

} // end func

function deg_to_rad($deg)
{
$radians = 0.0;
$radians = $deg * M_PI/180.0;
return($radians);
}

function inradius($zip,$radius)
{
global $db;
$query="SELECT * FROM storelocator WHERE zipcode='$zip'";
$db->query($query);

if($db->affected_rows()<>0) {
$db->next_record();
$lat=$db->f("lat");
$lon=$db->f("lon");
$query="SELECT zipcode FROM storelocator WHERE (POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius) ";
$db->query($query);
if($db->affected_rows()<>0) {
while($db->next_record()) {
$zipArray[$i]=$db->f("zipcode");
$i++;
}
}
}else{
return "Zip Code not found";
}
return $zipArray;
} // end func

} // end class

AND HERE'S THE CODE FOR THE DEALER RESULTS PAGE. IT GIVES ME EVERYTHING I NEED EXCEPT FOR A LISTING OF DEALERS FROM NEAREST TO FARTHEST. I'M ONLY GETTING A LIST OF DEALERS BACK BASED ON THE ZIP ORDER THAT IS RETURNED FROM THE CLASS:

$zipLoc = new zipLocator;
$radius = 100;
$zipArray = $zipLoc->inradius($userinputzip,$radius);

foreach($zipArray as $index => $zip)
$zipArray[$index]="'".$zip."'";
$zipstring=implode(',', $zipArray);

$querydistance = "SELECT zip FROM dealer where zip in ($zipstring)";
$resultdistance = mysql_query($querydistance);

while($rowdistance = mysql_fetch_array($resultdistance))
{
$distance = $zipLoc->distance($search_value,$rowdistance['zip']);
echo "The distance between $userinputzip and " . $rowdistance['zip'] . " is $distance Miles<br>";
}

[hakre]

hi!

i would prefer another way of querieing this directly from the db. for this each zip has to be put on a x/y grid, then you can calculate the distance without any gaps and you will need only one(!) query on your data instead of n (or even unlimited!).

[danielm28]

hakre,

would that make it easier to sort by distance? or just increase processing time? how would i go about doing that?

[hakre]

this will really make it simplier. in a 2d environment, you can simple calculate the distance. 2d only means every point (in this case zip-code) has 2 coordinates (often named x and y). because it can be calculated, even mysql can do this on the fly, so it's possible to have one field named distance in the returned data by mysql. that's no problem at all. sorting can be done by an order by clause.

for x and y of each zip you can maybe use geometry data available like the degrees on which that zip-region is in. i don't know zip specifics, but i think that should be solve-able.

[danielm28]

hmm, could you give me an example of how I could have a on the fly distance field in the returned data set that i could be able to sort by?

Thanks

[hakre]

this is all phytagoras based. if you're not that familiar with geometry, checkout wikipedia. you'll get a quick jump in there. let's say, each row in table "zips" has got 4 fields: id, zip, x and y. furthermore, we're located on sx and sy. a query could be:

SELECT id,zip,?-?-? AS distance FROM zips ORDER BY distance

please ignore the?-?-? now. this would be such a query. distance does not exists in the table zips but is created by the query. order by is used on it.

the?-?-? stand for the formula to calculate the distance between each rows x/y and sx/sy (the point where to measure the distance to). the mathematical formula to measure the distance (a²+b²=c² is used therefore in my example) needs to be translated to be used for mysql. this is done theoretically in the following example, i have not tested this one, don't blame me please ;):

SQRT( POW( IF(ABS(x-sx)<ABS(sx-x),ABS(x-sx),ABS(sx-x)),2 ) + POW( IF(ABS(y-sy)<ABS(sy-y),ABS(y-sy),ABS(sy-y)),2 ) )

this would be the?, each sx and sy have to be replaced with the values of the point to measure from.

[danielm28]

i'm having difficulty returning the distance for each zipOne,zipTwo pair.

here's my sql statement:

"select $zipLoc->distance($zipOne,$zipTwo) as distance from storelocator order by distance";

even when i hardcode zipone and ziptwo i still get no results.
the function definition is included in an earlier post.

i get the following error message:

Warning: Supplied argument is not a valid MySQL result resource in /nfs/cust/4/8/2/rounds/new/dealersearch/search_results.php on line 228

What am i doing wrong?

here's the code that appears on line 228:
while($rowdistance = mysql_fetch_array($resultdistance))
{
echo " <td class='data'>***" . $rowdistance['distance'] . "***</td>\n";
}