StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAdapt code to my mySQL tables
text
Body
copied!<p>I have a table, "jos_ezrealty" that has address, city, state fields and declat & declong for latitude and longitude. I need to adapt the code below (provided by google - <a href="http://code.google.com/apis/maps/articles/phpsqlgeocode.html" rel="nofollow noreferrer">http://code.google.com/apis/maps/articles/phpsqlgeocode.html</a>) to work with my table. All of my address fields are filled, and half of my lat & long fields are filled. I want to use the code below to fill in the missing lat & long information (don't worry about geocoding request limits, as I only have 300 properties and this code only fills in the missing ones)</p> <p>Thanks - </p> <pre><code> <?php require("phpsqlgeocode_dbinfo.php"); define("MAPS_HOST", "maps.google.com"); define("KEY", "abcdefg"); // Opens a connection to a MySQL server $connection = mysql_connect("localhost", $username, $password); if (!$connection) { die("Not connected : " . mysql_error()); } // Set the active MySQL database $db_selected = mysql_select_db($database, $connection); if (!$db_selected) { die("Can\'t use db : " . mysql_error()); } // Select all the rows in the markers table $query = "SELECT * FROM markers WHERE 1"; $result = mysql_query($query); if (!$result) { die("Invalid query: " . mysql_error()); } // Initialize delay in geocode speed $delay = 0; $base_url = "http://" . MAPS_HOST . "/maps/geo?output=xml" . "&key=" . KEY; // Iterate through the rows, geocoding each address while ($row = @mysql_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 markers " . " 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); } } ?> </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload