Logging Custom Information to a Database

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

 1:  <?php 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:  $mysqli = mysqli_connect("localhost", "joeuser", "somepass", "testDB"); 8: 9:  //create and issue query 10: $sql = "INSERT INTO access_tracker (page_title,user_agent,date_accessed) 11:         VALUES ('$page_title', '$user_agent', now())"; 12: $result = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli)); 13: 14: //close connection to MySQL 15: mysqli_close($mysqli); 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 26.1 and then the content in Listing 26.2.

Listing 26.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 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

 1:   <?php 2:   //connect to server and select database 3:   $mysqli = mysqli_connect("localhost", "joeuser", "somepass", "testDB"); 4: 5:   //issue query and select results for counts 6:   $count_sql = "SELECT count(page_title) AS p_count FROM access_tracker"; 7:   $count_res = mysqli_query($mysqli, $count_sql) or die(mysqli_error($mysqli)); 8: 9:   while ($count_info = mysqli_fetch_array($count_res)) { 10:      $all_count = $count_info['p_count']; 11:  } 12: 13:  //issue query and select results for user agents 14:  $user_agent_sql = "SELECT DISTINCT user_agent, count(user_agent) AS 15:                     ua_count FROM access_tracker GROUP BY user_agent 16:                     ORDER BY ua_count desc"; 17:  $user_agent_res = mysqli_query($mysqli, $user_agent_sql) 18:                    or die(mysqli_error($mysqli)); 19: 20:  //start user agent display block 21:  $user_agent_block = "<ul>"; 22: 23:  //loop through user agent results 24:  while ($row_ua = mysqli_fetch_array($user_agent_res)) { 25:     $user_agent = $row_ua['user_agent']; 26:     $user_agent_count = $row_ua['ua_count']; 27:     $user_agent_block .= " 28:     <li>".$user_agent." 29:        <ul> 30:        <li><em>accesses per browser: ".$user_agent_count."</em> 31:        </ul> 32:     </li>"; 33:  } 34: 35:  //finish up the user agent block 36:  $user_agent_block .= "</ul>"; 37: 38:  //issue query and select results for pages 39:  $page_title_sql = "SELECT DISTINCT page_title, count(page_title) AS 40:                     pt_count FROM access_tracker GROUP BY page_title 41:                     ORDER BY pt_count desc"; 42:  $page_title_res = mysqli_query($mysqli, $page_title_sql) 43:                    or die(mysqli_error($mysqli)); 44: 45:  //start page title display block 46:  $page_title_block = "<ul>"; 47: 48:  //loop through results 49:  while ($row_pt = mysqli_fetch_array($page_title_res)) { 50:     $page_title = $row_pt['page_title']; 51:     $page_count = $row_pt['pt_count']; 52:     $page_title_block .= " 53:     <li>".$page_title." 54:        <ul> 55:        <li><em>accesses per page: ".$page_count."</em> 56:        </ul> 57:     </li>"; 58:  } 59: 60:  //finish up the page title block 61:  $page_title_block .= "</ul>"; 62:  ?> 63:  <html> 64:  <head> 65:  <title>Access Report</title> 66:  </head> 67:  <body> 68:  <h1>Access Report</h1> 69:  <p><strong>Total Accesses Tracked:</strong> <?php echo "$all_count"; ?></p> 70:  <p><strong>Web Browsers Used:</strong> 71:  <?php echo "$user_agent_block"; ?></p> 72:  <p><strong>Individual Pages:</strong> 73:  <?php echo "$page_title_block"; ?></p> 74:  </body> 75:  </html>

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.

Sams Teach Yourself PHP, MySQL And Apache All in One
Sams Teach Yourself PHP, MySQL and Apache All in One (3rd Edition)
ISBN: 0672328739
EAN: 2147483647
Year: 2004
Pages: 327

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net