Making Usage Statistics in PHP - Storing the Data
Let's start with the database part. We'll need only one table that stores each click (page impression) of each visitor. The name of the table will be "hits". What should we store here?

1. The hostname is very important to identify the client. PHP gives us the IP address that I like to convert to hostname with gethostbyaddr(). I think it's better to see a string.
2. The time of visit can be stored easily by using the NOW() function of MySQL.
3. The visited page on your site should be stored. In most cases, you should store the URL of the visited page and the HTML title in two separated fields, eg. URL and Title fields, so that you'll be able to see your most visited sites simply by looking through the list of their titles.

Now we're going to write the piece of code that will save the hit. It will be called at the beginning of the program. To save the requested URL in the database you can use $_SERVER['PHP_SELF'] that gives you the URL without the domain name or $_SERVER['QUERY_STRING'] that gives you the parameters in the URL after the question mark.

Warning: $_SERVER['PHP_SELF'] doesn't contain the query string, only the path related to the server root and the filename. For instance if you call http://www.mydomain.com/folder1/personal/main.php?id=56 then PHP_SELF is /folder1/personal/main.php.

I prefer $_SERVER['QUERY_STRING'].  This can be especially useful if you use only one PHP to all functions, for example, you call "/index.php?op=forum&topicid=768" if you'd like to show a given forum topic or for showing articles /index.php?op=articles&id=25. Using QUERY_STRING, You will get "op=articles&id=25" that will be enough to identify the requested page.

// showing a topic using given data


define('TITLE', "Adam's forum > Topic: $topicname"); 
include('header.php')
// here comes the main part 



Now the included header.php can save the TITLE constant.
Here's the query to save the data. Put this piece to the beginning of the header.php.


$q

=

'insert into hits (Host, Site, Title, Date) values
  ("'.gethostbyaddr($_SERVER['REMOTE_ADDR']).'",
  "'.addslashes($_SERVER['QUERY_STRING']).'", "'.addslashes(TITLE).'", now())';
mysql_query($q) or print(mysql_error());



Details explained above. Note addslashes() that's necessary when writing strings in a database because of the hacking attempts. This is some kind of encoding method, so you'll need to decode it when you write the string out by the stripslashes() function.

Our next task is to code the site that will show us the saved statistics from the database. I will show two examples. The first one is text-based. Its goal is to allow you to review your visitors and visited sites. It's simple and quite obvious so you can get the data that you need from it.

This page consists of two tables. The first table shows all your visitors, each row represents a user. Data is retrieved from the database, using the MySQL GROUP BY clause. The table has the following columns:

1. Hostname
2. Number of visited pages
3. Clear data from this hostname

The picture above is my solution on my personal homepage. The 'X' signs in the last column are links referring to the code that deletes all visits from the given visitor. The code below shows the first table.


   $q

=

'SELECT Host, COUNT(Site) AS cs FROM hits GROUP BY Host';
   $visitor_r=mysql_query($q) or print(mysql_error());
 
   echo "<h3>Visitors</h3><table width=100% border=1><tr>n".
       "<td><b>Hostname</td>n".
       "<td><b>Visited pages</td>n".
       "<td align=center><b>Clear all from this visitor</td></tr>n";
 
   while ($visitor=mysql_fetch_array($visitor_r))
      echo "<tr><td>".$visitor['Host']."</td>n".
          "<td>".$visitor['cs']."</td>n".
          "<td align=center><a href=?op=stat&delhost=".
               urlencode($visitor['Host']).">X</a></td></tr>";
                    // This link is to delete the visited pages of this user
                    // Note urlencode() which is because the string
                    // that will be queried as an URL
 
   echo "</table>";



1. Visited page
2. Hostname
3. Date

Clicking on the visitor's hostname in the table above clears the data, like the previous query (the links refer to the same piece of code).

In the first column there is the title of the HTML, but it's a link using the saved URL.

And now let's see the code for the second table:


   $q

=

'SELECT Title, Site, Host, Date FROM hits
          ORDER BY Date DESC, Host';
   $page_imp_r=mysql_query($q) or print(mysql_error());
 
   echo "<h3>Page impressions</h3>".
       "<table width=100% border=1><tr>n".
       "<td><b>Visited page</td>n".
       "<td><b>Hostname</td>n".
       "<td><b>Date</td></tr>n";
 
   while ($page_imp=mysql_fetch_array($page_imp_r))
       echo "<tr><td><a href=?".$page_imp['Site'].">".
        $page_imp['Title']."</a></td>n".
      // Link to the page
        "<td><a href=?op=stat&delhost=".$page_imp['Host'].">".
        $page_imp['Host']."</a></td>n".
         "<td>".$page_imp['Date']."</td></tr>";
echo "</table>";


In a lot of cases the text-based statistics with two tables are enough, but figures made from the database can be more useful. What kind of figures can we make? Some examples: the number of the visitors on the days of the week, the page impressions ordered by the hours of the day, and the number of the reads of the pages.

Here we will make a chart to show how many pages the visitors downloaded. First of all, let's do the query:


SELECT Host

, 

Count(ID) as Count_ID FROM hits
         GROUP BY Host
         ORDER BY Count_ID DESC
         LIMIT 0, 10



Use the LIMIT clause on only the first ten rows of the query. We don't want to see thousands of users on a figure, only the Top 10. ORDER BY is necessary to see the "top 10" visitors who read the most from your site.

Having the data, let's start to deal with the image creation library of PHP (GD). Its output is GIF/PNG/JPEG. It uses image resources.  These are PHP variables representing images. Usually, in a program there is only one image resource. You can create an image resource using one of the following functions:

1. ImageCreate (int width, int height) - Creates a blank image resource
2. ImageCreateTrueColor (int width, int height) - Creates a blank true color image resource
3. ImageCreateFromGIF (string filename) - Creates an image resource using a given picture file
4. ImageCreateFromJPEG (string filename) - Creates an image resource using a given picture file
5. ImageCreateFromPNG (string filename) - Creates an image resource using a given picture file

This code can be good for this, but don't forget to call the header function because it's not HTML output!


header

(

'Content-type: image/png');
$image=imagecreate(400,210); 



Then you can start to draw the picture. Here are some of the functions - read them as we will use some to make the Top 10 stats. For more, see the PHP manual.

1. ImageColorAllocate (resource image, int red, int green, int blue) - Prepares an RGB color to use
2. ImageLine (resource image, int x1, int y1, int x2, int y2, int color) - Draws a line
3. ImageRectangle (resource image, int x1, int y1, int x2, int y2, int col) - Draws a rectangle
4. ImagePolygon (resource image, array points, int num_points, int color) - Draws a polygon using an array
5. ImageFilledRectangle (resource image, int x1, int y1, int x2, int y2, int color) - Draws a filled rectangle
6. ImageTTFText (resource image, int size, int angle, int x, int y, int color, string fontfile, string text) - Draws a TrueType text

Below you can read the code. It can be a little confusing, especially the numeric expressions, because you have to think in X and Y coordinates. But try it, you'll get the hang of it!

There is another interesting thing, that I've done it with $visitors array. It's keys are the hostnames of the visitors, values are the number of their visits. To draw them in our figure, I use $pixel_per_visit. That's relative to the Top 1 - the visitor whose column is the highest on the diagram.


imageline

(

$image, 20, 20, 20, 180, $pen);
// The vertical axe
imageline($image, 15, 25, 20, 20, $pen);
// The arrow
imageline($image, 25, 25, 20, 20, $pen);
imageline($image, 20, 180, 380, 180, $pen);
// The horizontal axe
imageline($image, 375, 175, 380, 180, $pen);
imageline($image, 375, 185, 380, 180, $pen);
$visitors=array();
 
while ($stat=mysql_fetch_array($stat_r))
    $visitors[$stat['Host']]=$stat['Count_ID'];
        // Loading up the array...
    $max=current($visitors);
       // How many pages did the Top 1 visit?
    $pixel_per_visit=150/$max;
       // This is because we get the data
       // in number of visits, not in pixels
 
    $i=0;
      // This is for the loop, to know
      // the number of the current visitor


   foreach($visitors as $host => $visits) {
     // now $visits is number of visits
   imagefilledrectangle($image, 35+$i*30, 180-(visits*$pixel_per_visit),
        45+$i*30, 180, $pen);
   imagettftext($image, 8, 0, 40+$i*30, 175-($visits*$pixel_per_visit),
   $pen, "\windows\fonts\arial.ttf", $host);
   imagettftext($image, 15, 0, 32+(($i++)*30), 200,
   $pen,"\windows\fonts\arial.ttf", $visits);
}



When you're ready with drawing the figure you have to send the output to the browser. Here are the functions and the code:

1. ImageGIF (resource image) - Outputs the image in GIF format
2. ImageJPEG (resource image) - Outputs the image in JPEG format
3. ImagePNG (resource image) - Outputs the image in PNG format imagepng($image);

Note that these functions accept an optional second argument, the name of a new file to save the picture there. This can be useful if you'd like to make a picture saving option for your site.
And here's the result:

Put the picture making part of your program to the beginning of your main PHP that produces HTML output! Use the isset() function that makes you able to check whether a variable is set or not.

An example: you have a huge code in an index.php file with an if (isset($gd)) condition at the beginning. The output of the index.php is HTML, if there's no $gd set and if there is, then the output is image. Imagine that you can include pictures with echoing a simple <img src=?gd=> tag. My favourite usage of this is the code below (thanks to Jack Shieh). It shows a thumbnail of the images stored in a MySQL table.


if 

(isset(

$pic_id)) {
   $q='SELECT Picture FROM images WHERE ID='.$pic_id;
   $pic_r=mysql_query($q) or print(mysql_error());
   $pic=mysql_fetch_array($pic_r);
   header("Content-type: image/jpeg");
   $size=80; // new image width
   $src=imagecreatefromstring($pic['Picture']);
   $width=imagesx($src); $height=imagesy($src);
   $aspect_ratio = $height/$width;
   if ($width <= $size) {
      $new_w = $width;
      $new_h = $height;
   } else {
   $new_w = $size;
   $new_h = abs($new_w * $aspect_ratio);
   }
   $img = imagecreatetruecolor($new_w,$new_h);
   imagecopyresized (img,$src,0,0,0,0,$new_w,$new_h,$width,$height);
   imagejpeg($img);
}
$q='SELECT ID FROM images';
$ids_r=mysql_query($q) or print(mysql_error());
echo "<table width=100%><tr>";
while ($ids=mysql_fetch_array($ids_r))
   echo "<td><img src=?pic_id=".$ids['ID']."></td>n";
echo "</tr></table>";



I hope that now you can track your visitors and make graphical statistics easily as well. I wish you the experience of being visited - and I wish you saw your success also in the statistics!