按距离排序邮政编码

Question

So, I'm working on a search form for my site. I've run into a road block. Seems others have too but I've been unable to find an answer for my question, at least one that I understand.

I found a PHP function online that grabs up all the zip codes within a specified radius. I edited the sql query so that I wasn't grabbing every zip code within that radius, but ones that are associated with a user account. The function works and grabs the zip codes I'm after. This function returns an array with the zip codes. I would like to sort the zip codes by distance from nearest to farthest, and apparently I'm no where near smart enough to figure out how to do it.

What I've Tried:

I found another PHP function that will calculate the distance between 2 sets of coordinates(lat,lon2, lat2, lon2). I created my own function that loops through my zip codes array and runs each zip through the calculateDistance function. Just seems to be a big mess. I believe I have all the pieces just not sure how to put it all together. Any help would be greatly appreciated.

Here is the zipcodeRadius function I'm using:

    // get all the zipcodes within the specified radius - default 20
function zipcodeRadius($lat, $lon, $radius)
{
$radius = $radius ? $radius : 20;
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
        $sql = 'SELECT DISTINCT zipcodes.ZIP,
                     members.*,
                     location.*
                     FROM members
                     LEFT JOIN location
                     ON members.id = location.user_id
                     LEFT JOIN zipcodes
                     ON location.zip = zipcodes.ZIP
                     WHERE activated="1"
                     AND (3958*3.1415926*sqrt((Latitude-'.$lat.')*(Latitude-'.$lat.') + cos(Latitude/57.29578)*cos('.$lat.'/57.29578)*(Longitude-'.$lon.')*(Longitude-'.$lon.'))/180) <= '.$radius.';';
                     $result = mysqli_query($dbc, $sql);
// get each result
$zipcodeList = array();
while($row = mysqli_fetch_array($result))
{
    array_push($zipcodeList, $row['ZIP']);
}
return $zipcodeList;
}

Here is the calculateDistance function

    function calculateDistance($latitude1, $longitude1, $latitude2, $longitude2) {
$theta = $longitude1 - $longitude2;
$miles = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) + (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
$miles = acos($miles);
$miles = rad2deg($miles);
$miles = $miles * 60 * 1.1515;
return $miles;
}   

And, Here's my seemingly useless function:

    $matches = join(',',$zipCodeArray);
function sort_zip_by_distance($matches, $lat, $lon){
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$queryDistance = "SELECT * FROM zipcodes WHERE  ZIP IN ($matches)";
$resultDistance = mysqli_query($dbc, $queryDistance)
or die("Unable to query the database");
while($rowDistance = mysqli_fetch_array($resultDistance)){
    $miles = calculateDistance($lat, $lon, $rowDistance['Latitude'],        $rowDistance['Longitude']);
    echo '<p>ZIP: ' . $rowDistance['ZIP'] . ' | Lat: ' . $rowDistance['Latitude'] . ' | Lon: ' . $rowDistance['Longitude'] . ' | Miles: ' . $miles . '</p><br />';

    $distanceTotal = array();
    array_push($distanceTotal, $rowDistance['ZIP'], $miles);
}
return $distanceTotal;
}

Answer 1

You can do arbitrary math in order by clauses, but you'd most likely to actually RETURN the distances found, so why not do

SELECT *, long_ugly_distance_calc_here AS distance
...
ORDER BY distance DESC