Logging Custom Information to a Database | Sams Teach Yourself PHP, MySQL and Apache in 24 Hours

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.

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:

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

Listing 17.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: $date_accessed = date("Y-m-d");   6:    7: //connect to server and select database   8: $conn = mysql_connect("localhost", "joeuser", "somepass")   9:     or die(mysql_error());  10: $db = mysql_select_db("testDB", $conn) or die(mysql_error());  11:   12: //create and issue query  13: $sql = "insert into access_tracker values  14:    ('', '$page_title', '$user_agent', '$date_accessed')";  15: mysql_query($sql,$conn);  16: ?>

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 17.1 and then the content in Listing 17.2.

Listing 17.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 17.3 creates a report that issues queries to count total results as well as the breakdown of browsers in use.

Listing 17.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 3 5 connect to the database so that you can issue the queries against the access_tracker table. Lines 8 10 issue the query to select the count of all pages, and lines 13 15 count the user agent accesses. Line 18 starts an unordered list block for the results of the user agent query, while lines 21 29 loop through the results and create the list, which is closed in line 32.

Lines 35 37 create and issue the query to count the individual pages. Line 40 starts an unordered list block for the results of this query, while lines 43 52 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 17.1 your page names, counts, and browsers will be different, but you get the idea.

Figure 17.1. Custom access report for tracked pages.

graphics/17fig01.gif

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.