Update a DB using data retrived from a while loop

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?

after gathering the results, then just create an update statement, then after thats processed, then make another select. and try to use mysqli or PDO with prepared statements instead. — Kevin, Nov 11 '14 at 12:23
thanks ghost could you show me an example please?sorry for my bad english — , Nov 11 '14 at 12:24

score 1 · Accepted Answer · edited May 23 '17 at 11:52

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();
    }
}

fatantastic...thanks so much...I don't know the part You call //make update..thanks a lot! — , Nov 11 '14 at 12:44
@cicciopasticcio basically that code block makes the update on that row. glad this helped — Kevin, Nov 11 '14 at 12:53

Update a DB using data retrived from a while loop

1 Answers1