Logging Custom Information to a Database | Sams Teach Yourself PHP, MySQL and Apache All in One (4th Edition)

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 will create 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 25.1 will write some basic information to the access_tracker table.

Listing 25.1. Code Snippet for Access Tracking

  1: <?  2: //set up static variables  3: $page_title = "sample page A";  4: $user_agent = getenv("HTTP_USER_AGENT");  5:  6: //connect to server and select database  7: $conn = mysql_connect("localhost", "joeuser", "somepass") or die(mysql_error());  8: $db = mysql_select_db("testDB", $conn) or die(mysql_error());  9: 10: //create and issue query 11: $sql = "insert into access_tracker values 12:    ('', '$page_title', '$user_agent', now())"; 13: mysql_query($sql,$conn); 14: ?>

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 25.1 and then the content in Listing 25.2.

Listing 25.2. Sample HTML Page

 1: <HTML> 2: <HEAD> 3: <TITLE>Sample Page A</TITLE> 4: </HEAD> 5: <BODY> 6: <h1>Sample Page A</h1> 7: <P>Blah blah blah.</p> 8: </BODY> 9: </HTML>

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 25.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 25.3. Creating an Access Report

  1: <?php  2: //connect to server and select database  3: $conn = mysql_connect("localhost", "joeuser", "somepass")  4:     or die(mysql_error());  5: $db = mysql_select_db("testDB", $conn) or die(mysql_error());  6:  7: //issue query and select results for counts  8: $count_sql = "select count(page_title) from access_tracker ";  9: $count_res = mysql_query($count_sql, $conn) or die(mysql_error()); 10: $all_count = mysql_result($count_res, 0, "count(page_title)"); 11: 12: //issue query and select results for user agents 13: $user_agent_sql = "select distinct user_agent, count(user_agent) as count 14:     from access_tracker group by user_agent order by count desc"; 15: $user_agent_res = mysql_query($user_agent_sql, $conn) 16:     or die(mysql_error()); 17: //start user agent display block 18: $user_agent_block = "<ul>"; 19: 20: //loop through user agent results 21: while ($row_ua = mysql_fetch_array($user_agent_res)) { 22:    $user_agent = $row_ua['user_agent']; 23:    $user_agent_count = $row_ua['count']; 24:    $user_agent_block .= " 25:    <li>$user_agent 26:         <ul> 27:         <li><em>accesses per browser: $user_agent_count</em> 28:         </ul>"; 29: } 30: 31: //finish up the user agent block 32: $user_agent_block .= "</ul>"; 33: 34: //issue query and select results for pages 35: $page_title_sql = "select distinct page_title, count(page_title) as count 36:     from access_tracker group by page_title order by count desc"; 37: $page_title_res = mysql_query($page_title_sql, $conn) 38:     or die(mysql_error()); 39: //start page title display block 40: $page_title_block = "<ul>"; 41: 42: //loop through results 43: while ($row_pt = mysql_fetch_array($page_title_res)) { 44:    $page_title = $row_pt['page_title']; 46:    $page_count = $row_pt['count']; 47:    $page_title_block .= " 48:    <li>$page_title 49:        <ul> 50:        <li><em>accesses per page: $page_count</em> 51:        </ul>"; 52: } 53: 54: //finish up the page title block 55: $page_title_block .= "</ul>"; 56: 57:?> 58: <HTML> 59: <HEAD> 60: <TITLE>Access Report</TITLE> 61: </HEAD> 62: <BODY> 63: <h1>Access Report</h1> 64: <P><strong>Total Accesses Tracked:</strong> <? echo "$all_count"; ?></p> 65: <P><strong>Web Browsers Used:</strong> 66: <?php print "$user_agent_block"; ?> 67: <P><strong>Individual Pages:</strong> 68: <?php print "$page_title_block"; ?> 69: </BODY> 70: </HTML>

Lines 35 connect to the database so that you can issue the queries against the access_tracker table. Lines 810 issue the query to select the count of all pages, and lines 1315 count the user agent accesses. Line 18 starts an unordered list block for the results of the user agent query, while lines 2129 loop through the results and create the list, which is closed in line 32.

Lines 3537 create and issue the query to count the individual pages. Line 40 starts an unordered list block for the results of this query, and lines 4352 loop through the results and create the list of accessed pages, which is closed in line 55.

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 25.1your page names, counts, and browsers will be different, but you get the idea.

Figure 25.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.