Creating your own logging tables in MySQL, matched up with snippets of PHP code, can help you to capture access-related information for specific pages of your site. Using this information, you can create customized reports. This method can be much less cumbersome than wading through Apache log files, especially when you are just searching for a subset of access information. The following sections outline a simple version of this process.
Creating the Database Table
The first step in your custom logging method is to create the database table. The following table creation command creates a table called access_tracker in your MySQL database, with fields for an ID, page title, user agent, and date of access:
mysql> CREATE TABLE access_tracker ( -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -> page_title VARCHAR(50), -> user_agent TEXT, -> date_accessed DATE -> );
Next, you'll create the code snippet that will write to this table.
Creating the PHP Code Snippet
As you may have gathered already, code snippet essentially means a little bit of code. In other words, something that doesn't qualify as a long script but just serves a simple purpose. In this case, the code snippet in Listing 26.1 writes some basic information to the access_tracker table.
Listing 26.1. Code Snippet for Access Tracking
What you'll do with this snippet is simple: Place it at the beginning of every page you want to track. For each page, change the value of $page_title in the snippet to represent the actual title of the page.
Now create a sample script called sample1.php, containing the contents of Listing 26.1 and then the content in Listing 26.2.
Listing 26.2. Sample HTML Page
Create a few copies of this file, with different filenames and values for $page_title. Then access these different pages with your web browser to fill up your logging table.
Creating Sample Reports
When you have the data in your access_tracker table, you can create a simple report screen to disseminate this information. The code in Listing 26.3 creates a report that issues queries to count total results as well as the breakdown of browsers in use. Each of these blocks will be explained after the code listing.
Listing 26.3. Creating an Access Report
Line 3 connects to the database so that you can issue the queries against the access_tracker table. Lines 67 issue the query to select the count of all pages, and lines 1418 count the user agent accesses. Line 21 starts an unordered list block for the results of the user agent query, while lines 2433 loop through the results and create the list, which is closed in line 36.
Lines 3943 create and issue the query to count the individual pages. Line 46 starts an unordered list block for the results of this query, and lines 4958 loop through the results and create the list of accessed pages, which is closed in line 61.
Put these lines into a text file called accessreport.php, and place this file in your web server document root. When you access this report, you will see something like Figure 26.1your page names, counts, and browsers will be different, but you get the idea.
Figure 26.1. Custom access report for tracked pages.
This sort of tracking is a lot easier than wading through Apache access logs, but I wouldn't recommend completely replacing your access logs with a database-driven system. That's a bit too much database-connection overhead, even if MySQL is particularly nice on your system. Instead, target your page tracking to something particularly important.