spam used to be a piggy

how to keep a traffic log with mysql and php

juust | November 16, 2008

A rather obsolete post. I hacked a quick referrer tracking script together because I wanted to see how Googlebot indexed my serp tool pages. I can use Google analytics or phpMyVisites, but that is not as rewarding as keeping my own logs.

I start with a mysql table LOG to store the traffic data

  1. CREATE TABLE IF NOT EXISTS `log` (
  2.   `id` bigint(11) NOT NULL auto_increment,
  3.   `ip` varchar(20) NOT NULL,
  4.   `page` varchar(120) NOT NULL,
  5.   `visitpage` varchar(120) NOT NULL,
  6.   `visitdate` varchar(25) NOT NULL,
  7.   `agent` varchar(200) NOT NULL,
  8.   `protocol` varchar(10) NOT NULL,
  9.   `url` varchar(120) NOT NULL,
  10.   `visitpath` varchar(80) NOT NULL,
  11.   PRIMARY KEY  (`id`)
  12. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

on every page I track, I start with

  1. session_start();
  2. log_hit();

The function log_hit() relies on the php $_SERVER array that holds all data the web server collects:

  1. function log_hit();
  2.         $ip   = $_SERVER['REMOTE_ADDR'];
  3.  
  4. //if it is my own ip, exit the routine
  5.  if($ip =="my.own.ip.here") return;
  6.  
  7. //what page is my visitor at ?
  8.  $page  = "http://".$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF'];
  9.  
  10. //what page did he come from ?
  11.  $visitpage  = $_SERVER['HTTP_REFERER'];
  12.  
  13.  $visitdate  = date("YmdHi");
  14.  
  15. //what user-string is it (iow: is it googlebot and pals or an actual visitor ?)
  16.  $agent      = $_SERVER["HTTP_USER_AGENT"];        
  17.  
  18. //the rest i dont use but i'll throw them in just for fun :
  19.  $protocol   = $_SERVER['SERVER_PROTOCOL'];
  20.  $method    = $_SERVER['REQUEST_METHOD'];
  21.  $url           = $_SERVER['REQUEST_URI'];
  22.  $visitpath   = parse_url($page, PHP_URL_PATH);
  23.  
  24.  if(!$ip) {} else {
  25. //if I have an Ip, register the hit :
  26. //connect to mysql table
  27.   $m=connect_data();
  28.  
  29. //and store it in the LOG table
  30.   $myqry="INSERT INTO `serp_log` (
  31.  `ip` ,`page` ,`visitpage` ,`visitdate` ,`agent` ,`protocol` ,`url` ,`visitpath` ) VALUES (
  32.  '".$ip."', '".$page."', '".$visitpage."', '".$visitdate."', '".$agent."', '".$protocol."', '".$url."', '".$visitpath."')";
  33.   mysql_query($myqry, $m) or die('store log :'.mysql_error());
  34.   mysql_close($m);
  35. }

the connect_data() function is standard mysql connection

  1. function connect_data() {
  2.   $DB_USER =  "user";
  3.   $DB_PASSWORD = "password";
  4.   $DB_HOST = "server";
  5.   $DB_DATA = "database";
  6.   $link =  mysql_connect($DB_HOST, $DB_USER, $DB_PASSWORD) or $error = mysql_error();
  7.   if (!$link) {
  8.       return $error;
  9.   }  
  10.      mysql_select_db($DB_DATA, $link) or $error = mysql_error();
  11.   return $link;
  12.  }

It is far from perfect, but it works. It helps check the crawl-rate of pages. You can filter out the useragent ‘Googlebot’ and run a query grouping useragent by date and see what times Googlebot comes by.

  1. //66.249.72.143 is GoogleBot
  2. check_traffic_by_IP('66.249.72.143' );
  3.  
  4. function check_traffic_by_IP($thisIP) {
  5.  $m = connect_data();
  6.  $qry = "SELECT * from `log` WHERE `ip` = '".$thisIP."' GROUP BY `visitdate`";
  7.  $lst = mysql_query($qry, $m);
  8.  echo '<table><tbody>';
  9.  while($row=mysql_fetch_assoc($lst)) {
  10.   echo '<tr><td>'.$row['visitdate'].'</td></tr>';
  11.  }
  12.  echo '</tbody></table>';
  13. }

…or exclude all records if the UserAgent indicates it was a Bot, and get a listing of organic beings visiting the pages :

  1. function check_real_traffic() {
  2.  $m = connect_data();
  3.  
  4. //the bots
  5.  $bots = array('googlebot', 'anonymouse');
  6.  
  7.  $qry = "SELECT * from `log`";
  8.  $lst = mysql_query($qry, $m);
  9.  
  10.  echo '<table><tbody>';
  11.  
  12.  while($row=mysql_fetch_assoc($lst)) {
  13.  
  14. //check if the entries in the bots-array
  15. //occur in the stored useragent string
  16.   $traffic=-1;
  17.   for($i=0;$i<count ($bots);$i++) {
  18.    if(strpos(strtolower($row['agent']), $bots[$i])>0) $traffic+=1;
  19.   }
  20.  
  21. //if it's not a bot (then the $traffic var is still -1), output the info
  22.   if($traffic==-1) {
  23.                echo '<tr><td>'.$row['visitdate'].'</td><td>'.$row['ip'].'</td><td>'.$row['page'].'</td></tr>';
  24.                 }
  25.  }
  26.  echo '</count></tbody></table>';
  27. }

…that shows ‘organic’ visitors.

other resources
tek-tips.com/ Limit Requests per day by IP

phpMyVisites open source free referrer and traffic analysis

Comments
2 Comments »
Categories
mysql, php
Tags
, ,
Comments rss Comments rss
Trackback Trackback

php classes

juust | July 28, 2008

These are some small classes (well you can call em a class, anyway) I wrote the past months ”

robots.txt parser small class implementation to load and parse robots.txt and test urls against it.

fibonacci toy-programming :) these nasty iterations and recursions and stuff…

pagerank that one is actually fun, based on ian roger’s examples, a class to assert the rank of pages in a site. I built one into a spider and am making a stand alone version, also with the toolbarqueries thingy to test and see if and to what measure it’s accurate.

some resources on php classes

php.net
In PHP 5 there is a new Object Model. PHP’s handling of objects has been completely rewritten, allowing for better performance and more features.
php.net

php classes
Freely available programming classes of objects written with the web scripting language named PHP.
www.phpclasses.org/

finalwebsites.com
Free PHP scripts, classes and snippets
finalwebsites.com

Comments
No Comments »
Categories
Uncategorized
Tags
, , ,
Comments rss Comments rss
Trackback Trackback

robots.txt parser php class

juust |

for completeness, one from my other site

  1.  
  2. Class Robots {
  3.  
  4.  var $Agents = array();
  5.  var $my_user_agent="User-agent: *"; //my useragent
  6.  
  7.  public function __construct($domain) {
  8.  
  9.   $MyAgent = Agents($this->$my_user_agent);
  10.  
  11.   $robotsdomain=trim("http://".$domain);
  12.   $stripped_robotsdomain=str_replace("/","",$robotsdomain);
  13.   $robots=Read_Content(trim("http://".$domain).'/robots.txt');
  14.   $robots=explode("\n",$robots);
  15.   for ($i=0;$i<sizeof ($robots);$i++){
  16.  
  17.    if (trim($robots[$i])==$this->my_user_agent){ // rules for agent: *
  18.     for ($checkrules=1;$checkrules&lt;10;$checkrules++){
  19.      if (trim($robots[$i+$checkrules])!=""){
  20.  
  21.       $pos = strpos( $current_line[$count],"User-agent");
  22.       if (is_integer($pos)) break;
  23.  
  24.       $pos = strpos( $current_line[$count],"#");
  25.       if (is_integer($pos)) $current_line[$count]=substr($current_line[$count],0,$pos);
  26.  
  27.       $disallow_line=str_replace("Disallow: ", "" ,$robots[$i+$checkrules]);
  28.       $disallow_line=str_replace("/", "" ,$disallow_line);
  29.       $Rule = $MyAgent->DisallowedDirectories($stripped_robotsdomain.$disallow_line);
  30.  
  31.       $count++;
  32.       }
  33.      }
  34.     }
  35.    }
  36.  
  37.  }
  38.  
  39.  
  40.  public function check($url) {
  41.   $forbidden=1;
  42.   $stripped_current_url=str_replace("/", "" ,$url);
  43.   $MyAgent = $this->Agents($my_user_agent);
  44.   foreach($MyAgent->DisallowedDirectories as $dir) {
  45.     if (preg_match("/".trim($dir->path)."/i",$stripped_current_url)) {$forbidden=0;}
  46.   }
  47.   return $forbidden;
  48.  }
  49.  
  50.  public function Agents($code) {
  51.   if(!$this->Agents[$code]) {
  52.    $this->Agents[$code] = new Agent($code);
  53.   }
  54.   return $this->Agents[$code];
  55.  }
  56.  
  57.         public function Read_Content($url){// Open een url return content
  58.          $handle=@fopen($url,"r");
  59.          if($handle){
  60.            $contents = fread ($handle, 10000);
  61.            fclose($handle);
  62.   }
  63.           return $contents;
  64.  }
  65.  
  66. }
  67.  
  68. Class Agent {
  69.  
  70.  var $DisallowedDirectories = array();
  71.  
  72.  public function DisallowedDirectories($code) {
  73.   if(!$this->DisallowedDirectories[$code]) {
  74.    $this->DisallowedDirectories[$code] = new DisallowedDirectory($code);
  75.   }
  76.   return $this->DisallowedDirectories[$code];
  77.  }
  78.  
  79. }
  80.  
  81. Class DisallowedDirectory {
  82.  
  83.  var $path;
  84.  var $pathstripped;
  85.  
  86.  
  87.  public function __construct($apath) {
  88.   $this->path=$apath;
  89.   this->pathstripped=str_replace("/", "" ,$apath);
  90.  }
  91.  
  92. }
  93.  
  94.  
  95. function robots_allowed($url){
  96.  $current_url=$url;
  97.  $xmp=explode("/", $current_url."/");
  98.  $robotsdomain=trim("http://".$xmp[2]);
  99.  $stipped_robotsdomain=str_replace("/","",$robotsdomain);
  100.  $stripped_current_url=str_replace("/", "" ,$url);
  101.  $my_user_agent="User-agent: *"; //my useragent
  102.  
  103.  $robots=Read_Content($robotsdomain.'/robots.txt');
  104.  $robots=explode("\n",$robots);
  105.  for ($i=0;$i</sizeof><sizeof ($robots);$i++){
  106.  
  107.  if (trim($robots[$i])==$my_user_agent){ // rules for agent: *
  108.   for ($checkrules=1;$checkrules&lt;10;$checkrules++){
  109.    if (trim($robots[$i+$checkrules])!=""){
  110.  
  111.     $pos = strpos( $current_line[$count],"User-agent");
  112.     if (is_integer($pos)) break;
  113.  
  114.     $pos = strpos( $current_line[$count],"#");
  115.     if (is_integer($pos)) $current_line[$count]=substr($current_line[$count],0,$pos);
  116.     $disallow_line=str_replace("Disallow: ", "" ,$robots[$i+$checkrules]);
  117.     //$disallow_line=str_replace("http://", "" ,$disallow_line);
  118.     $disallow_line=str_replace("/", "" ,$disallow_line);
  119.     $newdata[$num]=$stipped_robotsdomain.$disallow_line;
  120.  
  121.  
  122.     $num++;
  123.     $count++;
  124.     }
  125.    }
  126.   }
  127.  }
  128.  
  129.  $forbidden=1;
  130.  for ($last=0;$last&lt;20;$last++){
  131.   if (trim($newdata[$last])!=""){
  132.    if (preg_match("/".trim($newdata[$last])."/i",$stripped_current_url)) {$forbidden=0;}
  133.   }
  134.  }
  135.  return $forbidden;
  136. }

usage

  1. $thisurl = "http://www.juust.org/seo/blackwidow/index.php";
  2. $rb = new Robots(parse_url($thisurl, PHP_URL_HOST));
  3. $rb->check($thisurl);

that should return 0, disallowed

the advantage of using a class, I parse the file only once, so if I am spidering I dont have to parse the dumb text every time to check if a file is ‘accessible’. It’s just a rough sketch.

this is the original code :
# Original PHP code by Chirp Internet: www.chirp.com.au
# Please acknowledge use of this code by including this header.
# http://www.the-art-of-web.com/php/parse-robots/

  1. function PHProbots_allowed($url, $useragent=false) {
  2.  
  3. # parse url to retrieve host and path
  4. $parsed = parse_url($url);
  5. $agents = array(preg_quote('*'));
  6. if($useragent) $agents[] = preg_quote($useragent);
  7. $agents = implode('|', $agents);
  8.  
  9. # location of robots.txt file
  10. $robotstxt = @file("http://{$parsed['host']}/robots.txt");
  11. if(!$robotstxt) return true;
  12. $rules = array();
  13. $ruleapplies = false;
  14.  
  15. foreach($robotstxt as $line) {
  16. # skip blank lines if(!$line = trim($line)) continue;
  17. # following rules only apply if User-agent matches $useragent or '*'
  18. if(preg_match('/User-agent: (.*)/i', $line, $match)) {
  19. $ruleapplies = preg_match("/($agents)/i", $match[1]);
  20. }
  21. if($ruleapplies && preg_match('/Disallow:(.*)/i', $line, $regs)) {
  22. # an empty rule implies full access - no further tests required
  23. if(!$regs[1]) return true;
  24. # add rules that apply to array for testing
  25. $rules[] = preg_quote(trim($regs[1]), '/');
  26. }
  27. }
  28. foreach($rules as $rule) {
  29. # check if page is disallowed to us
  30. if(preg_match("/^$rule/", $parsed['path'])) return false;
  31. }
  32. # page is not disallowed
  33. return true;
  34. }
  35.  
  36. }

Comments
No Comments »
Categories
Uncategorized
Tags
, ,
Comments rss Comments rss
Trackback Trackback

908 confirmed spam kills