Generating Web Page Statistics using PHP

Intended Audience

This tutorial is intended for the PHP programmer interested in gathering and analyzing Website statistics in a presentable format using mySQL and PHP.

You will need a moderate understanding of PHP and a limited knowledge of mySQL, including how to create a mySQL table using the mySQL client.

Readers interested in learning more about mySQL and PHP before reading this tutorial are encouraged to do so by refering to:

• Zend.com's PHP manual
• MySQL

Overview

When stepping foot onto the World Wide Web, the first goal is usually to setup a Web page. The extent and complexity of that page will usually depend on what needs are trying to be met, as well as what services need to be provided. The services being provided could be as complex as a comprehensive e-commerce site or as simple as providing pictures of your child's birthday party. Mirroring the importance of presenting your information on the Web is collecting information from the people who are seeing your information, in order to justify and analyze its effectiveness. This means that you will be able to identify how many people are visiting your site as well as important information that can affect the way you design a page (i.e. their browser preference and their operating system). This can be especially helpful when optimizing a page for a specific browser type.

Raw data can be gathered dynamically from a Web page and stored in a database for study at a later point in time. From this data, statistics can be generated in real time, and updated for every visitor - not just compiled at the end of a month or the end of a year.

Furthermore, statistics can be used to justify additional funding. They can show, for example, traffic improvement or page hit ratios.

This tutorial provides instruction for developing a statistics gathering and analyzing system using PHP. Application scripts are based on a system running PHP 4.0 or higher with some sort of database to store our data (we will be using mySQL).

The statistics script will enable you to:

• Gather the IP address, Date, Browser, OS Platform from each Web visitor
• Distinguish Onsite visitors from Offsite visitors
• Calculate daily total hits and daily distinct hits (those that come from a unique IP address)
• Generate gif images using PHP to dynamically present your data

To achieve these features, this tutorial will demonstrate that PHP is capable of:

• Interacting with a database to analyze numerical data
• Generate image files dynamically
• Collecting useful information from a user without requiring a form submission

Hopefully by finding your way through this tutorial, you will obtain a feel for how useful mySQL can be in analyzing data. While PHP can be used to achieve the same effect, it does require quite a bit of substantial code and processor load. Nonetheless, PHP is essential in the proper display of that information and can be used to break down simple numbers provided from the database into concrete statistical information. With the continued growth of a dynamic Web society, it is quite useful to have a statistical application on your Web site to properly evaluate Web traffic.

Learning Objectives

In this tutorial, you will learn how to:

• Collect information from a user by "browser sniffing".
• Use the GD library to create images through PHP's image creation functions.
• Create a stastical gathering and analyzing system complete with dynamically generated numbers and graphics.

Definitions

• GD Library: A library that provides a standardized way to generate images such as JPEGs, GIFs, and PNG formats.
• Browser Sniffing: A method of gathering information from a client's browser, without having a user submit them via a form.
• Target Web page: The Web page from which data will be collected.
• Primary Key: The field in a database that is usually searched and is usually unique.
• Graphic Generation page: The Web page used to create the Statistics bar graphs.

Background Information

This tutorial relies on two software packages other than PHP. GD will be used to generate the graphics needed for the script. This is done so that the script in this tutorial can generate the graphics internally - aiding in portability. For data storage, mySQL will be used as the database.

In the example below, statistics discussed in this tutorial are presented in their graphical output form:

Prerequisites

As a prerequisite for creating a statistics system, it is recommended that you:

• Define the database: From which you will store and manage your statistics information. This tutorial utilizes mySQL as its DBMS of choice.
• Define your information requirements: namely, statistics needed, the database definition, and the mySQL statement.Create a database and format the accompanying table to store our collected data.
• Configure the Target Web page: Insert php code into the page from which you will be gathering statistics (collect.php).
• Create the Graphic Generation page: Create the gd.php in order to generate your statistics bar graphics.

Define The Database

Naturally, when structuring the database, the recorded (actual) data is driven by a unique ID. The question is what data is really necessary and in what form should it take in the table?

For example, what relevant data can be collected from the Web page visitor? Within this tutorial, we have decided to include the IP address, the browser, the OS platform and the date on which they have visited, as shown below.

The first field in our table structure shown above is the ID field. This field serves as our visual counter in addition to being a reference that identifies a record (row) in our database. This ID field is designated as the PRIMARY KEY in addition to an AUTO_INCREMENT field, meaning it automatically adds a number to the row each time data is inserted. This is accomplished by inserting the following code into our overall table definition:

id int DEFAULT '0' NOT NULL auto_increment

Information Requirements

We can now begin to discuss the data that we will collect from our users. This data will tell us the browser they are using, the date that they visited, the operating system and IP address of the computer they are accessing the website from. This means that we will need several fields in our database to accommodate this influx of data, which can be seen as we setup our database in the following steps.

Statistics Needed

The statistics that are needed and will be collected into our database are browser information, ip address information, date visited and their operating system. We can put both the browser information and the operating system information into the same database field since they are retrieved by the same variable, however the received field and the ip address field will require their own, as can be seen below.

The Database Definition

Displayed below is the mySQL table definition for the statistics.

Statistic Table Definitions

Note: When defining your table ensure that the fields ID, BROWSER, IP, and RECEIVED are set with the NOT NULL Property (or similar).

The mySQL Statement

The following CREATE TABLE statement may be used create the above table:

Configuring the Target Web page

Now that we've defined our database parameters and setup our table, we are ready to begin accepting data. The next step is to identify which page we would like to collect data from and modify it to connect to the database that we just setup. Remember, if you want to collect data on multiple pages, this tutorial recommends that you setup multiple tables to do so - otherwise you won't be able to tell apart the separate statistics.

You must identify a Target Web page for gathering statistics. Generally, this is the Home page (i.e. http://www.foo.com/index.php) and is the most heavily visited page on the site. However, you can track multiple pages or any page on the site that requires traffic analysis.

Place your finished Web page in its directory and add the following code above the html:

Code Flow

• Define the browser variable to collect information from HTTP_USER_AGENT.
• Define the ip variable to collect information from REMOTE_ADDR.
• Connect to the database.
• Insert the data into your database via an SQL query.
• Run the query.

Data Validation

Once you begin collecting statistics, make sure that mySQL is collecting data (the collection of data will begin once the code seen below is added to your collect.php file). Be sure that all fields are being populated with data. At the mySQL command line type:

SELECT * FROM tablename LIMIT 5;

This will give you a nice, short slice of the database information you've been collecting. It should look something like this:

mysql> SELECT * FROM stats LIMIT 5;

From the above example, browser information (MSIE x.x - Internet Explorer), Platform information (Macintosh vs. Windows), the IP address and the date (in YYYY/MM/DD format).

Create the Graphic Generation page

In order to avoid reliance on a pre-generated gif, it is necessary to dynamically generate graphic files through PHP. This aids in portability and accuracy of presentation. If we use a pre-generated graphics file, the script becomes reliant on modifying the pixel height of that graphic based on a percentage of visitors to the site. This becomes particularly cumbersome as scaling is incorporated to maintain a pre-defined range of sizes that are acceptable for the graph.

This code, appended to a separate called file, is automatically activated when the page is loaded. It is called via an IMG SRC tag (see below). The tag refers to our gd.php page and sends two variables, $bluehg and $redhg, in order to allow us to define the height of the bars within our graphic. These variables, $bluehg and $redhg, define the (y) variable for height within the ImageFilledRectangle() function. They've been designated "hg" for height and "blue/red" for their respective color codes.

Be sure to send the variables to the gd.php file, otherwise the graphic generated will not have the proper parameters.

Code Flow

• Send the image/jpeg header to identify the file as a graphic.
• Create an image.
• Set color variables.
• Fill the "mother" rectangle with a designated color.
• Create "daughter" rectangles within the "mother" rectangle with designated colors.
• While creating the "daughter" rectangles, pay attention to the $bluehg and $redhg variables, they will become the verticle (y) values for your graphic.

How The Script Works

You've embedded the aforementioned code into your Target Web page, you've entered into your database some interesting information and you've created a dynamically generated jpeg image in gd.php. Now let's bring our prerequisites together and crunch those numbers down into a presentable format so that you can demonstrate your success (such as a favorable traffic experience).

The script presented in this tutorial is a self-contained application. However, it must be slightly modified depending on your database naming system and your expected amount of traffic. The main script is contained within the stats.php file, which interacts with the other components of our system (gd.php, collect.php and the mySQL database) as shown below:

gd.php: The graphic generating PHP script. collect.php: The data collection PHP script. stats.php: The data analyzing PHP script.

Script Overview

Now that we have the layout designed and have validated the data being accumulated, the rest is a matter of extracting and presenting this data. The basic code-flow of the stats.php script is outlined below:

Open the stats.php file, Load prepended script file.

• Determine Total: The total number of distinct hits to our page.
• Analyze the Data: By modifying the "engine" of our stats.php script.
• Create Percentage data: From the analysis.
• Generate the graphics: That will illustrate the statistics, by defining a function to call gd.php.
• Calculate date-based data: Convert numbers into data broken down by month, day and year.
• Compare Total Hits with Distinct Hits: Retrieve total overall hit counts.

Make static variable modifications ($dbsql).

Reload stats.php and view the information.

Determine Total Distinct Hits

The code found in this section enables us to obtain our primary number used in the analysis – total distinct (non-repetitive) hits. This number enables us to calculate other information based on mathematical subtractions.

For example, if we know the grand total number of distinct hits and we know the total number of distinct hits coming from a Windows Platform, we can subtract the windows hits from the grand total to get the number for "Other Platform" hits.

Note: For purposes of this tutorial, we will designate the "Other Platform" hits to be primarily Macintosh hits.

We can use the $total variable for most of our analysis. Some of our calculations later on will involve average hits per time period.

Code Flow

• Insert the Query to ask for distinct information only (non-repetative).
• Set the $total variable equal to the number of rows returned.
• Set the $ip variable for later reference.

Analyze the Data

The code found in this section is the main "engine" of our stats.php script. The queries listed below need to be customized based on your information gathering needs.

Having inserted the data from our Target Web page into the database and constructed our Graphic Generation page, our task now is to study how we can use the PHP/mySQL interface to analyze some of that data.

In the Script Flow presented below, the $dbsql variable is defined at a later step. For now, we rely on the fact that it will be modified to reflect the appropriate table within your database.

Also, the first SQL query can be modified to accommodate one or two separate IP subnets. For example, where I work, there are several IP subnets assigned to various campus locations, but over all, there are only two variations at the second octet level. While this does leave some room for error, in our case it works well. For a limited subnet in the fourth octet, the local IP address can be clearly specified.

Note: If we were more concerned about platform or browser considerations, they could be added and extracted by using various methods. This is a topic that I'd like to address in future tutorials.

The script features three analytical SQL queries.

• First Query: Select the number of records that are both distinct IP addresses (not repeat hits/visitors) and that are outside of our local network addresses.
• Second Query: Select the number of records that are identified as Internet Explorer.
• Third Query: Select the number of records that are identified as Windows based.

All three of these queries function in a limited fashion with regards to discrimination. For this tutorial, we will use the examples to simply discriminate between Windows and Other (designated as Mac); Internet Explorer and Other (designated as Netscape). In future tutorials, multi-platform and multi-browser specific information will be discussed.

Note: By making the assumption that having been given only two variables, the second variable is derived by subtracting the first variable from the 'total' variable. The 'total' variable retrieval is described further on in the tutorial.

Code Flow

• Define Local addresses and insert one (or two) into the first query.
• Populate your variables with information from the query.
• Deploy the remaining code into your script.

Create Percentage Data

The code introduced in this section provides us with percentage values from the numbers retrieved in the above "Analysis" section. This is important when implementing the graphic() function that we will design below. It provides the percent height of each graphic bar in comparison to its competitive graphic bar.

Code Flow

• Define variables for each section of data (for example, Mac values, Windows values).
• Set the defined variables equal to the retrieved value divided by the total values of the set.

Generate the graphics

The code found in this section is the method in which we calculate variables passed to gd.php in order to display the proper graphic based on our statistical data. It involves defining a custom made function for this purpose and then calling the gd.php via an IMG SRC tag. We will also demonstrate how the graphic function is populated with variable data to then call the page.

Code Flow

• Create the graphic() function and the $blueval and $redval variables.
• Define your percentage values and round them off so they are in presentable format – these will be displayed next to the graphics for illustration.
• Define your blue and red variables by multiplying their percentage values by a 200 maximum pixel parameter.
• Insert the IMG SRC tag to call our gd.php script and close the function.
• Call the function within your HTML and insert the appropriate competitive data set (i.e. graphic( $onsiteval, $offsiteval) for the Onsite and Offsite graphic).

Calculate Date-based Data

Finally, what every good analysis needs is a breakdown by time. This gives us an accurate way of determining an overall traffic level to the site by monthly, yearly and daily information.

In a later tutorial, we will discuss how to incorporate a more sophisticated reporting system to enable the display of more information by specific weeks and months. For our purposes in this tutorial, however, we break down our total numbers by average monthly hits and average daily hits. This alone should give an adequate feel for the amount of traffic tht our Website is experiencing. Nonetheless, the numbers will not effectively display a sudden increase of traffic for one particular period of time, unless the increase is quite substantial.

Code Flow

• Convert our dates into numerical data using the TO_DAYS() function.
• Obtain the difference in days by subtracting our maximum value (presumably today) from our minimum value (the first date recorded) in the database.
• Obtain our average Daily Hit count by dividing the $total variable we retrieved above by our $avgday value, round() the value.
• Divide this number by 24 (the number of hours in a day) for our average hourly hit value, again round() the value.

Compare Total Hits with Distinct Hits

The code found in this section mimics the previous section almost identically. The only difference is the addition of a Total number of hits (not just distinct, unique hits) and then processing their average values in the same manner. The retrieval of the total hits data is done by sending a count function through our mySQL interface and then storing that number in a variable.

Code Flow

• Submit the COUNT(*) function to the mySQL database.
• Populate your variables with information from the database.
• Obtain the average number of days that have passed by subtracting our two dates from each other and dividing by the total number of seconds found in one year.
• Obtain our average Daily Hit count by dividing the $total variable we retrieved above by our $avgday value, round() the value.
• Divide this number by 24 (the number of hours in a day) for our average hourly hit value, again round() the value.

The Script

The code listed below is divided into the three complete scripts by comments. The first is collect.php, the second is gd.php and the third is stats.php. All of the portions that needed to be customized have been outlined above.

Note: The PHP elements are found within the<?php and ?> marks. The code contains comments preceded by // or /* and*/marks.

The code below represents collect.php

The code below represents gd.php

The code below represents stats.php

About The Author

Patrick Delin is a System/Database/Web Administrator with over 8 years of development experience and 2 years of PHP experience. He has taken a lead in Web development at the University of Nebraska - Lincoln and is considered "an Army of One". He is also an active writer for Web content organizations such as Zend. Patrick has worked with many high profile projects in Nebraska and hopes to continue collaborations with Zend.com.

He may be reached by e-mail: mailto:pdelin2@unl.edu