Storing data into an array and then accessing it one by one in a while loop

Question

---

Background

---

• I have a table in a database made using MySql which includes several columns. Two of these are "name" and "address".

• I have a function written in PHP which takes in two addresses and returns the distance between them using Google maps API. This function works.

---

Objective

---

• I want to take the two columns in the table "name" and "address" and store them in a variable on PHP. I then want to compare the distance of each row in the column "address" to an address that is hardcoded in the script and find out the one with the shortest distance. Once I find out the address with the shortest distance, I echo the "name" it is associated with.

---

Problem

---

My issue is my lack of understanding of how to implement the search and storage of the data . So far I have this to try to access each address and calculate the distance:

$sql = "SELECT name, address FROM users";
$result = $conn->query($sql);
$distance = []; // an array so we can store our distance result in here
if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    $distance[] = [
  'name' => $row['name'], // user id so you can use it later
  'distance' => getDistance($row['address'],$addressTo)
] // get the distance from api and save it on an array
  }
}

// now somehow have to get the min distance and name associated with it from that array

1. This does not even compile for some reason, gives a parse error at the end of the while loop

Parse error: syntax error, unexpected '}'

2. The way I am approaching this isnt very effective (at least with my understanding of arrays in other languages) due to my limited understanding of how arrays work in PHP. Is there a function or a way where I could effectively extract the address and the associated name that comes with it to perform this? I would appreciate some help with this.

---

Distance function

---

For reference

function getDistance($addressFrom, $addressTo, $unit = ''){
    // Google API key
    $apiKey = ' The API key';
    
    // Change address format
    $formattedAddrFrom    = str_replace(' ', '+', $addressFrom);
    $formattedAddrTo     = str_replace(' ', '+', $addressTo);
    
    // Geocoding API request with start address
    $geocodeFrom = file_get_contents('https://maps.googleapis.com/maps/api/geocode/json?address='.$formattedAddrFrom.'&sensor=false&key='.$apiKey);
    $outputFrom = json_decode($geocodeFrom);
    if(!empty($outputFrom->error_message)){
        return $outputFrom->error_message;
    }
    
    // Geocoding API request with end address
    $geocodeTo = file_get_contents('https://maps.googleapis.com/maps/api/geocode/json?address='.$formattedAddrTo.'&sensor=false&key='.$apiKey);
    $outputTo = json_decode($geocodeTo);
    if(!empty($outputTo->error_message)){
        return $outputTo->error_message;
    }
    
    // Get latitude and longitude from the geodata
    $latitudeFrom    = $outputFrom->results[0]->geometry->location->lat;
    $longitudeFrom    = $outputFrom->results[0]->geometry->location->lng;
    $latitudeTo        = $outputTo->results[0]->geometry->location->lat;
    $longitudeTo    = $outputTo->results[0]->geometry->location->lng;
    
    // Calculate distance between latitude and longitude
    $theta    = $longitudeFrom - $longitudeTo;
    $dist    = sin(deg2rad($latitudeFrom)) * sin(deg2rad($latitudeTo)) +  cos(deg2rad($latitudeFrom)) * cos(deg2rad($latitudeTo)) * cos(deg2rad($theta));
    $dist    = acos($dist);
    $dist    = rad2deg($dist);
    $miles    = $dist * 60 * 1.1515;
    
    // Convert unit and return distance
    $unit = strtoupper($unit);
    if($unit == "K"){
        return round($miles * 1.609344, 2).' km';
    }elseif($unit == "M"){
        return round($miles * 1609.344, 2).' meters';
    }else{
        return round($miles, 2).' miles';
    }
}

Answer 1

Iterating over every entry in your database and making an API call in loop is going to be pretty slow. Also, I notice that your distance calculation is being performed from the lat and lng, and isn't being directly returned by the API.

You will have a lot better performance (especially if you are planning on having any serious amount of data), to store the lat and lng for each address in the database (meaning you would only have to make the call once for each new address), and then perform the calculation without the API call.

Even better would be to just rewrite the query so that it performs the calculation and returns the top result. Take a look at this db fiddle, with some random cities in it.

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=56014857919d57a9465938982caced7f

I have output two versions of the query, one where it shows all results sorted by distance, and one where it limits to to the top result, just so you could see all of the cities ranked.

I have not checked the distance formula. I tried to do a straight transcription from your code, and I did not verify that it was accurate, I just got it working in mySQL, and the sorting of the cities seems right. I didn't convert into miles or anything else, so you might want to check my formula there, but I assume you know more about the correct formula than I do. I'm just supplying some SQL here.

Handle the unit conversion in php after you've calculated the results.

Depending on how much data you expect to have, doing an unlimited self join like this could slow down. There are ways to handle for this, but since I don't know exactly what your data will look like I think that's a topic for another time.

Also, there is a POINT data type in mySQL which is probably better suited for this type of work, and can take a spatial index, which would probably make any queries against the data more performant, and if there will be a lot of data it may be worth looking into. I just have personally never used it, so I just went with the lat and lng stored separately in DECIMALs.

https://dev.mysql.com/doc/refman/5.7/en/gis-data-formats.html