CODEWITHSUNDEEP
phpmysqlgoogle-mapslatitude-longitude
ditisrichard
ditisrichard

Reputation: 3

Update google maps lat/lng in database

I got the following piece of code, but it doesn't seem to get the right values for lat and long. It gives me 0,000000 in return. When I check an adress, it gives me the right values in the json.

mysql_select_db($dbname);

$result = mysql_query("SELECT id, street, postalcode, place, lat, lng FROM stores") or die(mysql_error());  

while($row = mysql_fetch_array( $result )){

$addressbar = $row['street']." ".$row['postalcode']." ".$row['place'];

$address = urlencode($addressbar);

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

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

mysql_query("UPDATE stores SET lat='$lat', lng='$lng' WHERE id=".$row['id']."") or die(mysql_error());
printf( $addressbar."%d\n", mysql_affected_rows());
echo "<br/><br/>";
}

I can't seem to find what I'm doing wrong. I hope anyone can help me out.

Upvotes: 0

Views: 979

Answers (2)

LSerni
LSerni

Reputation: 57398

The Google querying code and the JSON decoding are correct; I tried them myself and, if the address checks (the one I tried did), it correctly issues lat and lng.

Try printing the query and inspecting executing it manually from client.

The only possibilities that came to me are:

  • you don't have UPDATE privileges on the table (you should have gotten an error)
  • maybe "stores" is a non-updatable view (should throw error 1288)
  • the value of "$lat" and/or "$lng" is NOT a number, but a string, or even an empty string. This will give you no error, and a value of 0.00000.

Other possibilities which did not come to me:

  • Google isn't answering properly due to firewall, network problems, or OVER_QUERY_LIMIT.
  • Problems in UTF encoding of address

I also vote for Allen Holman's advice.

Upvotes: 1

Allen Holman
Allen Holman

Reputation: 125

A few things! To debug, I would do the following:

  1. Ensure that use strict; & use warnings; is at the top of your code (you probably did this, but it doesn't show i nthe snippet).

  2. I would print out $json, if you are getting 0's back from the map api, you may be able to see an error msg back in the $json text. If you are getting lat/lng in $json, then add more debugging to your mysql query or print out the query before it goes to the exec function. For example to add more debugging:

    DBI->connect( $dsn, $username, $password, { 'PrintError' => 1 } )

  3. If it is not in $json, then I would manually go to the map api URL to see what I get back

  4. I'm guessing that you removed it from the URL above(i see no &key=AAABB.....), but double check your maps API key is correct

  5. I'm sure you trust the data back from google, but please get in the habit of using placeholders when doing any query. Placeholders (?) instead of putting the variables directly in the SQL code protect you from SQL injection. Ex:

    $mysqlquery = $dbh->prepare( 'UPDATE stores SET lat=?, lng=? WHERE id=?' );
$mysqlquery->execute( $lat, $lng, $row['id'] );

  6. Sometimes it helps me when I am debugging to print out what I am about to send to an API. Instead of sending the URL or query directly to the function, put it in a variable, print that variable out, then send the variable to the function/api. You may be assuming that it is correct, but some small issue is preventing it from working that is obvious if you see the string.

Hope this all helps a bit, good luck!

Upvotes: 2

Related Questions