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; } }