PHP mysqli_fetch_array selecting smallest values

Question

Hey guys and thanks for your help. What I'm trying to do is use the latitude and longitude from a user, and compare it to the latitude and longitude I have saved in my DB with associated names of cities. The code I have now, works, but the problem is there could be more than one row within a 30 mi radius. Take a look at my code, help me if you can.

<?php function distance($lat1, $lon1, $lat2, $lon2) {

    $pi80 = M_PI / 180;
    $lat1 *= $pi80;
    $lon1 *= $pi80;
    $lat2 *= $pi80;
    $lon2 *= $pi80;

    $r = 6372.797; // mean radius of Earth in km
    $dlat = $lat2 - $lat1;
    $dlon = $lon2 - $lon1;
    $a = sin($dlat / 2) * sin($dlat / 2) + cos($lat1) * cos($lat2) * sin($dlon / 2) * sin($dlon / 2);
    $c = 2 * atan2(sqrt($a), sqrt(1 - $a));
    $km = $r * $c;

    //echo '<br/>'.$km;
    $km = $km * 0.621371;
    return $km;
 }
    include_once("dbconnect.php");
    /* check connection */
    if ($mysqli->connect_errno) {
        printf("Connect failed: %s\n", $mysqli->connect_error);
        exit();
    }
    $grabCities = "SELECT * FROM cities";
    $result = $mysqli->query($grabCities);
    while($row = mysqli_fetch_array($result)){ 
    $distance = distance(41.08866717,-81.4780426,$row['lat'],$row['longitude']);
    if ($distance < 30){
        echo $myCity = $row['City'] . "<BR>";
    }

    }
    $mysqli->close();
?>

What I'm trying to do is from all the results I have in my DB; convert the latitude and longitude. Find the city with the shortest distance to the user, and select that city. @Augwa — Jimmy Canadezo, Apr 11 '17 at 02:34
You can do this in SQL, and just order the result with a limit 1. — Jonathan, Apr 11 '17 at 02:38
No, I'm getting the right calculation based on distance; I need to organize my results to select the one out of 416 that has the smallest number, and the name of that City. which is saved in my DB. @Augwa — Jimmy Canadezo, Apr 11 '17 at 02:41
I can't do it with SQL because I'll only be grabbing one row, and there's 416 rows; I don't want to compare one. I want to compare them all and find which city is closest. @Augwa — Jimmy Canadezo, Apr 11 '17 at 02:42

score 2 · Accepted Answer · answered Apr 11 '17 at 02:47

You can do this using MYSQL, 1 result returned which is the one your want.

sprintf('
    SELECT
        *,
        (((acos(sin((%1$d*pi()/180)) * 
            sin((`lat`*pi()/180))+cos((%1$d*pi()/180)) * 
            cos((`lat`*pi()/180))*cos(((%2$d-`longitude`) * 
            pi()/180))))*180/pi())
        ) as distance
        FROM `cities` 
    ORDER BY
        distance ASC
    LIMIT 1
    ',
    $latitude,
    $longitude
);

score 0 · Answer 2 · answered Apr 11 '17 at 02:25

0

Something like this? Untested.

$smallestDistance = null;

while($row = mysqli_fetch_array($result)){ 
    $distance = distance(41.08866717,-81.4780426,$row['lat'],$row['longitude']);
    if ($distance < $smallestDistance){
        $myCity = $row['City'];
    }
    $smallestDistance = $distance;
}

echo $myCity;

answered Apr 11 '17 at 02:25

GeorgeQ

1,382
10
8

I was hoping it was gonna work, but it's bringing me to random cities; the city I should be expecting to get is Akron/Canton Ohio. That's the coordinates I have inputted by default. – Jimmy Canadezo Apr 11 '17 at 02:35

PHP mysqli_fetch_array selecting smallest values

2 Answers2