MySQL Query to Use MaxMind GeoLite IP Database

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