(0) Introduction
(1) Create database
(2) Create PHP progs
(3) Connect to Database
(4) Store data
(5) Simple data dump
(6) Spider Analysis
(7) Graphics with GD
(8) Months and Days
(9) Pie Charts
(10) Visitor's country
(11) Feedback

spider analysis tutorial

This project uses PHP, SQL and mySQLi to analyse page hits and spider (robot) activity on your web site. With this page, I will attempt to describe how I undertook the project, in case you should wish to try such a project (or similar). This is a good and useful way to learn about PHP, and how it interfaces with mySQL.

You will need PHP and mySQL/mySQLi facilities via your web hosting company. The mySQLi content can be replaced by mySQL if required. You will need PHP GD to produce the graphics (optional). This project is suitable as a first PHP project, but a working knowledge of HTML is assumed. I strongly recommend doing the PHP and SQL tutorials on W3Schools.com, and using this excellent site as a reference at all times.

Click here to see the latest analysis on my domain

(1) Set up a mySQL table to store the data

This is easily done via phpMyAdmin, or alternatively can be done in SQL by the CREATE TABLE 'yourdb' command. Use an auto-incrementing integer field to index the table as a primary key. Create fields for the data that you will need to store:

id [Auto incrementing primary key, int(6)] time [varchar(23)] ip [varchar(15)] page [varchar(5)] agent [varchar(250)] refer [varchar(250)] browser [varchar(20)] link [varchar(20)] xday [integer(6)] // see later for definition xmonth [integer(6)] // see later for definition

(2) Create php files: prog.php, dbload.php and analysis.php

"prog.php" is the program file that you wish to analyse. For each program, you will need to add the following code within the PHP. All PHP code should be entered between the usual PHP tags <?php and ?>.

<?php $page="SPIDA"; // your for the page to be analysed require ("dbload.php"); ?>

Note that each file must have the extension .php, so any existing .htm files will have to be renamed, and re-directions put in place. It is worth it though! PHP works server-side, and is compiled (parsed) at the server. This means that you will have to load the files to the server each time you wish to check them. Also, it means that confidential information such as passwords cannot be seen by viewing the source.

(3) Make the connection to the database, and obtain key information

The first part of the dbload.php program should make the link to the mySQL database, and obtain ip, spider, browser and link information. You will need to enter your own userid, password and database in the first line below.

<?php $mysqli = new mysqli('localhost', 'userid', 'password', 'database') or die('Could not establish database connection'); $time=gmdate(DATE_RFC822); // Greenwich mean time $time=substr($time,0,23); $agent=$_SERVER["HTTP_USER_AGENT"];// contains the browser/spider info $ip=$_SERVER["REMOTE_ADDR"]; // contains the ip address $refer=$_SERVER["HTTP_REFERER"]; // contains link information $stmt->close(); ?>

(4) Store data in the mySQL database

The second part of the dbload.php program will write info to the mySQL database.

Here I introduce two derived variables: 'browser' (which will contain spider info too) and 'link'. The following code should be inserted into the code above, just before the close statement.

$browser="Other"; if (strchr($agent,"MSIE")) $browser="IE"; if (strchr($agent,"Firefox")) $browser="Firefox"; if (strchr($agent,"Google")) $browser="Google spider"; if (strchr($agent,"Yahoo")) $browser="Yahoo spider"; $link=""; if ($refer) $link="Other"; if (strchr($refer,"Google")) $link="Google"; if (strchr($refer,"yahoo")) $link="Yahoo"; $agent=strval($agent); // set to string as a precaution if field blank $refer=strval($refer); $browser=strval($browser); $link=strval($link); $stmt=$mysqli->prepare("INSERT into yourdb(time,page,ip,agent,refer,xday,xmonth,browser,link) values (?,?,?,?,?,?,?,?,?)"); $stmt->bind_param('sssssiiss',$time,$page,$ip,$agent,$refer,$xday,$xmonth,$browser,$link); $stmt->execute();

xday (incremental day counter) and xmonth (incremental month counter) will need calculating and storing if you wish to produce a graph of hits/crawls by day (see sections 7 and 8).

Over time, you can analyse the variables browser and link where they contain 'Other' to see which spiders are crawling your site, and who is linking to you; and then expand the code above.

(5) Analyse the data - simple data dump

Now, you can start on the program analysis.php, which basically can be very simple, or as advanced as you like.

Firstly, make the connection to the database as before. The following is a simple dump of info for the last 100 records.

$stmt=$mysqli->prepare("SELECT time,page,browser,link,ip FROM yourdb WHERE browser not like '%spider%' ORDER BY id DESC LIMIT 100"); echo "<table>"; echo "<tr>"; echo "<th width='180' text-align='left'>date</th>"; echo "<th width='140' text-align='left'>page</th>"; echo "<th width='140' text-align='left'>browser</th>"; echo "<th width='100' text-align='left'>link</th>"; echo "<th width='140' text-align='left'>ip</th>"; echo "</tr>"; $stmt->execute(); $stmt->bind_result($time1,$page1,$browser1,$link1,$ip1); while($stmt->fetch()) { echo "<tr>"; echo "<td>".$time1."</td>"; echo "<td>".$page1."</td>"; echo "<td>".$browser1."</td>"; echo "<td>".$link1."</td>"; echo "<td>".$ip1."</td>"; echo "</tr>"; } echo "</table>";

(6) Analyse the data - spider analysis

Make the connection to the database as before. This is a table containing a count of spider crawls.

$stmt=$mysqli->prepare("SELECT COUNT(browser) FROM `yourdb` where browser like '%spider%'"); $stmt->execute(); $stmt->bind_result($x);   while($stmt->fetch()) $totspider=$x;   $stmt=$mysqli->prepare("SELECT browser,COUNT(browser) FROM `yourdb` where browser like '%spider%' GROUP BY browser ORDER BY COUNT(browser) DESC LIMIT 27"); echo "<tr>"; echo "<th width='120' text-align='left'>spider</th>"; echo "<th width='100' text-align='left'>count</th>"; echo "<th width='80' text-align='left'></th>"; echo "</tr>"; $stmt->execute(); $stmt->bind_result($browser1,$nn); while($stmt->fetch()) { echo "<tr>"; echo "<td>".$browser1."</td>"; echo "<td>".$nn."</td>"; $a=$nn*100; $b=number_format($a/$totspider,1); echo "<td>".$b."</td>"; echo "</tr>"; } echo "</table>";

(7) Introduce graphics

Here I use PHP GD2 to produce a dynamic graph of hits over the past month.

I introduce a variable xday which increments by 1 each day. This will need adding to the database load program.

The following code should go in a new program called analysis2.php, which is called from analysis.php as follows:

<?php require("analysis2.php"); ?> <img src="graph.PNG"/>

Program analysis2.php:

<?php $image=imagecreate(400,200); $white=imagecolorallocate($image,255,255,255); $black=imagecolorallocate($image,0,0,0); $grey=imagecolorallocate($image,128,128,128); $red=imagecolorallocate($image,255,0,0); imagefill($image,0,0,$white); imagestring($image,3,8,0,"400",$black); imagestring($image,5,150,0,"Hits by day",$red); imageline($image,0,0,0,200,$black); imageline($image,1,0,1,200,$black); imageline($image,0,200,400,200,$black); imageline($image,0,199,400,199,$black); imageline($image,$x,198,$x,194,$black); imageline($image,0,100,400,100,$grey); $stmt=$mysqli->prepare("SELECT xday,COUNT(xday) FROM `yourdb` WHERE browser not like '%spider%' GROUP BY xday ORDER BY xday DESC LIMIT 33"); $x=400; $y=200; $oldx=400; $oldy=200; $stmt->execute(); $stmt->bind_result($notused,$n);   while($stmt->fetch()) { $n=$row['COUNT(xday)']; $y=200-($n/2);// set to display max 400 hits, change to /4 to show 800 etc. if($y<0) $y=0; imageline($image,$x,$y,$oldx,$oldy,$red); $oldx=$x; $oldy=$y; $x=$x-12; } imagePNG($image,'graph.PNG'); imagedestroy($image); ?>

(8) xmonth and xday

Here is an example of code to calculate xmonth and xday. The code is set as at March 2010, with a value of xday=1 equating to 1st Jan 2010. It will need amendment going forwards to cater for future dates, leap years etc.

$day=gmdate("d"); $month=gmdate("m"); $year=gmdate("y"); switch($year) { case "11"; $x=12; break; case "12"; $x=24; break; case "13"; $x=36; break; default; $x=0; } $xmonth=$month+$x; $x=0 switch($month) { case "01": $x=365; break; case "02": $x=396; break; case "03": $x=59; break; case "04": $x=90; break; case "05": $x=120; break; case "06": $x=151; break; case "07": $x=181; break; case "08": $x=212; break; case "09": $x=243; break; case "10": $x=273; break; case "11": $x=304; break; case "12": $x=334; break; default: $x=0; } $xday=$day+$x;

(9) Pie charts

PHP scripts to produce pie charts are available free on the internet.

An example is provided below :

Click here for atokar.net solution (this is what I used).

(10) Identifying the visitor's country

There are several resources on the internet providing free look-up databases of ip address to country.

An example is provided below :

Click here for webnet77.com solution (this is what I used).

(11) Feedback

Please feed back any comments or mistakes or areas where clarification is required. Thanks.

Comments here please...

[return to top of page]