IP国家对照表在PHP MySQL中的应用

piaoling  2012-05-03 11:57:30

IP国家对照表在PHP+MySQL中的应用

最近项目中要用到IP国家对照表,于是google到一个由webhosting推出的免费的IP国家数据库:The IP to Country Database,更新速度也很快,使用起来也很方便。最新版:点击下载
在PHP使用之前,首先要把csv导入到MySQL中,用以下语句创建MySQL表,用来存放数据:

CREATE TABLE IF NOT EXISTS `ipcountry` (
  `ip_from` int(10) unsigned NOT NULL default '0',
  `ip_to` int(10) unsigned NOT NULL default '0',
  `country_code2` char(2) NOT NULL,
  `country_code3` char(3) NOT NULL,
  `country_name` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='IP国家对照表';

然后用以下语句导入csv文件数据,注意csv文件路径和表名,这里以junnan_org数据库为例:

Windows环境的MySQL导入语句

LOAD DATA INFILE "D:\ip-to-country.csv" INTO TABLE junnan_org.ipcountry FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'rn';

Linux环境的MySQL导入语句

LOAD DATA INFILE "/var/www/ip-to-country.csv" INTO TABLE junnan_org.ipcountry FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n';

PHP中的查询方法就更方便了:

$conn=mysql_connect("localhost","USER","PASSWORD");
mysql_select_db('junnan_org');
$result = mysql_query("SELECT country_name FROM ipcountry WHERE ip_from< =INET_ATON('$ip') AND ip_to>=INET_ATON('$ip') LIMIT 0,1");
......

 

类别 :  默认(739)  |  浏览(5343)  |  评论(0)
发表评论(评论将通过邮件发给作者):

Email: