在上一篇文章中使用了檔案查詢的方式從訪客 IP 找出其國名, 其中存在兩個問題, 一是資料似乎有點舊, 有些 IP 找不到所屬國家 (特別是香港); 其二是檔案處理要使用迴圈, 查詢速度似乎較慢.
我找到 ip2nation 這個網站, 不但可以線上查詢 IP 所屬國家, 還慷慨地提供資料庫讓我們下載 (點左方導覽列的 download), 方便整合到自己的應用服務之中. 還可以在底下的框框輸入 email, 當資料庫有更新時會通知我們下載 :
# http://www.ip2nation.com/
解壓縮所下載的 ip2nation.zip 會得到一個 ip2nation.sql 資料庫檔, 裡面建立了 ip2nation 與 ip2nationCountries 這兩個資料表, 前者儲存 IP 的上限與國碼簡碼, 如下所示 :
DROP TABLE IF EXISTS ip2nation;
CREATE TABLE ip2nation (
ip int(11) unsigned NOT NULL default '0',
country char(2) NOT NULL default '',
KEY ip (ip)
);
DROP TABLE IF EXISTS countries;
CREATE TABLE countries (
code varchar(4) NOT NULL default '',
iso_code_2 varchar(2) NOT NULL default '',
iso_code_3 varchar(3) default '',
iso_country varchar(255) NOT NULL default '',
country varchar(255) NOT NULL default '',
country_zhtw varchar(255) NOT NULL default '',
lat float NOT NULL default '0',
lon float NOT NULL default '0',
PRIMARY KEY (code),
KEY code (code)
);
INSERT INTO ip2nation (ip, country) VALUES(0, 'us');
INSERT INTO ip2nation (ip, country) VALUES(687865856, 'za');
INSERT INTO ip2nation (ip, country) VALUES(689963008, 'eg');
INSERT INTO ip2nation (ip, country) VALUES(691011584, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691617792, 'zw');
INSERT INTO ip2nation (ip, country) VALUES(691621888, 'lr');
INSERT INTO ip2nation (ip, country) VALUES(691625984, 'ke');
INSERT INTO ip2nation (ip, country) VALUES(691630080, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691631104, 'gh');
INSERT INTO ip2nation (ip, country) VALUES(691632128, 'ng');
INSERT INTO ip2nation (ip, country) VALUES(691633152, 'zw');
INSERT INTO ip2nation (ip, country) VALUES(691634176, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691650560, 'gh');
INSERT INTO ip2nation (ip, country) VALUES(691666944, 'ng');
INSERT INTO ip2nation (ip, country) VALUES(691732480, 'tz');
INSERT INTO ip2nation (ip, country) VALUES(691798016, 'zm');
INSERT INTO ip2nation (ip, country) VALUES(691863552, 'za');
INSERT INTO ip2nation (ip, country) VALUES(691994624, 'zm');
INSERT INTO ip2nation (ip, country) VALUES(692011008, 'za');
.....
後者儲存國碼簡碼, 英文全名, 經緯度等資訊 :
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw, lat, lon) VALUES('ad', 'AD', 'AND', 'Andorra', 'Andorra', '', 42.3, 1.3);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw, lat, lon) VALUES('ae', 'AE', 'ARE', 'United Arab Emirates', 'United Arab Emirates', 24, 54);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw, lat, lon) VALUES('af', 'AF', 'AFG', 'Afghanistan', 'Afghanistan', 33, 65);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw, lat, lon) VALUES('ag', 'AG', 'ATG', 'Antigua and Barbuda', 'Antigua and Barbuda', 17.03, -61.48);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw, lat, lon) VALUES('ai', 'AI', 'AIA', 'Anguilla', 'Anguilla', 18.15, -63.1);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw, lat, lon) VALUES('al', 'AL', 'ALB', 'Albania', 'Albania', 41, 20);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw, lat, lon) VALUES('am', 'AM', 'ARM', 'Armenia', 'Armenia', 40, 45);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw, lat, lon) VALUES('an', 'AN', 'ANT', 'Netherlands Antilles', 'Netherlands Antilles', 12.15, -68.45);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw, lat, lon) VALUES('ao', 'AO', 'AGO', 'Angola', 'Angola', -12.3, 18.3);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw, lat, lon) VALUES('aq', 'AQ', 'ATA', 'Antarctica', 'Antarctica', -90, 0);
INSERT INTO countries (code, iso_code_2, iso_code_3, iso_country, country, country_zhtw, lat, lon) VALUES('ar', 'AR', 'ARG', 'Argentina', 'Argentina', -34, -64);
.....
在 phpmyadmin 的輸入上傳這個 ip2nation.sql 就會在系統產生 ip2nation 與 ip2nationCountries 這兩個資料表 :
但是這樣只能查詢英文國名, 為了要查中文國名, 我另外準備了一個 nation 資料表, 它只有 code 與 name 兩個欄位, code 就是上面 ip2nationCountries 資料表的國名簡碼, 而 name 是其繁體中文國名, 此表的可由下列連結下載 :
# 下載國名簡碼與中文國名對照表 nation.sql
# 下載國名簡碼與中文國名對照表 nation.txt
然後參考 ip2nation.com 網站在 Sample scripts 所提供的範例程式碼, 修改 sys.php 中的 visitors 與 list_visitors 這兩個模組, 在 visitors 模組中我將中英國名分在兩欄呈現 :
$('#sys_visitors').datagrid({
columns:[[
{field:'id',title:'id',sortable:true},
{field:'visit_time',title:'到訪時間',sortable:true},
{field:'remote_addr',title:'遠端位址',sortable:true},
{field:'remote_port',title:'遠端埠號',sortable:true},
{field:'country',title:'Country',sortable:false},
{field:'name',title:'國家',sortable:false},
{field:'user_agent',title:'使用者代理',sortable:true}
]],
url:"sys.php",
queryParams:{op:"list_visitors"},
fitColumns:true,
singleSelect:true,
pagination:true,
pageSize:10,
rownumbers:true
});
注意, 因為 country 與 name 並非 visitors 資料表內的欄位, 所以這裡 sortable 要設為 false.
而 list_visitors 模組則改為如下 :
case "list_visitors" : {
$page=isset($_REQUEST['page']) ? intval($_REQUEST['page']) : 1;
$rows=isset($_REQUEST['rows']) ? intval($_REQUEST['rows']) : 10;
$sort=isset($_REQUEST['sort']) ? $_REQUEST['sort'] : 'id';
$order=isset($_REQUEST['order']) ? $_REQUEST['order'] : 'desc';
if (isset($_REQUEST['search_field'])) { //有 search
$where="WHERE ".$_REQUEST['search_field']." LIKE '%".
$_REQUEST['search_what']."%'";
}
else {$where="";} //無 search
$start=($page-1) * $rows; //本頁第一個列索引 (0 起始)
$SQL="SELECT COUNT(*) FROM `sys_visitors`";
$RS=run_sql($SQL);
$total=$RS[0][0]; //紀錄總筆數
$SQL="SELECT * FROM sys_visitors ".$where." ORDER BY ".
$sort." ".$order." LIMIT ".$start.",".$rows;
$RS=run_sql($SQL);
$visitors=Array();
if (is_array($RS)) {
for ($i=0; $i<count($RS); $i++) {
//查詢 IP 來源國名
$SQL='SELECT c.country,c.code FROM ip2nationCountries c,ip2nation i '.
'WHERE i.ip < INET_ATON("'.$RS[$i]["remote_addr"].'") AND '.
'c.code=i.country ORDER BY i.ip DESC LIMIT 0,1';
$RS1=run_sql($SQL);
if (is_array($RS1)) { //
$code=trim($RS1[0]["code"]); //Country code
$country=$RS1[0]["country"]; //Country name
$RS1=search("nation", "code", $code); //根據 code 查中文國名
if (is_array($RS1)) {$name=$RS1[0]["name"];}
else {$name="";}
}
else {
$country="";
$name="";
}
$visitors[$i]=Array("id" => $RS[$i]["id"],
"visit_time" => $RS[$i]["visit_time"],
"remote_addr" => $RS[$i]["remote_addr"],
"remote_port" => $RS[$i]["remote_port"],
"country" => $country,
"name" => $name,
"user_agent" => $RS[$i]["user_agent"]
);
}
}
$arr=array("total" => $total, "rows" => $visitors);
echo json_encode($arr);
break;
}
主要就是利用從 ip2nationCountries 資料表查得的國名簡碼 code, 再去 nation 這張表去查中文國名而已. 這樣就能順利顯示訪客來自哪個國家了 :
以後若發現有些 IP 沒顯示國名, 表示資料需要更新了, 只要再去 ip2nation.com 下載最新的 sql 檔匯入資料庫即可, 而 nation 資料表除非有新的獨立國家, 否則幾乎都不用更新. 有了這三張資料表, 上一篇文章所用的檔案可以丟掉了, 而且 file.php 函式庫中新增的 get_country_by_ip() 也可以拿掉了.
如果能顯示 IP 的 DNS 更好, 可以大致知道訪客來自哪個公司或機關, 但可惜沒有這樣的資料庫可用, 有的話相信也很龐大, 會佔據 MySQL 很大份量. 這可以到下列網站查詢 :
# http://ping.eu/nslookup/
最後我修改了 EasyuiCMS 的系統安裝檔 install.php, 加入上面三個資料表的建立語法 :
//建立 ip2nation 資料表 (訪客紀錄用:DUMMY:避免錯誤)
$data_array["ip"]="int(11)"; //IP 上限
$data_array["country"]="char(2)"; //國名簡碼
$result=create_table("ip2nation",$data_array);
if ($result) {$msg .= "建立資料表 ip2nation ... 完成!<br>";}
$data_array=NULL;
//建立 countries 資料表 (訪客紀錄用:DUMMY:避免錯誤)
$data_array["code"]="varchar(4)"; //國名簡碼
$data_array["iso_code_2"]="varchar(2)"; //國名簡碼
$data_array["iso_code_3"]="varchar(3)"; //國名簡碼
$data_array["iso_country"]="varchar(255)";
$data_array["country"]="varchar(255)";
$data_array["lat"]="float";
$data_array["lon"]="float";
$result=create_table("countries",$data_array);
if ($result) {$msg .= "建立資料表 countries ... 完成!<br>";}
$data_array=NULL;
//建立 nation 資料表 (訪客紀錄)
$data_array["code"]="varchar(4) PRIMARY KEY";
$data_array["name"]="varchar(255)"; //中文國名
$result=create_table("nation",$data_array);
if ($result) {$msg .= "建立資料表 nation ... 完成!<br>";}
$data_array=NULL;
//從 data 下讀取 nation.txt 寫入 nation 資料表
$file=read_file("./data/nation.txt");
$lines=explode("\n", $file);
foreach($lines as $line) {
if (strlen($line)) {
$arr=explode(',', trim($line));
//插入 nation 資料表
$data_array["code"]=$arr[0];
$data_array["name"]=$arr[1];
$result=insert("nation", $data_array);
$data_array=NULL;
}
}
這裡主要是建立 nation 這張資料表, 並從 /data 下讀取 nation.txt 填入資料表中, 而 ip2nation 與 countries 這裡只是建立空資料表, 避免尚未匯入從 ip2nation.com 下載的 ip2nation.sql 時, 程式讀取它們可能產生的錯誤.
參考 :
# INET_ATON() and INET_NTOA() in PHP?
沒有留言 :
張貼留言