GrabPERF — Performance Measurement
GrabIT2 — HTTP(S) File Download Tool
GrabIP — IP-to-Country Mapping
IP Address to IP Number Converter
Hit Tracking with PHP and MySQL
©2000-2008 Stephen Pierzchala
Hit Tracking with PHP and MySQL
Recently there was an outage at a hit-tracking vendor I was using to track the hits on my externally hosted blog, leaving me with a gap in my visitor data several hours long. While this was an inconvenience for me, I realized that this could be mission critical failure to an online business reliant on this data.
To resolve this, I used the PHP HTTP environment variables and the built-in function for converting IP addresses to IP numbers to create my own hit-tracker. It is a rudimentary tracking tool, but it provides me with the basic information I need to track visitors.
To begin, I wrote a simple PHP script to insert tracking data into a MySQL database.
<?php
$mysql_server = "[DATABASE NAME OR IP]";
$mysql_user_name = "[DBUSER]";
$mysql_user_pass = "[DBPASSWD]";
$link = mysql_connect($mysql_server, $mysql_user_name, $mysql_user_pass, MYSQL_CLIENT_COMPRESS) or die("Could not connect to MySQL database"
);
mysql_select_db("logger") or die("Could not select logger database");
$logtime = date("YmdHis");
$ipquery = sprintf("%u",ip2long($REMOTE_ADDR));
if ((!ereg("[FILTER IPS IN HERE]",$REMOTE_ADDR))){
$query2 = "INSERT into logger.blog_log values ($logtime,$ipquery,'$HTTP_USER_AGENT','$HTTP_REFERER')";
mysql_query($query2) or die("Log Insert Failed");
mysql_close($link);
}
?>
<html>
<head>
<META HTTP-EQUIV=Refresh CONTENT="0; URL=/images/dot_clear.gif"/>
</head>
</html>
Next, I created the database table.
DROP TABLE IF EXISTS `blog_log`; CREATE TABLE `blog_log` ( `date` timestamp NOT NULL default '0000-00-00 00:00:00', `ip_num` double NOT NULL default '0', `uagent` varchar(200) default NULL, `visited_page` varchar(200) NOT NULL default '', UNIQUE KEY `date` (`date`,`ip_num`,`visited_page`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
It's done. I can now log any request I want using this embedded tracker. But how can I invoke it and have the META REFRESH work? Turns out that IFRAMES are the perfect way.
I use TypePad, so I created an IFRAME reference in one of my Typelists, using the 'Title' field.
<iframe src="trackme.php" frameborder="0" height="1" width="1"></iframe>
Data should begin flowing to your database immediately. This sample snippet of code will allow you to pull data for a selected day and list each individual hit.
$query1 = "SELECT
bl.ip_num,
DATE_FORMAT(bl.date,'%d/%b/%Y %H:%i:%s') AS NEW_DATE,
bl.uagent,
bl.visited_page
FROM blog_log bl
WHERE
DATE_FORMAT(bl.date,'%Y%m%d') ='$YMD'
and uagent not REGEXP '(.*bot.*|.*crawl.*|.*spider.*|^-$|.*slurp.*|.*walker.*|.*lwp.*|.*teoma.*|.*aggregator.*|.*reader.*|.*libwww.*)'
ORDER BY bl.date ASC";
print "<table border=\"1\">\n";
print "<tr><td>IP</td><td>DATE</td><td>USER-AGENT</td><td>PAGE VIEWED</td></tr>";
while ($row = mysql_fetch_array($result1)) {
$visitor = long2ip($row[ip_num]);
print "<tr><td>$visitor</td><td nowrap>$row[NEW_DATE]</td><td nowrap>$row[uagent]</td><td>";
if ($row[visited_page] == ""){
print " --- </td></tr>\n";
} else {
print "<a href=\"$row[visited_page]\" target=\_blank\">$row[visited_page]</a></td></tr>\n";
}
}
mysql_close($link);
And that's it. A few lines of code and you're done. With a little tweaking, you can integrate the IP number data with a number of Geographic IP databases available for purchase to track by country and ISP, and using graphics applications for PHP, you can add graphs.