search engine by freefind . . . .

Generating Web Statistics

In this exercise you will create a simple database to hold statistical data, and a PHP script to gather the data. The data will include the visitor's IP address, browser and operating system, and the date on which they visited your site.

Creating the database

  1. Connect to the MySQL server by opening a command window and navigating to the directory where MySQL stores its executable files (on my computer, this would be X:\xampp\mysql\bin) and enter the command:

    mysql -u root

  2. Enter the command:

    create database webstats;

  3. Enter the command:

    use webstats;

  4. Create the "stats" table using the following command:

    CREATE TABLE stats
    (
      id INT NOT NULL auto_increment,
      browser VARCHAR(255) NOT NULL,
      ip VARCHAR(15) NOT NULL,
      ondate DATE NOT NULL,
      PRIMARY KEY (id)
    );

  5. Exit the MySQL command shell by typing:

    quit

Setting up the target web page

We will use only the home page of the website ("index.php") to gather statistics (it is possible to set up multiple pages to track user activity, but this is beyond the scope of this exercise). The following simple web page is provided as an example, but you can easily add the required PHP code to an existing web page, if required. The page will be located in the "htdocs" directory in your "xampp" folder (note that in later versions of PHP the register_globals directive is set to OFF by default, so you may need to use the so-called "superglobal" variables $_SERVER['HTTP_USER_AGENT'] and $_SERVER['REMOTE_ADDR'] instead of $HTTP_USER_AGENT and $REMOTE_ADDR respectively).

<?php
  $browser = $HTTP_USER_AGENT;
  $ip = $REMOTE_ADDR;
  $db = mysql_connect("localhost", "root");
  mysql_select_db("webstats", $db);
  $sql = "INSERT INTO stats(ip,browser,ondate)
      VALUES('$ip', '$browser', now())";
  $results = mysql_query($sql);
?>

<html>

  <head>
    <title>Hello World!</title>
  </head>

  <body>
    <h1>Hello World!</h1>
    <p>This is the home page of the "Hello World!" web site.</p>
  </body>

</html>

Run the XAMPP server and type the URL of the home page ("http://localhost/index.php") into the browser's address bar, then refresh the page a few times. To check that the script is working, connect to the MySQL server once more and enter the following MySQL commands to view the content of the "stats" table (note that the output is limited to five records):

use webstats;
SELECT * FROM stats LIMIT 5;

Setting up the web stats script

The script that displays the basic web statistics is shown below. Enter the code into a text file and save the files as "stats.php" in your "htdocs" directory, then run the XAMPP server and type the URL ("http://localhost/stats.php") into the browser's address bar to see the result. A screen shot follows the code, and shows typical output for the script.

<?php

$now = date("d-m-Y");
$dbsql = "stats";
$db = mysql_connect( "localhost", "root");
mysql_select_db( "webstats", $db);

echo "<center><h1>Web Statistics</h1></center>";

echo "<center><table border=1 width=80%><tr><td colspan=3><b>Web statistics from:</b> ";

$sql = "SELECT ondate FROM $dbsql ORDER BY id LIMIT 1";
$results = mysql_query($sql);
while($myrow = mysql_fetch_array($results))
{
  $temp_date = $myrow["ondate"];
  $date = substr($temp_date, 8, 4)."-".substr($temp_date, 5, 2)."-".substr($temp_date, 0, 4);
  echo $date;
}
echo " <b>To:</b> $now</td></tr>";

$sql = "SELECT COUNT(*) AS Count FROM $dbsql" ;
$results = mysql_query ($sql);
while ($myrow = mysql_fetch_array($results))
{
  $overall_total = $myrow ["Count"];
}
echo "<tr><td colspan=3><b>Total number of hits: </b>$overall_total</td></tr>";

echo "<tr><td><b>Date</b></td><td><b>IP Address</b></td><td><b>Browser and Operating
system</b></td></tr>";

$sql = "SELECT * FROM $dbsql";
$results = mysql_query($sql);
while ($myrow = mysql_fetch_array($results))
{
  $ip = $myrow["ip"];
  $browser = $myrow["browser"];
  $ondate = $myrow["ondate"];
  echo "<tr><td>$ondate</td><td>$ip</td><td>$browser</td></tr>";
}

$sql = "SELECT COUNT(*) AS Count FROM $dbsql" ;
$results = mysql_query ($sql);
while ($myrow = mysql_fetch_array($results))
{
  $overall_total = $myrow ["Count"];
}

echo "</table></center>";
?>


Typical output from stats.php

Typical output from stats.php