';
print 'This script may only be executed on the Command Line!';
print '
';
exit(2);
}
// set_time_limit($runtime) does not have any effect on the command line!
$time_start = time();
/**
* Retrieve geoip data for ip.
*
* @param ip
* A string containing an ip address.
* @return
* array geoip data array.
*/
function visitors_get_geoip_data($ip) {
$result = array(
'continent_code' => '',
'country_code' => '',
'country_code3' => '',
'country_name' => '',
'region' => '',
'city' => '',
'postal_code' => '',
'latitude' => '0',
'longitude' => '0',
'dma_code' => '0',
'area_code' => '0'
);
if (function_exists('geoip_record_by_name')) {
$data = @geoip_record_by_name($ip);
if ((!is_null($data)) && ($data !== FALSE)) {
$result = $data;
}
}
return $result;
}
try {
$dbh = new PDO($driver . ':host=' . $server . ';dbname=' . $db, $user, $password);
print 'Connection to ' . $dbh->getAttribute(PDO::ATTR_DRIVER_NAME) . ' server ' . $dbh->getAttribute(PDO::ATTR_SERVER_VERSION) .
' established (' . $dbh->getAttribute(PDO::ATTR_CONNECTION_STATUS) . ').' . "\n";
} catch (Exception $e) {
die('Unable to connect to database "' . $db . '": ' . $e->getMessage());
}
print 'Updating GeoIP data in "' . $table . '" table of database "' . $db . '"';
foreach ($dbh->query(
'SELECT count(visitors_ip) FROM ' . $table
) as $row) {
$rows = $row[0];
print ' (containing ' . $rows . ' rows).' . "\n";
}
# Get all unique IP addresses.
foreach ($dbh->query(
'SELECT count(DISTINCT visitors_ip) FROM ' . $table . ' WHERE visitors_continent_code = \'\''
) as $row) {
$to_be_processed = $row[0];
print $to_be_processed . ' unique IP addresses have to be processed.' . "\n";
}
$sql = 'SELECT DISTINCT visitors_ip FROM ' . $table . ' WHERE visitors_continent_code = \'\'';
$sth = $dbh->prepare($sql);
$sth->execute();
$update_sql = 'UPDATE ' . $table . '
SET
visitors_continent_code=:continent_code,
visitors_country_code=:country_code,
visitors_country_code3=:country_code3,
visitors_country_name=:country_name,
visitors_region=:region,
visitors_city=:city,
visitors_postal_code=:postal_code,
visitors_latitude=:latitude,
visitors_longitude=:longitude,
visitors_dma_code=:dma_code,
visitors_area_code=:area_code
WHERE visitors_ip = :ip';
$updated = 0;
$skipped = 0;
$empty = 0;
while ($record = $sth->fetch(PDO::FETCH_ASSOC)) {
$ip = long2ip($record['visitors_ip']);
// Skip local IP addresses:
if (preg_match('/^(0\.|127\.|192\.168\.|10\.)/', $ip)) {
$skipped++;
continue;
}
$data_ip = visitors_get_geoip_data($ip);
$update_sth = $dbh->prepare($update_sql);
# Update records.
$ret = $update_sth->execute(
array(
':continent_code' => $data_ip['continent_code'],
':country_code' => $data_ip['country_code'],
':country_code3' => $data_ip['country_code3'],
':country_name' => $data_ip['country_name'],
':region' => $data_ip['region'],
':city' => $data_ip['city'],
':postal_code' => $data_ip['postal_code'],
':latitude' => $data_ip['latitude'],
':longitude' => $data_ip['longitude'],
':dma_code' => $data_ip['dma_code'],
':area_code' => $data_ip['area_code'],
':ip' => $record['visitors_ip'],
)
);
if (! $ret) {
$errorInfo = $update_sth->errorInfo();
print 'SQL Error: ' . $errorInfo[2];
exit(1);
}
if (! $data_ip['continent_code']) {
$empty++;
} else {
$updated++;
print $updated . ': Updated IP ' . $ip . ' (' . $record['visitors_ip'] . '): continent_code = ' . $data_ip['continent_code'] . "\n";
}
if (time() >= $time_start + $max_runtime) {
print 'Script stopped because maximum allowed runtime of ' . $max_runtime . ' seconds reached.' . "\n";
break;
}
}
$runtime = time() - $time_start;
if ($skipped) {
print $skipped . ' IP addresses skipped.' . "\n";
}
if ($empty) {
print $empty . ' IP addresses could not be resolved into GeoIP data.' . "\n";
}
if ($updated) {
$time_per_ip = round($runtime/$updated, 3);
print $updated . ' IP addresses successfully updated in ' . $runtime . ' seconds (' . $time_per_ip . ' s/IP).' . "\n";
} else {
print 'Nothing updated! Runtime: ' . $runtime . ' seconds.' . "\n";
}
$todo = $to_be_processed - $skipped - $empty - $updated;
if ($todo) {
print 'There are still ' . $todo . ' IP addresses that have not yet been checked';
if ($updated) {
print ' (Expected required time: ';
$todo_time = $todo * $time_per_ip;
if ($todo_time > 3600) {
print round($todo_time/3600, 2) . ' h';
} elseif ($todo_time > 60) {
print round($todo_time/60, 1) . ' min';
} else {
print $todo_time . ' s';
}
print ')';
}
print '.';
}
print "\n\n";
exit(0);