Building Traffic Reports

Yesterday, in my article about tracking user data we talked about how to access and store data about your web sites traffic. Today we will discuss how to use it.

To make our traffic report we need to collect, store and retrive the data about each of our users. For this example we'll use MySQL as our data store. We start by establishing a connection to a databse named traffic then using the array we built yesterday save the user data to the vistors table.

$you = array();
$you['ip'] = $_SERVER['REMOTE_ADDR'];
$you['hostaddress'] = gethostbyaddr($you['ip']);
$you['browser'] = $_SERVER['HTTP_USER_AGENT'];
$you['referer'] = $_SERVER['HTTP_REFERER'];

mysql_connect($server, $username, $password);
mysql_select_db('traffic');

$query = "INSERT INTO visitors ip='{$you['ip']}', "
    . "hostaddress='{$you['hostaddress']}',"
    . "browser='{$you['browser']}',referer='{$you['referer']}'"

mysql_query($query);

Now that we have saved some data we need to run some reports. The first example will return a simple log of all the users activity.

<?php
echo "<table>";
$query = 'SELECT * FROM visitors ORDER id DESC';
$rows = mysql_fetch_object(mysql_query($query));
while($results = $rows){
    echo "<tr>";
    echo "<td>{$results->ip}</td>";
    echo "<td>{$results->hostaddress}</td>";
    echo "<td>{$results->browser}</td>";
    echo "<td>{$results->referer}</td>";
    echo "</tr>";
}
echo "</table>";
?>

At this point you should have a table similar to the following.

| IP | Host | Referer | Browser | | --- | --- | --- | --- | | 127.0.0.1 | localhost | | Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/28.0.1500.71 Safari/537.36 |

That's it, we now have a simple log file of our websites traffic. Later we'll discuss a more detailed analysis of your sites traffic.