How to log to a database using the Stanford Web Application Toolkit

From Web Services Wiki

Jump to: navigation, search

Contents

Problem

You want to set up a database log to track user activity, errors, or other data.

Solution

Use the StanfordLogDatabase class to log messages to a database table. Each log entry is automatically populated with the date and time, remote host IP, script name, and query string.

Basic Usage

Connect to the database

Read How to configure and access MySQL using the Stanford Web Application Toolkit for more information about accessing MySQL through the toolkit. Please note that only groups, departments, and services have access to MySQL at this time. Individual user accounts are not supported.

Create a new StanfordLogDatabase

Initialize a new StanfordLogDatabase using the database connection handle and the name of the log table. Call append to write to the log.

// Include StanfordLogDatabase
require_once("stanford.log.database.php");
 
// Create a new StanfordDatabase connection
$db = new StanfordDatabase("username", "password", "database");
 
// Create a new StanfordLogDatabase with the database connection handler and the table name as parameters
// The database connection handler must be of type MySQLi (StanfordDatabase is an extension of MySQLi)
$log = new StanfordLogDatabase($db, "my_log_table");
 
// Write to the log
$log->append("A new message");

To create a new log table if it does not already exist, simply initialize a StanfordLogDatabase with its third parameter set to true. Once the function is called for the first time, the table is created. If the table already exists, an exception is thrown, so you must omit the $create_if_nonexistent parameter on all subsequent calls.

// Create a new StanfordLogDatabase and automatically set up the DB table if it does not exist
// After running this code once, set the optional third parameter to false or simply omit it
$log = new StanfordLogDatabase($db, "my_log_table", $create_if_nonexistent = true);

Sending the log via e-mail

You may configure either of the Stanford logging classes (StanfordLogFile or StanfordLogDatabase) to be sent via e-mail at a specified interval. To find out more, read How to set up an e-mail log using the Stanford Web Application Toolkit.

Truncating the log

To truncate the log to zero length, call the truncate function.

// Truncate the log
$log->truncate();

Adding logging capabilities to StanfordApp

You may attach a StanfordLog object to StanfordApp by calling the function set_logger, as shown below. Then, you may call $app->log() in the same manner that you would call $log->append(). Using StanfordApp to manage logging may be desirable for aesthetic purposes and storage type independence, since it is easy to swap out a different type of logging object if a change is necessary in the future.

// Include StanfordApp and StanfordLogDatabase
include_once("stanford.app.php");
include_once("stanford.log.database.php");
 
// Create a new app
$app = new StanfordApp();
 
// Create database connection
$db = new StanfordDatabase("username", "password", "database");
 
// Create a new log
$log = new StanfordLogDatabase($db, "my_log_table");
 
// Attach logger to app
$app->set_logger($log);
 
// Append a message to the log file
$app->log("a message");

To get the logging object from StanfordApp, call get_logger.

// Get the StanfordLog object
$log = $app->get_logger();

Discussion

What SQL statement can I use to manually create a log table?

If the automatic method of creating a log table (see Basic Usage) does not work due to insufficient permissions other reasons, you may manually create the table using the following statement:

CREATE TABLE my_log_table (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 log_time TIMESTAMP DEFAULT NOW() NOT NULL,
 remote_addr INT UNSIGNED NOT NULL,
 script VARCHAR(500) NOT NULL,
 query_string VARCHAR(1000) NOT NULL,
 message TEXT
);

Structure of the log table

The log table is structured as follows:

Field Type Null Key Default Extra
id int(11) NO PRI auto_increment
log_time timestamp NO CURRENT_TIMESTAMP
remote_addr int(10) unsigned NO
script varchar(500) NO
query_string varchar(1000) NO
message TEXT YES NULL

Each entry stores the current time, the user's IP address, the script name, the query string, and the custom message sent to append.

Storing IP addresses as unsigned integers

The IP address is stored as an unsigned integer for the sake of efficiency (4 bytes for an unsigned int vs 15 bytes for a string). Use the MySQL function INET_NTOA to retrieve IP addresses in their normal dotted decimal string format:

SELECT INET_NTOA(remote_addr) AS ip_address FROM my_log_table;

Though you do not have to manually store IP address data using StanfordLogDatabase, if you should need it, use the reverse function, INET_ATON.

UPDATE my_log_table SET remote_addr = INET_ATON("13.256.49.8") WHERE id = 9;

PHP offers similar functions. See ip2long and long2ip for more information.

Displaying the log in an HTML table

The script below displays the 20 most recent log entries in a simple HTML table. For more information on StanfordDBQuery, read How to retrieve, sort, and display information from a database using the Stanford Web Application Toolkit.

// Include StanfordDBQuery
include_once("stanford.dbquery.php");
 
// Create new database connection
$db = new StanfordDatabase("username", "password", "database");
 
// Create database query
$data = new StanfordDBQuery($db, "log_table_name");
 
// Sort by timestamp, newest first
$data->sort_by("log_time", StanfordData::DESCENDING);
 
// Set remote_addr type to UNSIGNED_INT_IP to convert IPs from integers to dotted decimal format upon retrieval
$data->set_type("remote_addr", StanfordData::UNSIGNED_INT_IP);
 
// Limit to 20
$data->set_limit(20);
 
// Display table
$data->display_as_html_table();

References

Personal tools