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.
|
|