Its very useful to record the site visitor information like ip address, search keywords, visit url...

Today I will write a simple php script to achieve that. First we need a database table to store the information. Here is the table:

 
  CREATE TABLE `stat` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    `UserAgent` text COLLATE utf8_unicode_ci NOT NULL,
    `RequestTime` datetime DEFAULT NULL,
    `InsertDate` datetime DEFAULT NULL,
    `RequestUri` text COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY  (`ID`)
  ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
  ALTER TABLE stat ADD COLUMN cookie text COLLATE utf8_unicode_ci NOT NULL after RequestUri;
 
  ALTER TABLE stat ADD COLUMN refer text COLLATE utf8_unicode_ci NOT NULL after cookie;
 

Next, we need a function which to be called on each access. I prefer to use the url rewrite and call the function at the entry point.

 
 
function mystat() {
  $spiders = array();
  $spiders ["Mozilla/5.0 (compatible; OpenindexSpider; +http://www.openindex.io/en/webmasters/spider.html)"] = true;  
  $spiders ["Mozilla/5.0 (compatible; AhrefsBot/4.0; +http://ahrefs.com/robot/)"] = true;  
  $spiders ["Mozilla/5.0 (compatible; Ezooms/1.0; ezooms.bot@gmail.com)"] = true;
  $spiders ["Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"] = true;
  $spiders ["Mozilla/5.0 (compatible; JikeSpider; +http://shoulu.jike.com/spider.html)"] = true;
  $spiders ["360Spider"] = true;
  $spiders ["Aboundex/0.2 (http://www.aboundex.com/crawler/)"] = true;
  $spiders ["Apache-HttpClient/4.1.3 (java 1.5)"] = true;
  $spiders ["BacklinkCrawler (http://www.backlinktest.com/crawler.html)"] = true;
  $spiders ["binlar_2.6.3 test@mgmt.mic"] = true;
  $spiders ["DoCoMo/2.0 N905i(c100;TB;W24H16) (compatible; Googlebot-Mobile/2.1; +http://www.google.com/bot.html)"] = true;
  $spiders ["DoCoMo/2.0 P900i(c100;TB;W24H11) (compatible; ichiro/mobile goo; +http://search.goo.ne.jp/option/use/sub4/sub4-1/)"] = true;
  $spiders ["EasouSpider"] = true;
  $spiders ["Googlebot/2.1 (+http://www.google.com/bot.html)"] = true;
  $spiders ["HTTP_Request2/2.1.1 (http://pear.php.net/package/http_request2) PHP/5.3.2-1ubuntu4.17"] = true;
  $spiders ["ia_archiver (+http://www.alexa.com/site/help/webmasters; crawler@alexa.com)"] = true;
  $spiders ["libwww-perl/5.805"] = true;
  $spiders ["LinksCrawler 0.1beta"] = true;
  $spiders ["Mediapartners-Google"] = true;
  $spiders ["Mozilla (compatible; Mediapartners-Googlebot/2.1;(+http://www.google.com/bot.html)"] = true;
  $spiders ["Mozilla/5.0 (compatible; AhrefsBot/3.1; +http://ahrefs.com/robot/)"] = true;
  $spiders ["Mozilla/5.0 (compatible; AhrefsBot/4.0; +http://ahrefs.com/robot/)"] = true;
  $spiders ["Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)"] = true;
  $spiders ["Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)"] = true;
  $spiders ["Mozilla/5.0 (compatible; BoxBrowserTest/2.x; Linux) CSSBox/2.x (like Gecko)"] = true;
  $spiders ["Mozilla/5.0 (compatible; CompSpyBot/1.0; +http://www.compspy.com/spider.html)"] = true;
  $spiders ["Mozilla/5.0 (compatible; DCPbot/1.2; +http://domains.checkparams.com/)"] = true;
  $spiders ["Mozilla/5.0 (compatible; Exabot/3.0; +http://www.exabot.com/go/robot)"] = true;
  $spiders ["Mozilla/5.0 (compatible; Ezooms/1.0; ezooms.bot@gmail.com)"] = true;
  $spiders ["Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"] = true;
  $spiders ["Mozilla/5.0 (compatible; JikeSpider; +http://shoulu.jike.com/spider.html)"] = true;
  $spiders ["Mozilla/5.0 (compatible; Lipperhey Link Explorer; http://www.lipperhey.com/)"] = true;
  $spiders ["Mozilla/5.0 (compatible; Mail.RU_Bot/2.0; +http://go.mail.ru/help/robots)"] = true;
  $spiders ["Mozilla/5.0 (compatible; Mail.RU_Bot/2.0)"] = true;
  $spiders ["Mozilla/5.0 (compatible; Mail.RU/2.0)"] = true;
  $spiders ["Mozilla/5.0 (compatible; NetcraftSurveyAgent/1.0; +info@netcraft.com)"] = true;
  $spiders ["Mozilla/5.0 (compatible; OpenindexSpider; +http://www.openindex.io/en/webmasters/spider.html)"] = true;
  $spiders ["Mozilla/5.0 (compatible; Plukkie/1.5; http://www.botje.com/plukkie.htm)"] = true;
  $spiders ["Mozilla/5.0 (compatible; WBSearchBot/1.1; +http://www.warebay.com/bot.html)"] = true;
  $spiders ["Mozilla/5.0 (compatible; YandexBot/3.0; +http://yandex.com/bots)"] = true;
  $spiders ["Mozilla/5.0 (compatible; YodaoBot/1.0; http://www.yodao.com/help/webmaster/spider/; )"] = true;
  $spiders ["Mozilla/5.0 (compatible; YoudaoBot/1.0; http://www.youdao.com/help/webmaster/spider/; )"] = true;
  $spiders ["Mozilla/5.0(compatible; Sosospider/2.0; +http://help.soso.com/webspider.htm)"] = true;
  $spiders ["New-Sogou-Spider/1.0 (compatible; MSIE 5.5; Windows 98)"] = true;
  $spiders ["SiteInTheWorld"] = true;
  $spiders ["Sogou Pic Spider/3.0(+http://www.sogou.com/docs/help/webmasters.htm#07)"] = true;
  $spiders ["Sogou web spider/4.0(+http://www.sogou.com/docs/help/webmasters.htm#07)"] = true;
  $spiders ["Sosospider+(+http://help.soso.com/webspider.htm)"] = true;
  $spiders ["TurnitinBot/2.1 (http://www.turnitin.com/robot/crawlerinfo.html)"] = true;
  $spiders ["Wget/1.9+cvs-stable (Red Hat modified)"] = true;
  $spiders ["Wotbox/2.01 (+http://www.wotbox.com/bot/)"] = true;
  $spiders ["Yahoo! Slurp China"] = true;
  $spiders ["Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.8.0.11) Gecko/20070312 Firefox/1.5.0.11; 360Spider"] = true;
  $spiders ["Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; MDDR; .NET4.0C; .NET4.0E; .NET CLR 1.1.4322; Tablet PC 2.0); 360Spider"] = true;
  $spiders ["Mozilla/5.0 (compatible; MJ12bot/v1.4.3; http://www.majestic12.co.uk/bot.php?+)"] = true;
  $spiders ["Mozilla/5.0 (compatible; archive.org_bot +http://www.archive.org/details/archive.org_bot)"] = true;
  $spiders ["Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.8.0.11) Firefox/1.5.0.11; 360Spider"] = true;
 
 
  $useragent = $_SERVER['HTTP_USER_AGENT'];
 
  if( isset($spiders [ $useragent ]) ) return;
 
  $ip = getip();
  if($ip == "127.0.0.1") return;
 
  $useragent = $_SERVER['HTTP_USER_AGENT'];
 
  $time =  date("Y-m-d H:i:s",$_SERVER['REQUEST_TIME']);
  $requesturi = $_SERVER['REQUEST_URI'];
  $cookie = $_SERVER['HTTP_COOKIE'];
  $refer = isset($_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : "not set";
 
  $sql = "insert into stat values('','".$ip."','".$useragent."','".$time."',now(),'".$requesturi."','".$cookie."','".$refer."')";
  $d = new DSN();
  $d->insertsql($sql);
 
}
 
 
 

The spiders array is a list of crawler, usually you don't want this information show up in your statistical database. Most of them are useless. The list may not complete , you can add if you find out more spiders.

To get the IP address of the visitor can be tricky, here is a fine solution , it can get the real ip address even user are using a proxy server.

 
function getip(){
   if(getenv("HTTP_CLIENT_IP") && strcasecmp(getenv("HTTP_CLIENT_IP"), "unknown"))
   {
     $ip = getenv("HTTP_CLIENT_IP");
   } else if(getenv("HTTP_X_FORWARDED_FOR") && strcasecmp(getenv("HTTP_X_FORWARDED_FOR"), "unknown")){
     $ip = getenv("HTTP_X_FORWARDED_FOR");
   } else if(getenv("REMOTE_ADDR") && strcasecmp(getenv("REMOTE_ADDR"), "unknown"))
   {
     $ip = getenv("REMOTE_ADDR");
   } else if(isset ($_SERVER['REMOTE_ADDR']) && $_SERVER['REMOTE_ADDR'] && strcasecmp($_SERVER['REMOTE_ADDR'], "unknown")){
      $ip = $_SERVER['REMOTE_ADDR'];
   }else{
      $ip = "unknown";
   }
     return ($ip);
  }
 
 

The DSN class is a simple class that wrap php mysql api.

 
<?php
 
class DSN  {
    public $host;
    public $user;
    public $pass;
    public $Scheme;
    public $Connection;
 
    public function __construct() {
 
 
        $this->host = "localhost";
        $this->user = "root";
        $this->pass = "pass";
        $this->Scheme = "stat";
 
 
        $this->Connection  = mysql_connect( $this->host, $this->user, $this->pass );
 
        mysql_select_db( $this->Scheme, $this->Connection );
 
    }
 
 
    public function insertsql( $sql ) {
        return mysql_query($sql,$this->Connection);
    }
 
    public function updatesql( $sql ) {
        return mysql_query($sql,$this->Connection);
    }
 
    public function querysql( $sql ) {
 
        $Result = mysql_query($sql,$this->Connection);
        if(!$Result) { echo 'mysql_query error, sql is :' . $sql . ' error code is :  ' . mysql_error();}
        $numrows = mysql_num_rows($Result);
 
        $r = array();
        if ( $numrows == 0  ) return NULL;
        else {
            while( $row = mysql_fetch_object($Result) ) {
                $r[] = $row;
            }
            return $r;
        }
    }
 
}
?>