MaxMind offer a fantastic resource to geolocate IP addresses. Unfortunately the PHP API supplied is overkill for most (simple) uses. Instead, we can do it mostly within PHP/MySQL.
Create Schema:
DROP TABLE IF EXISTS `blocks`; CREATE TABLE `blocks` ( `startIPNum` int(10) unsigned NOT NULL, `endIPNum` int(10) unsigned NOT NULL, `locID` int(10) unsigned NOT NULL, PRIMARY KEY (`startIPNum`,`endIPNum`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 DELAY_KEY_WRITE=1;
DROP TABLE IF EXISTS `location`; CREATE TABLE `location` ( `locID` int(10) unsigned NOT NULL, `country` char(2) default NULL, `region` char(2) default NULL, `city` varchar(45) default NULL, `postalCode` char(7) default NULL, `latitude` double default NULL, `longitude` double default NULL, `dmaCode` char(3) default NULL, `areaCode` char(3) default NULL, PRIMARY KEY (`locID`), KEY `Index_Country` (`country`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;
Read in the MaxMind data:
load data infile 'GeoLiteCity-Blocks.csv' into table `blocks` fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' ignore 2 lines;
load data infile 'GeoLiteCity-Location.csv' into table `location` fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' ignore 2 lines;
And then we can use raw SQL to query against it – extremely quickly (0.0001 sec) by using the following query:
SELECT *
FROM `location`
WHERE locid =
(
SELECT locid
FROM `blocks`
INNER JOIN
(
SELECT Max(startipnum) AS start
FROM `blocks`
WHERE startipnum <= inet_aton('1.2.3.4'))
AS
s ON
(
startipnum = s.start
)
WHERE endipnum >= inet_aton('1.2.3.4'));
You can similarly use this almost exact query within PHP using the following function (assuming PDO….):
function IPdata($ip) {
global $dbh;
if(!isset($dbh)) die("No database connection available.");
if(filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4 | FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE)) {
$cip = ip2long($ip);
if($cip) {
try {
$stmt = $dbh->prepare("SELECT * FROM `ip_geolocation` WHERE locID = (SELECT locID FROM `ip_blocks` INNER JOIN (SELECT MAX(startIPNum) AS start FROM `ip_blocks` WHERE startIPNum <= :IPADDR1) AS s ON (startIPNum = s.start) WHERE endIPNum >= :IPADDR2)");
$stmt->bindParam(':IPADDR1', $cip);
$stmt->bindParam(':IPADDR2', $cip);
$stmt->execute();
} catch (PDOException $ex) {
die("IPdata() SQL ERROR: " . $ex->getMessage());
return false;
}
if($stmt->rowCount() == 1) {
$row = $stmt->fetch();
return $row;
} else {
die("IPdata() ERROR: NO DATA RETURNED FOR {$ip}");
return false;
}
} else {
die("IPdata() ERROR: ip2long RETURNED FALSE FOR {$ip}:{$cip}");
return false;
}
} else {
die("IPdata() ERROR: IP address does not validate ({$ip}:{$cip})");
return false;
}
}
