IP to Country
Since I’m currently working with advertising related stuff it might be a good thing to learn the whole geographic location by ip number thing. A good starting point is The IP-to-Country Handbook.
After downloading the CSV from that site it’s time to import:
First we need a MySQL table for all this:
CREATE TABLE `blog`.`country_ip` (
`ip_from` DOUBLE NOT NULL ,
`ip_to` DOUBLE NOT NULL ,
`cc2` VARCHAR( 2 ) NOT NULL ,
`cc3` VARCHAR( 3 ) NOT NULL ,
`c_name` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAMAnd then import:
class ip_db_config{
static $db_name = "mydb";
static $db_password = "";
static $db_user = "root";
static function db_connect(){
$db_link = mysql_connect("localhost", self::$db_user, self::$db_password);
mysql_select_db(self::$db_name, $db_link);
}
}
function import_country_ips(){
$file_arr = file("ip-to-country.csv");
foreach($file_arr as $line){
$clean_line = trim( str_replace('"', "'", str_replace("'", "\'", $line) ) );
$sql = "INSERT INTO country_ip (ip_from,ip_to,cc2,cc3,c_name) VALUES($clean_line)";
echo $sql."<br>";
mysql_query($sql);
}
echo "Finished importing";
}
ip_db_config::db_connect();
import_country_ips();Note the double str_replace(), the first one is needed because of countries like Cote D’ivoire. The second one because a typical row in that CSV file looks like this:
"33996344","33996351","GB","GBR","UNITED KINGDOM"It’s time to test:
function assoc_query_1D($sql){
$result = mysql_query($sql);
if(!$result)
return 0;
else
return mysql_fetch_assoc($result);
}
function check_country($country, $dot_ip){
$ip = ip2long($dot_ip);
$sql = "SELECT * FROM `country_ip` WHERE ip_from <= $ip and ip_to >= $ip";
$country_data = assoc_query_1D($sql);
$strlen = strlen($country);
if($strlen == 2)
$test_with = $country_data['cc2'];
else if($strlen == 3)
$test_with = $country_data['cc3'];
else
$test_with = $country_data['c_name'];
if(strtolower($country) == strtolower($test_with))
return true;
else
return false;
}
ip_db_config::db_connect();
if(check_country('THA', $_SERVER['REMOTE_ADDR']))
echo "Sawadee Kup!";