MySQL server has gone away (Err no 2006) and geocoding

Bit of a techy post - I have a database of addresses with latitude and longitude. I had geocoded these a few years ago using Google maps api. (Geocoding means take an address and get the latitude and longitude)

The problem was that the old geocoding wasn't biased towards the country I was searching for. So for example, if I looked up Boston, it could take me to Boston in the US rather than the UK.

The latest version of the Google maps api has a biased feature - so I can say search for Boston in the UK first. Brill!

So I've written some php code to geocode my addresses. Then had a strange error message "error 2006 - MySQL server has gone away". It took me 2 days to find an answer... Trouble is there are lots of reasons for it.

I was using the mysql functions in php, so thought I would try the more advanced mysqli functions. Still the same error. Then came across the mysqli->ping() function. This will report if the connection has been lost, so I can reconnect - a very simple solution and no more silly MySQL server has gone away messages

<?php

$key = "xxxxxxxxxxxxxxxx";
$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)

echo "Reconnect : " . ini_get('mysqli.reconnect') . '<br/>';
if (ini_set('mysqli.reconnect', '1'))
{
    echo 'Reconnection set<br/>';
}
else
{
    echo 'reconnection not set <br/>';
}

require("phpsqlajax_dbinfo.php");

$mysqli = mysqli_init();
if (!$mysqli) {
    die('mysqli_init failed');
}

if (!$mysqli->options(MYSQLI_INIT_COMMAND, "SET NAMES 'utf8'")) {
    die('Setting MYSQLI_INIT_COMMAND failed');
}

// MYSQL_OPT_RECONNECT
// Google delay is 34 seconds
if (!$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 60)) {
    die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
}

if (!$mysqli->real_connect('localhost', $username, $password, $database)) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}


//$mysqli = new mysqli('localhost', $username, $password, $database);
//if (mysqli_connect_errno()) {
//    die("Invalid query: " .mysqli_connect_errno() . ' : ' .mysqli_connect_error());
//}


$mysqli->query("SET NAMES 'utf8'");

// Select all the rows in the markers table
$query = "SELECT * FROM mymarkers WHERE updated = 0 ORDER BY countryname LIMIT 1000;"; // AND SUBSTRING(field_countrycode,-2)='TN'
if (!$rows = $mysqli->query($query))
{
      die("Invalid query: " .$mysqli->errno . ' : ' .$mysqli->error);
}

// Iterate through the rows, geocoding each address
while ($row = $rows->fetch_assoc())
{
    $mycount = 0;
    $finished = false;

    $itemid = $row["itemid"];
    $region = strtolower(substr($row["field_countrycode"],-2));
   
    $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'];
   
   
    // print_r($row);
    /// convert ampersands and \u?
   
    $mycount = 0;   
    $maxfield = count($fields)-1;   
    while (!$finished && $mycount<=$maxfield)
    {
        $address = "";
       
        for ($field=$mycount; $field<=$maxfield; $field++)
        {
            if ( ($field==0) || ($fields[$field]!=$fields[$field-1]) )
            {
                $address .= $fields[$field] . " ";
            }
        }
       

        $mycount++;
        echo "Search : " . $address ." region : " . $region . " Search attempt : " . $mycount . "<br/>";
       
       
        $request_url = $base_url . "&address=" . urlencode($address) . "&region=" . 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;
            }
        }

    }
    // echo $json_contents;
    // echo $json;

    $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

    // Select all the rows in the markers table
    $query = sprintf( "UPDATE mymarkers SET location='%s', geo_lat='%s', geo_long='%s', accuracy='%s', updated=1 where itemid=%s limit 1",
                addslashes($location),
                addslashes($geo_lat),
                addslashes($geo_long),
                addslashes($accuracy),
                $itemid );
    echo $query . '<br/>';
   
    if (!$mysqli->ping())
    {
        echo ("Ping Error: " . $mysqli->errorno . " : " . $mysqli->error . '<br/>');
        if (!$mysqli->real_connect('localhost', $username, $password, $database)) {
            die('Connect Error (' . mysqli_connect_errno() . ') '
                    . mysqli_connect_error());
        }       
        else
        {
            echo 'Reconnected successfully!!<br/>';
        }
    }   

    if (! $mysqli->query($query) ) {
        die("Invalid query: " .$mysqli->errno . ' : ' .$mysqli->error);
    }   

    echo "Found : " . $location . " lat : " . $geo_lat . " lng : " . $geo_long . " accuracy : " . $accuracy . '<br/><br/>';
   
}
$rows->close();

mysqli_close($mysqli);


    function unicodetohtml($str)
    {
        return (preg_replace("/\\\\u([0-9a-f]{3,4})/i", "&#x\\1;", $str));
    }


?>