使用从while循环检索的数据更新数据库

Question

I Have a table named FRANCE like this

  City          Region            LAT   LNG
  PARIS         L'Ile-de-France  
  MARSEILLE     Provenza

Now, LAT and LNG values I retrieve throught a function that use google api. To do this I must concatenate City and Region

So this is what I do:

$sql="Select * from France";
$result=mysql_query($sql) or die(mysql_error());
while($row=mysql_fetch_array($result)){
$city=$row['city'];
$region=$row['region'];

 $address=$city.",".$region.", France";

$coordinates = file_get_contents('http://maps.googleapis.com/maps/api/geocode/json?address=' .      urlencode($address) . '&sensor=true');
$coordinates = json_decode($coordinates);

$lat = $coordinates->results[0]->geometry->location->lat;
$lng = $coordinates->results[0]->geometry->location->lng;
 }

Now I'd like to update the table FRANCE to have this output

  City          Region            LAT         LNG
  PARIS         L'Ile-de-France   48.856614   2.352222
  MARSEILLE     Provenza          43.296482   5.369780

How can I do?

Answer 1

It's quite straightforward, just like the comments above, you already got the fetching of results and made the request. After gathering the response from the request just make that UPDATE statement.

Obligatory note:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Here is a rough untested example using mysqli with prepared statements. Of course you need to modify those items:

// first step connect and make the first query
$db = new mysqli('localhost', 'username', 'password', 'database');
$sql = 'SELECT * FROM france';
$query = $db->query($sql);

while($row = $query->fetch_assoc()) {
    // fetch and assign results
    $id = $row['id'];
    $city = $row['city'];
    $region = $row['region'];
    $address = $city.",".$region.", France";

    // make the google request and gather results
    $coordinates = file_get_contents('http://maps.googleapis.com/maps/api/geocode/json?address=' . urlencode($address) . '&sensor=true');
    $coordinates = json_decode($coordinates);

    // check if there are results
    if($coordinates != null) {
        $lat = $coordinates->results[0]->geometry->location->lat;
        $lng = $coordinates->results[0]->geometry->location->lng;

        // make the update
        $sql2 = 'UPDATE france SET `LAT` = ?, `LNG` = ? WHERE id = ?';
        $update = $db->prepare($sql2);
        // i don't know if this is double column or varchar
        $update->bind_param('ddi', $lat, $lng, $id);
        $update->execute();
    }
}