0

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?

Improve this question
2
  • 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. Commented Nov 11, 2014 at 12:23
  • thanks ghost could you show me an example please?sorry for my bad english Commented Nov 11, 2014 at 12:24

1 Answer 1

Reset to default
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();
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

fatantastic...thanks so much...I don't know the part You call //make update..thanks a lot!
@cicciopasticcio basically that code block makes the update on that row. glad this helped

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.