Creating a Full Statistics Suite With Administration
on Thu Jan 08 18:27:59 GMT 2009 in PHP and viewed 16950 times
The most important part of your website is your visitors. You should know where they come from, what they use, where they go. There are various statistics software that can tell you all these things, but why use a pre-made one when you can build one yourself? Here, you’ll learn how to track where your visitor goes, where they come from, what browser they use, and how many total hits and unique hits you get complete with a login check.
Step One: Introduction and Database
This statistics suite will need to track each individual hit. Each hit has an ip, a referrer (the page they came from), the browser they’re using, the date they access the page, and the page that they access. The table you create should reflect this in the database.
Here’s what your complete backend will look like.
Username: username Password: password
The statistics will display for any PHP based example in any of my other tutorials.
CREATE TABLE `statistics` (
`id` int(11) NOT NULL auto_increment,
`ip` varchar(16) default NULL,
`referrer` text,
`browser` text,
`date` date NOT NULL default '0000-00-00',
`page` text NOT NULL,
PRIMARY KEY (`id`)
)
Step Two: The Include File (Inserting the Row)
The very first thing you need to do, most likely, is to start to fill the database up with rows, hits, statistics, users, whatever you want to call them. But on the whole, inserting the row into the database is basically the easiest part of the whole statistics suite.
<?php
$connection = mysql_connect("localhost",
"username",
"password");
mysql_select_db("database", $connection);
mysql_query("INSERT INTO statistics VALUES('',
'".$_SERVER['REMOTE_ADDR']."',
'".$_SERVER['HTTP_REFERER']."',
'".$_SERVER['HTTP_USER_AGENT']."',
'".date("Y-m-d")."',
'".$_SERVER['REQUEST_URI']."')") or die(mysql_error());
mysql_close($connection);
?>
Basically, a very easy script. Connect to the database, insert a row, and close the connection. The values of the row are easy as well. The first is blank because it’s just the id column, which is automatic, the second column is the ip of the user, then the referrer (the last page the user was on), then the user agent (which is the info about the browser and operating system and etc), then the date, and finally the page it was displayed on.
But you have a slight conundrum here. If you leave the browser column as just $_SERVER[‘HTTP_USER_AGENT’], you get a value that looks sort of like this: “Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.10) Gecko/20050716 Firefox/1.0.6”. And that’s just not great. There’ll be lots of different values. You want to be able to select a universal browser like just Firefox or IE to show how many people use that. So to circumvent that, just use a bunch of if and elseifs and the function eregi.
Eregi is a great little function. It looks for a string contained in another string, and it’s case-insensitive. That means that “firefox”, “FIREFOX”, and “fIrEfOx” should all match. Using this function, you should be able to look through the gigantic user agent information, and find the identifiers for Firefox, IE, Opera, and others.
Looking at the updated code…
if(eregi("opera", $_SERVER['HTTP_USER_AGENT'])){
$browser = "Opera";
}
elseif(eregi("msie", $_SERVER['HTTP_USER_AGENT'])){
$browser = "Internet Explorer";
}
elseif(eregi("Konqueror", $_SERVER['HTTP_USER_AGENT'])){
$browser = "Konqueror";
}
elseif(eregi("Firefox", $_SERVER['HTTP_USER_AGENT'])){
$browser = "Firefox";
}
elseif(eregi("safari", $_SERVER['HTTP_USER_AGENT'])){
$browser = "Safari";
}
elseif(eregi("netscape", $_SERVER['HTTP_USER_AGENT'])){
$browser = "Netscape";
}
elseif(eregi("AOL", $_SERVER['HTTP_USER_AGENT'])){
$browser = "AOL";
}
else {
$browser = "Other";
}
mysql_query("INSERT INTO statistics VALUES('',
'".$_SERVER['REMOTE_ADDR']."',
'".$_SERVER['HTTP_REFERER']."',
'".$browser."',
'".date("Y-m-d")."',
'".$_SERVER['REQUEST_URI']."')") or die(mysql_error());
Quite long, quite bloated, but it gets the job done. There’s seven major browsers, and it should be good enough.
Now that you’re done with that, save it as include.php, upload it, and browse to it to test it out and see if it works. If you get no errors, then you’re golden. Move onto the next step.
Step Three: Statistics Backend
Allright, now it’s time to move onto the meat, the steak of the whole operation. The filet. It’s that good.
First thing to do is decide what actions you need. You need a general overview telling the total hits and the unique hits, a page to show the referrers, the displayed pages, and of course the browsers used.
Let’s jump right into it.
<html>
<head>
<title>Statistics Suite</title>
</head>
<body>
<ul id="navigation">
<li><a href="statistics.php">Main</a></li>
<li><a href="statistics.php?action=referrer">Referrers</a></li>
<li><a href="statistics.php?action=displayed">Displayed Pages</a></li>
<li><a href="statistics.php?action=browsers">Browsers Used</a></li>
</ul> <br /><br />
<?php
$connection = mysql_connect("localhost",
"username",
"password");
mysql_select_db("database", $connection);
switch($_GET["action"]){
default:
break;
case "select":
break;
case "referrer":
break;
case "displayed":
break;
case "browsers":
break;
}
?>
</body>
</html>
Shouldn’t be too bad. There’s only five actions. Not bad. A database connection. And a switch on the GET variable “action.” Should be easy right?
The Default Case
The default case. What this should do is display the total hits for the day, the unique hits for the day, and also have a form to let you select another date.
Luckily this action is probably one of the easiest.
$all = mysql_query("SELECT ip FROM statistics WHERE date = '".date("Y-m-d")."'") or die(mysql_error());
$allnum = mysql_num_rows($all);
$unique = mysql_query("SELECT DISTINCT ip FROM statistics WHERE date = '".date("Y-m-d")."'") or die(mysql_error());
$uniquenum = mysql_num_rows($unique);
echo "<h2>Hits For Today: ".date("Y-m-d")."</h2>";
echo "Total Hits Today: $allnum <br />";
echo "Unique Hits Today: $uniquenum";
Not hard at all. The idea is the first query the database for each row with the date corresponding to the current date, then count that amount of rows into the variable $allnum. Then the second two statements is to query the table for each DISTINCT (or unique, or differet) ip address with the date corresponding to today. The number it, and print it all to the screen.
Next you need the form to input another date in case you were gone a day, or you want to compare, whatever.
?>
<br />
<br />
<h2>Date Selection</h2>
<form action="statistics.php?action=select" method="post">
<b>Month (mm):</b>
<input type="text" name="month" />
<br />
<b>Day (dd):</b>
<input type="text" name="day" />
<br />
<b>Year (yyyy):</b>
<input type="text" name="year" />
<br />
<input type="submit" value="Find Date" />
</form>
<?
The first thing to do is to get out of PHP to output some plain ol’ HTML.
Next you need the form. The action it goes to is “select.”
You just need to input the month, the day, and the year. It’s a simple form. And the default case should be done.
default:
$all = mysql_query("SELECT ip FROM statistics WHERE date = '".date("Y-m-d")."'") or die(mysql_error());
$allnum = mysql_num_rows($all);
$unique = mysql_query("SELECT DISTINCT ip FROM statistics WHERE date = '".date("Y-m-d")."'") or die(mysql_error());
$uniquenum = mysql_num_rows($unique);
echo "<h2>Hits For Today: ".date("Y-m-d")."</h2>";
echo "Total Hits Today: $allnum <br />";
echo "Unique Hits Today: $uniquenum";
?>
<br />
<br />
<h2>Date Selection</h2>
<form action="statistics.php?action=select" method="post">
<b>Month (mm):</b>
<input type="text" name="month" />
<br />
<b>Day (dd):</b>
<input type="text" name="day" />
<br />
<b>Year (yyyy):</b>
<input type="text" name="year" />
<br />
<input type="submit" value="Find Date" />
</form>
<?
break;
Selecting Another Date
Now that you’ve seen today’s hits, you’re probably wondering about yesterdays, or the previous days. Well, let’s process the form now.
case "select":
$date = $_POST["year"]."-".$_POST["month"]."-".$_POST["day"];
$all = mysql_query("SELECT ip FROM statistics WHERE date = '".$date."'") or die(mysql_error());
$allnum = mysql_num_rows($all);
$unique = mysql_query("SELECT DISTINCT ip FROM statistics WHERE date = '".$date."'") or die(mysql_error());
$uniquenum = mysql_num_rows($unique);
echo "<h2>Hits For Today: ".$date."</h2>";
echo "Total Hits Today: $allnum <br />";
echo "Unique Hits Today: $uniquenum";
break;
Basically, it’s exactly the same as the default case. The only difference is that instead of using the date() function to find the current date, you take the values entered in from the previous form for the date. Then it’s just find the total hits, the unique hits, and printing it.
Finding the Referrers
One of the best things about your statistics is knowing where your visitors are coming from. It’s always good to know who is linking to you and what other websites your visitors are looking at as well.
Finding the referrers shouldn’t be too hard. Find each distinct referrer, then loop through them selecting the number of rows where the referrer equals that distinct one and output it in a table.
case "referrer":
$select = mysql_query("SELECT DISTINCT referrer FROM statistics") or die(mysql_error());
?>
<table>
<tr>
<td>Referrer</td>
<td>Hits</td>
</tr>
<?
while($r = mysql_fetch_array($select)){
$query = mysql_query("SELECT * FROM statistics WHERE referrer = '".$r[2]."'") or die(mysql_error());
$num = mysql_num_rows($query);
?>
<tr>
<td><?=$r[0]?></td>
<td><?=$num?></td>
</tr>
<?
}
echo "</table>";
break;
Just like I said. It’s just all outputted into a table, listing the referrer and the number of hits from that specific one.
The Pages Most Visited
Another important thing to know is where your visitors going. What are some of the most popular pages on your website?
The concept behind it is exactly the same as the referrer case.
case "displayed":
$select = mysql_query("SELECT DISTINCT page FROM statistics") or die(mysql_error());
?>
<table>
<tr>
<td>Page</td>
<td>Hits</td>
</tr>
<?
while($array = mysql_fetch_array($select)){
$query = mysql_query("SELECT page FROM statistics WHERE page = '".$array[0]."'") or die(mysql_error());
$num = mysql_num_rows($query);
?>
<tr>
<td><?=$array[0]?></td>
<td><?=$num?></td>
</tr>
<?
}
echo "</table>";
break;
First each distinct page is selected from the statistics table, and a table is started. Then the query is looped through and the total rows with that page are then numbered and outputted.
Well, that should be halfway done. One more action to go, and that is…
What Browsers Are Your Visitors Using?
With the “browser wars” going on, it’s always great to know which browsers your users are using. If you have a browser preference, touting that this is the greatest browser. Or of course if your website displays differently on different browsers, it’s good to know how many are using that browser.
case "browsers":
$select = mysql_query("SELECT DISTINCT browser FROM statistics") or die(mysql_error());
?>
<table>
<tr>
<td>Page</td>
<td>Hits</td>
</tr>
<?
while($array = mysql_fetch_array($select)){
$query = mysql_query("SELECT browser FROM statistics WHERE browser = '".$array[0]."'") or die(mysql_error());
$num = mysql_num_rows($query);
?>
<tr>
<td><?=$array[0]?></td>
<td><?=$num?></td>
</tr>
<?
}
echo "</table>";
break;
Again, same idea. Select the different browsers, loop through them, and number the rows of that specific browser into a table. Easy as pie.
Conclusion (of Step 3)
Let’s look at the full switching code.
switch($_GET["action"]){
default:
$all = mysql_query("SELECT ip FROM statistics WHERE date = '".date("Y-m-d")."'") or die(mysql_error());
$allnum = mysql_num_rows($all);
$unique = mysql_query("SELECT DISTINCT ip FROM statistics WHERE date = '".date("Y-m-d")."'") or die(mysql_error());
$uniquenum = mysql_num_rows($unique);
echo "<h2>Hits For Today: ".date("Y-m-d")."</h2>";
echo "Total Hits Today: $allnum <br />";
echo "Unique Hits Today: $uniquenum";
?>
<br />
<br />
<h2>Date Selection</h2>
<form action="statistics.php?action=select" method="post">
<b>Month (mm):</b>
<input type="text" name="month" />
<br />
<b>Day (dd):</b>
<input type="text" name="day" />
<br />
<b>Year (yyyy):</b>
<input type="text" name="year" />
<br />
<input type="submit" value="Find Date" />
</form>
<?
break;
case "select":
$date = $_POST["year"]."-".$_POST["month"]."-".$_POST["day"];
$all = mysql_query("SELECT ip FROM statistics WHERE date = '".$date."'") or die(mysql_error());
$allnum = mysql_num_rows($all);
$unique = mysql_query("SELECT DISTINCT ip FROM statistics WHERE date = '".$date."'") or die(mysql_error());
$uniquenum = mysql_num_rows($unique);
echo "<h2>Hits For Today: ".$date."</h2>";
echo "Total Hits Today: $allnum <br />";
echo "Unique Hits Today: $uniquenum";
break;
case "referrer":
$select = mysql_query("SELECT DISTINCT referrer FROM statistics") or die(mysql_error());
?>
<table>
<tr>
<td>Referrer</td>
<td>Hits</td>
</tr>
<?
while($r = mysql_fetch_array($select)){
$query = mysql_query("SELECT * FROM statistics WHERE referrer = '".$r[2]."'") or die(mysql_error());
$num = mysql_num_rows($query);
?>
<tr>
<td><?=$r[0]?></td>
<td><?=$num?></td>
</tr>
<?
}
echo "</table>";
break;
case "displayed":
$select = mysql_query("SELECT DISTINCT page FROM statistics") or die(mysql_error());
?>
<table>
<tr>
<td>Page</td>
<td>Hits</td>
</tr>
<?
while($array = mysql_fetch_array($select)){
$query = mysql_query("SELECT page FROM statistics WHERE page = '".$array[0]."'") or die(mysql_error());
$num = mysql_num_rows($query);
?>
<tr>
<td><?=$array[0]?></td>
<td><?=$num?></td>
</tr>
<?
}
echo "</table>";
break;
case "browsers":
$select = mysql_query("SELECT DISTINCT browser FROM statistics") or die(mysql_error());
?>
<table>
<tr>
<td>Page</td>
<td>Hits</td>
</tr>
<?
while($array = mysql_fetch_array($select)){
$query = mysql_query("SELECT browser FROM statistics WHERE browser = '".$array[0]."'") or die(mysql_error());
$num = mysql_num_rows($query);
?>
<tr>
<td><?=$array[0]?></td>
<td><?=$num?></td>
</tr>
<?
}
echo "</table>";
break;
}
Well, there’s that. You can easily stop here if you don’t need a authentication system for your statistics suite and don’t mind if others see your logs. But for those of you that do want that (or if you came here just for this) let’s press on.
Step Four: Authentication
Ah, the final step. Authentication is always fun. For this we’ll need a cookie. But first, let’s just put a big if around all the code you just entered in.
if($_COOKIE["statisticslogged"] == "yourpassphrase"){
//The switch and mysql code should go here.
}
else {
?>
<h2>Log in</h2>
<form action="statistics.php?action=2" method="post">
<label for="username"><b>Username:</b></label>
<input type="text" name="username" />
<br />
<label for="password"><b>Password:</b></label>
<input type="password" name="password" />
<br />
<input type="submit" value="Login!" />
</form>
<?
}
There’s also the else option. If you’re not logged in, you’ll be presented with a form to login. It’s a simple form. Just a username and password required. Now action two will be interesting.
You can’t put action two in the switch because that would require a cookie being set. And you can’t put it in the main PHP block because you can’t set a cookie after the headers have been set (in the <head> elements). So we’ll put it before the HTML is even started.
This code goes above <html>
<?php
if($_GET["action"] == "2"){
$username = $_POST["username"];
$password = $_POST["password"];
$referrer = "http://shadow-fox.net/examples/statistics/";
if($username == "username" && $password == "password" && str_replace("www.", "", $_SERVER['HTTP_REFERER']) == $referrer."statistics.php"){
setcookie("statisticslogged", "yourpassphrase", 0);
header("Location: statistics.php");
}
}
?>
On the whole it’s nothing new. I just used “username” and “password” as the username and password respectively. The only other thing is to block some of the more basic hacking attempts. Replace $referrer with the path to where your statistics.php file will be. Without the “statistics.php” part of course. So if statistics.php is in /statistics of http://yoursite.com, the $referrer should equal “http://yoursite.com/statistics”. That’ll make sure that any logging in attempts should come from your website. For the most part. And make sure that you leave out the www. just because that could throw a wrench into it. I made sure to take out the www. from the referrer just to uncomplicate things.
Then there’s just the matter of setting the cookie. Setcookie takes three arguments (there’s more, but I only used three): the name, the value, and the timeout. 0 indicates the cookie will expire when the browser is closed. Then there’s just a redirect to the main page.
Conclusion
There’s your very own statistics suite, complete with anything and everything you could want. Here’s the code in its entirety (plus some extra style).
<?php
if($_GET["action"] == "2"){
$username = $_POST["username"];
$password = $_POST["password"];
$referrer = "http://shadow-fox.net/examples/statistics/";
if($username == "username" && $password == "password" && str_replace("www.", "", $_SERVER['HTTP_REFERER']) == $referrer."statistics.php"){
setcookie("statisticslogged", "yourpassphrase", 0);
header("Location: statistics.php");
}
}
?>
<html>
<head>
<title>Statistics Suite Backend</title>
<style type="text/css">
BODY { padding:1em;
background-color:#000;
font-family:"Trebuchet MS", Verdana, Arial, sans-serif; }
#layout { margin:0 auto;
background-color:#596F80;
padding:.5em;
border:5px solid #6D9B77; }
#navigation a { border:2px solid #CCAE8F;
padding:.3em; }
#navigation a:hover { padding:.5em; }
#navigation li { display:inline; }
td { border:1px solid #CCAE8F;
padding:.3em;
margin:.1em; }
a { color:#CFDCE6; }
h1 { margin:0;
color:#CFDCE6;
padding:0; }
</style>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<h1>Statistics Suite</h1>
<div id="layout">
<ul id="navigation">
<li><a href="statistics.php">Main</a></li>
<li><a href="statistics.php?action=referrer">Referrers</a></li>
<li><a href="statistics.php?action=displayed">Displayed Pages</a></li>
<li><a href="statistics.php?action=browsers">Browsers Used</a></li>
</ul>
<?php
if($_COOKIE["statisticslogged"] == "yourpassphrase"){
$connection = mysql_connect("localhost",
"username",
"password");
mysql_select_db("database", $connection);
switch($_GET["action"]){
default:
$all = mysql_query("SELECT ip FROM statistics WHERE date = '".date("Y-m-d")."'") or die(mysql_error());
$allnum = mysql_num_rows($all);
$unique = mysql_query("SELECT DISTINCT ip FROM statistics WHERE date = '".date("Y-m-d")."'") or die(mysql_error());
$uniquenum = mysql_num_rows($unique);
echo "<h2>Hits For Today: ".date("Y-m-d")."</h2>";
echo "Total Hits Today: $allnum <br />";
echo "Unique Hits Today: $uniquenum";
?>
<br />
<br />
<h2>Date Selection</h2>
<form action="statistics.php?action=select" method="post">
<b>Month (mm):</b>
<input type="text" name="month" />
<br />
<b>Day (dd):</b>
<input type="text" name="day" />
<br />
<b>Year (yyyy):</b>
<input type="text" name="year" />
<br />
<input type="submit" value="Find Date" />
</form>
<?
break;
case "select":
$date = $_POST["year"]."-".$_POST["month"]."-".$_POST["day"];
$all = mysql_query("SELECT ip FROM statistics WHERE date = '".$date."'") or die(mysql_error());
$allnum = mysql_num_rows($all);
$unique = mysql_query("SELECT DISTINCT ip FROM statistics WHERE date = '".$date."'") or die(mysql_error());
$uniquenum = mysql_num_rows($unique);
echo "<h2>Hits For Today: ".$date."</h2>";
echo "Total Hits Today: $allnum <br />";
echo "Unique Hits Today: $uniquenum";
break;
case "referrer":
$select = mysql_query("SELECT DISTINCT referrer FROM statistics") or die(mysql_error());
?>
<table>
<tr>
<td>Referrer</td>
<td>Hits</td>
</tr>
<?
while($r = mysql_fetch_array($select)){
$query = mysql_query("SELECT * FROM statistics WHERE referrer = '".$r[2]."'") or die(mysql_error());
$num = mysql_num_rows($query);
?>
<tr>
<td><?=$r[0]?></td>
<td><?=$num?></td>
</tr>
<?
}
echo "</table>";
break;
case "displayed":
$select = mysql_query("SELECT DISTINCT page FROM statistics") or die(mysql_error());
?>
<table>
<tr>
<td>Page</td>
<td>Hits</td>
</tr>
<?
while($array = mysql_fetch_array($select)){
$query = mysql_query("SELECT page FROM statistics WHERE page = '".$array[0]."'") or die(mysql_error());
$num = mysql_num_rows($query);
?>
<tr>
<td><?=$array[0]?></td>
<td><?=$num?></td>
</tr>
<?
}
echo "</table>";
break;
case "browsers":
$select = mysql_query("SELECT DISTINCT browser FROM statistics") or die(mysql_error());
?>
<table>
<tr>
<td>Page</td>
<td>Hits</td>
</tr>
<?
while($array = mysql_fetch_array($select)){
$query = mysql_query("SELECT browser FROM statistics WHERE browser = '".$array[0]."'") or die(mysql_error());
$num = mysql_num_rows($query);
?>
<tr>
<td><?=$array[0]?></td>
<td><?=$num?></td>
</tr>
<?
}
echo "</table>";
break;
}
mysql_close($connection);
}
else {
?>
<h2>Log in</h2>
<form action="statistics.php?action=2" method="post">
<label for="username"><b>Username:</b></label>
<input type="text" name="username" />
<br />
<label for="password"><b>Password:</b></label>
<input type="password" name="password" />
<br />
<input type="submit" value="Login!" />
</form>
<?
}
?>
</div>
</body>
</html>
That’s a sizeable backend.
Username: username Password: password
The statistics will display for any PHP based example in any of my other tutorials.
Ideas For Expansion
Of course.
- Sanitize database input
- Make a date selector instead of having to insert info manually
- Add the AJAX to make the date selection automatic
Now go and find out more about your visitors.
I was curious, how about if this provides more details such as screen resolution and other stuff?
by Alteredillusionz