At random points, sometimes 2 or 3 records, sometimes 50+ records I get a error number 2006, "MySQL server has gone away" error.
Is there a setting in php I need to do?
Code: Select all
<?php
require("phpsqlajax_dbinfo.php");
// 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());
}
mysql_query("SET NAMES 'utf8'");
// Select all the rows in the markers table
$query = "SELECT * FROM mymarkers WHERE updated = 0 LIMIT 2500";
$rows = mysql_query($query);
if (!$rows) {
die("Invalid query: " . mysql_error());
}
// Google maps api key http://code.google.com/apis/maps/signup.html
$key = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
$base_url = "http://maps.googleapis.com/maps/api/geocode/json?key=" . $key . "&sensor=false";
// Initialize delay in geocode speed
$delay = (86400000000/2500); // microseconds in a day divided by maximum requests per day (2500)
// Iterate through the rows, geocoding each address
while ($row = @mysql_fetch_assoc($rows))
{
$mycount = 0;
$finished = false;
$itemid = $row["itemid"];
$region = strtolower(substr($row["field_countrycode"],-2));
// Pick out the address fields
$fields = array();
$mycount = 0;
foreach( $row as $field )
{
if (($mycount>=2) && ($mycount<=7) && ($field!='') && ($field!=null))
{
$fields[] = html_entity_decode(unicodetohtml($field)); // convert \u to chars and &xxx; to the character
}
$mycount++;
}
$fields[] = $row['countryname'];
$mycount = 0;
$maxfield = count($fields)-1;
while (!$finished && $mycount<=$maxfield)
{
$address = "";
// Keep trying a reduced version of the address, ie. full address first, then city+state, then state etc.
for ($field=$mycount; $field<=$maxfield; $field++)
{
if ( ($field==0) || ($fields[$field]!=$fields[$field-1]) )
{
$address .= $fields[$field] . " ";
}
}
// What are we searching for
echo " Search : " . $address ." region : " . $region . " Search count : " . $mycount . <br/>";
$mycount++;
// Get the json
$request_url = $base_url . "&address=" . urlencode($address) . "®ion=" . urlencode($region);
if ($address!='')
{
// Wait for it...
usleep($delay);
$json_contents = file_get_contents($request_url,0,null,null);
$json = json_decode($json_contents);
echo "Search status : " . $json->status . "<br/>";
switch ($json->status)
{
case "ZERO_RESULTS":
// try again
$finished=false;
break;
case "OVER_QUERY_LIMIT":
// try again
$finished=false;
$delay += 100000;
$mycount--;
break;
case "REQUEST_DENIED":
// try again
$finished=true;
break;
case "INVALID_REQUEST":
// try again
$finished=true;
break;
case "OK":
$finished=true;
break;
}
}
}
$location = $json->results[0]->formatted_address;
$geo_lat = $json->results[0]->geometry->location->lat;
$geo_long = $json->results[0]->geometry->location->lng;
$accuracy = $json->results[0]->geometry->location_type; // Accuracy
// update the marker
$query = sprintf( "UPDATE mymarkers SET location='%s', geo_lat='%s', geo_long='%s', accuracy='%s', updated=1 where itemid=%s",
addslashes($location),
addslashes($geo_lat),
addslashes($geo_long),
addslashes($accuracy),
$itemid );
$result = mysql_query($query);
if (!$result) {
die("Invalid query: " . mysql_error());
}
echo "Found : " . $location . " lat : " . $geo_lat . " lng : " . $geo_long . " accuracy : " . $accuracy . '<br/>';
}
function unicodetohtml($str)
{
return (preg_replace("/\\\\u([0-9a-f]{3,4})/i", "&#x\\1;", $str));
}
?>