從 IP 查來源國家 (二)

在上一篇文章中使用了檔案查詢的方式從訪客 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?