In the second part of this tutorial about creating your own custom web statistics, we analyse the data that is collected each time a page is viewed. We'll be looking at various ways of reading the data we require from the database, and making MySQL work harder by using some of its extra functions to process our data.
We create a function to execute an SQL query, so that we don't repeatedly enter code to open and close the database etc, which makes for shorter code that’s easier to understand and debug. We then create a range of functions that could extract certain statistics from the data stored in the database. We create a set of functions which create the SQL queries dynamically, and can be easily inserted into the framework of the main site pages.
We then construct a page which serves as the index page for the web statistics application, and shows an overall view of every year that has data in the database, showing each year and the number of hits per month.
Finally, we create a page that displays a single year's-worth of data at a time, in a table which is dynamically generated using PHP, and shows more detailed figures for the year.
Please note that this article forms part of a chapter in the DMXzone e-book PHP Web Applications for Dreamweaver: Juicy Solutions for the Busy Developer.