Thursday, August 13, 2009


A while ago I found this great article on how to import the free GeoIP database into MySQL. It provides a really simple way to look up IPs and see what country they are from using a MySQL database. It is also fairly optimized for size (the GeoIP .csv is 7.9MB and the MySQL tables are 1.9MB). Vincent (the author of the article) also provides some PHP snippets that look up IPs, just to get the feel of it: <?php function getALLfromIP($addr,$db) { // this sprintf() wrapper is needed, because the PHP long is signed by default $ipnum = sprintf("%u", ip2long($addr)); $query = "SELECT cc, cn FROM ip NATURAL JOIN cc WHERE ${ipnum} BETWEEN start AND end"; $result = mysql_query($query, $db); if((! $result) or mysql_numrows($result) < 1) { //exit("mysql_query returned nothing: ".(mysql_error()?mysql_error():$query)); return false; } return mysql_fetch_array($result); } function getCCfromIP($addr,$db) { $data = getALLfromIP($addr,$db); if($data) return $data['cc']; return false; } function getCOUNTRYfromIP($addr,$db) { $data = getALLfromIP($addr,$db); if($data) return $data['cn']; return false; } function getCCfromNAME($name,$db) { $addr = gethostbyname($name); return getCCfromIP($addr,$db); } function getCOUNTRYfromNAME($name,$db) { $addr = gethostbyname($name); return getCOUNTRYfromIP($addr,$db); } ?> If anyone needs this, I have exported the cc and ip tables from the 01-May-09 version of the GeoIP database (it's the latest one at this point in time). Download: geoip.01-May-2009.sql.gz [773.5 KB] Also, here's a little demo application that looks up IPs and/or hostnames: (which might be offline at times)

No comments:

Post a Comment