PHP MySQL GeoIP Lookup


The latest GeoIPCountryWhois.csv is available from http://www.maxmind.com/app/geolitecountry

Parse GeoIP CSV to MySQL
<?php
$lookup_arr = array('ip_start'=>0,'ip_end'=>1,'ip32_start'=>2,'ip32_end'=>3,'country_code'=>4,'country_full'=>5);
$fd = fopen("GeoIPCountryWhois.csv", "r");
while (($data = fgetcsv($fd, 1024, ",")) !== FALSE)
{
    $arr=array();
    $arr['ip_start'    ] = $data[$lookup_arr['ip_start'    ]];
    $arr['ip_end'      ] = $data[$lookup_arr['ip_end'      ]];
    $arr['ip32_start'  ] = $data[$lookup_arr['ip32_start'  ]];
    $arr['ip32_end'    ] = $data[$lookup_arr['ip32_end'    ]];
    $arr['country_code'] = $data[$lookup_arr['country_code']];
    $arr['country_full'] = $data[$lookup_arr['country_full']];
 
    foreach($arr as $k=>$v)
        $arr[$k] = mysql_escape_string($v);
 
    $query='';
    $query.="insert into `geoip`(`".implode("`,`", array_keys($arr))."`) ";
    $query.="values('".implode("','", array_values($arr))."');";
    echo $query."\n";
}
fclose($fd);
?>

GeoIP MySQL Lookup
<?php
 
function ipaddress_to_uint32($ip)
{
    list($v4,$v3,$v2,$v1) = explode(".", $ip);
    return ($v4*256 *256*256) + ($v3*256*256) + ($v2*256) + ($v1);
}
 
$ip='34.23.13.43';
$i = ipaddress_to_uint32($ip);
 
$db = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') or die(mysql_error());
mysql_select_db('my_db', $db)  or die(mysql_error());
 
$query   = "select * from `geoip` where ip32_start<= $i and $i <=ip32_end;";
$result  = mysql_query($query);
if ($row = mysql_fetch_assoc($result))
{
    echo $row['country'];
}
?>
code snippets are licensed under Creative Commons CC-By-SA 3.0 (unless otherwise specified)

Larbi on 2014-12-09 14:24:42
thank you for your code, it is very instructive.