Dynamic relation between GOOGLE Maps API v3 and MySQL

Question

I am designing my very own first web page. It's a real estate page where some users will add open house information while others can pull the info. The code for the database and the web is very good (after two months...) and inserting and getting data is sweet... Now the Google maps. I am trying to have something like trulia or even the very original housingmaps.com (It seems that they started the mash up, right?). crimereports.com is very nice, too.

GOAL: pull addresses from database, get long and lat from geocode, insert it back to table, display in google map. Plus: as people pan the map, new info pops into map.

Here is the code for retrieving addresses, geo code, and add lat and lng back to database.

<?php
//require("phpsqlajax_dbinfo.php");

// Opens a connection to a MySQL server
$username = "abc"; //personal info changed to abc
$password = "abc";
$hostname = "abc"; 
$database = "abc";
$connection = mysqli_connect($hostname, $username, $password);
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

//if (!$connection) {
  //die("Not connected : " . mysql_error());
//}

define("MAPS_HOST", "maps.google.com");
define("KEY", "my .............key........code");



// Set the active MySQL database
$db_selected = mysqli_select_db($connection, $database);
if (!$db_selected) {
  die("Can\'t use db : " . mysql_error());
}

// Select all the rows in the markers table
$query = "SELECT * FROM brokerstour.property WHERE 1";
$result = mysqli_query($connection, $query);
if (!$result) {
  die("Invalid query: " . mysql_error());
}

// Initialize delay in geocode speed
$delay = 0;
$base_url = "http://maps.googleapis.com/maps/api/geocode/output?parameters";

// Iterate through the rows, geocoding each address
while ($row = @mysqli_fetch_assoc($result)) {
  $geocode_pending = true;

  while ($geocode_pending) {
    $address = $row["address"];
    $id = $row["id"];
    $request_url = $base_url . "&q=" . urlencode($address);
    $xml = simplexml_load_file($request_url) or die("url not loading");

    $status = $xml->Response->Status->code;
    if (strcmp($status, "200") == 0) {
      // Successful geocode
      $geocode_pending = false;
      $coordinates = $xml->Response->Placemark->Point->coordinates;
      $coordinatesSplit = split(",", $coordinates);
      // Format: Longitude, Latitude, Altitude
      $lat = $coordinatesSplit[1];
      $lng = $coordinatesSplit[0];

      $query = sprintf("UPDATE property " .
             " SET lat = '%s', lng = '%s' " .
             " WHERE id = '%s' LIMIT 1;",
             mysql_real_escape_string($lat),
             mysql_real_escape_string($lng),
             mysql_real_escape_string($id));
      $update_result = mysql_query($query);
      if (!$update_result) {
        die("Invalid query: " . mysql_error());
      }
    } else if (strcmp($status, "620") == 0) {
      // sent geocodes too fast
      $delay += 100000;
    } else {
      // failure to geocode
      $geocode_pending = false;
      echo "Address " . $address . " failed to geocoded. ";
      echo "Received status " . $status . "
\n";
    }
    usleep($delay);
  }
}
?>

Here is what I have changed to the Google Tutorial (original code): 1) updated php: mysqli to avoid errors. Not getting more php errors. 2) changed the url from "http://" . MAPS_HOST . "/maps/geo?output=xml" . "&key=" . KEY; TO "http://maps.googleapis.com/maps/api/geocode/output?parameters" trying to update code to API v3.

If I use old url, i get error 610. With new url I get:

---

( ! ) SCREAM: Error suppression ignored for ( ! ) Warning: simplexml_load_file(): I/O warning : failed to load external entity "maps.googleapis.com/maps/api/geocode/output?parameters&q=4200+Park+Blvd+" in C:.... on line 57 .....

url not loading

Full disclosure: I am very new and maybe the above doesn't even make sense. However I have been stuck on this problem for a week. (By the way for those new to google maps, the book Beginning Google API 3 is a must. It does not deal with database though, but explains the google maps api very well).

Could someone please give any a hint, book, tutorial? Any help is a great help. Thank you for your time.

Line 57 is $xml = simplexml_load_file($request_url) or die("url not loading");

Answer 1

This is a partial success. This code accomplishes: a) get addresses from table; b) get lat and lnt from google; c) insert data back into table.

I still need to work on the delay. Besides, I am a 3 month old programmer... and to get the above working took me two long weeks... I left the delay in there just for reference (it was tutorial from Google with changes form other people in the web.)

I tried to vote up the answer from Mr. Strachan, but I lack reputation inside stack overflow... Could not do it. That fix also works. Thank you for your time.

<?php

$conn = mysql_connect('localhost', 'rxxt', 'xxxxxxx');
if(!$conn)
 {
   echo "Database Error.";
 }
 else
 {
   echo "Connected.";
   mysql_select_db('YOUR TABLE', $conn);
   $query = "SELECT * from YOUR TABLE";
   $result = mysql_query($query, $conn) or die($query.mysql_error());
    while($row = mysql_fetch_array($result))
    { 

      $address = $row['address'];
      $id = $row['id'];

      $loc_address = str_replace(" ", "+", $address); //to replace spaces by '+' signs
          $location = $loc_address;
      echo $location."<br>";
      $delay = 0;  // this delay is here just to avoid errors - 
          $url = "http://maps.google.com/maps/api/geocode/json?address=".$location."&sensor=false";
          echo $url;
            $output=file_get_contents($url);
            $out= json_decode($output);
            $lat = $out->results[0]->geometry->location->lat;
            $lng = $out->results[0]->geometry->location->lng;
            echo '<br>Lat is '. $lat;
            echo '<br>Long is '. $lng;


       $query = sprintf("UPDATE YOUR TABLE " .
             " SET lat = '%s', lng = '%s' " .
             " WHERE id = '%s' LIMIT 1;",
             mysql_real_escape_string($lat),
             mysql_real_escape_string($lng),
             mysql_real_escape_string($id));
           $update_result = mysql_query($query);

      echo "Successfully inserted.";
    // Below is just for reference - they say we need the delay. The may problem: does not get the status to compare to 200, 620, etc.   
    /*$status = $xml->Response->Status->code;  
    if (strcmp($status, "200") == 0) {
            // Successful geocode
            echo '<br />successfull geocode</strong><br />';
            $geocode_pending = false;
    }
    else if (strcmp($status, "620") == 0) {
         // sent geocodes too fast
         $delay += 100000;
            } else {
         // failure to geocode
         $geocode_pending = false;
         echo "Address " . $address . " failed to geocoded. ";
         echo "Received status " . $status . "
        \n";
          }*/
    usleep($delay); 
    }
 }
mysql_close($conn);
?>

Answer 2

The $base_url = "http://maps.googleapis.com/maps/api/geocode/output?parameters"; You do not specify output.It should be either xml or jason. Also the parameters you pass are address and either sensor = false or sensor = true

EDIT

Remove

$geocode_pending = true;
while ($geocode_pending) {

END EDIT

$q = $row["address"];
$base_url = "http://maps.googleapis.com/maps/api/geocode/xml?address=";
$request_url = $base_url.urlencode($q)."&sensor=false";
$xml = simplexml_load_file($request_url) or die("url not loading");
if($xml->status=="OK"){
    // Successful geocode
    $lat = $xml->result->geometry->location->lat;
    $lng = $xml->result->geometry->location->lng; 

EDIT

    echo $lat.","$lng."<br>";//
    } 
    }

Remove rest of code and make sure this works before incementally adding rest of code