Border States Consulting, LLC

PHP web app event logger

This is a simple event logger that can be attached to pretty much any php/mysql web application. I'm going to split this topic into two separate articles - the first will show the code for writing the events to the database, and the second will show the code for displaying the events in a sortable table.

This is strictly vanilla PHP, using mysqli (not PDO), and not using any frameworks. There are probably a hundred other ways to do this, but this is one way.

To begin we need to create the table in the database that will hold the events. Below are the sql commands necessary (assuming you already have a database created for your application):

DROP TABLE IF EXISTS `bsc_event_log`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bsc_event_log` (
`evnt_id` int unsigned NOT NULL AUTO_INCREMENT,
`evnt_type` int unsigned NOT NULL DEFAULT 0,
`evnt_comment` varchar(256) NOT NULL DEFAULT '',
`evnt_domain` varchar(64) NOT NULL DEFAULT '',
`evnt_timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`evnt_id`),
KEY `idx_evnttype` (`evnt_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

And next we need a class that will write the events to that table. You will notice that we pass the database connection to the constructor. We will create the database connection once at the beginning of our script that is using this Event class, and then create just one object of type Event, passing that db connection to the constructor, and then we'll use that Event object throughout the script.

In my case, I have a wrapper class that I use to make the database connection and give me class methods similar to the mysqli commands. If you prefer to use PDO, you should be able to slightly modify this and make that work too. Obviously, you would adjust the items passed to the mysqli() call in the Database.php file to match your settings.

Database.php:

class Database
{
private static $instance = NULL;
private $conID;

public function __construct() {
$this->conID = new mysqli("server","username","password","database");
if($this->conID->connect_error) {
trigger_error('Failed to connect: '.$this->conID->connect_error, E_USER_ERROR);
die('Main Database Connection Failed');
}
}
public static function getInstance() {
if(self::$instance === NULL){
self::$instance = new Database();
}
return self::$instance;
}
public function getConnection(){
return $this->conID;
}
}

DBMysqli.php:

require_once('Database.php');
class DBMysqli
{
private $conID;
private $insertID;
private $queryResult;
private $statement;
private $statements;
private $types;
private $prepared;

public function __construct(){
$db = Database::getInstance();
$this->conID = $db->getConnection();
$this->queryResult = NULL;
$this->prepared = FALSE;
$this->statements = [];
$this->types = [];
}
public function query($sql, array $vars = [], $types = ""){
if($sql == ""){
die(" ERROR: Cannot execute empty query.");
}
$this->queryResult = NULL;
if(!empty($vars)){
$this->prepared = TRUE;
$ret = $this->preparedQ($sql, $vars, $types);
if(!$ret){
die(" Eror number: ".$this->conID->errno." Error detail: ".$this->conID->error." ".$sql." ".($this->prepared ? "TRUE" : "FALSE"));
}
} else {
$this->prepared = FALSE;
$this->queryResult = $this->conID->query($sql);
$this->insertID = $this->conID->insert_id;
if(!$this->queryResult){
die(" Eror number: ".$this->conID->errno." Error detail: ".$this->conID->error." ".$sql." ".($this->prepared ? "TRUE" : "FALSE"));
}
}
return $this->queryResult;
}
private function preparedQ($sql, array $vars, $types = NULL){
if(!$types){
$types = str_repeat('s', count($vars));
}
if(count($vars) !== strlen($types)){
die(" Error: invalid function variables in prepared statement");
}
$this->statement = $this->conID->prepare($sql);
if(!$this->statement){
return FALSE;
}
$this->statement->bind_param($types, ...$vars);
if(!$this->statement->execute()){
return FALSE;
}
$this->insertID = $this->statement->insert_id;
$this->queryResult = $this->statement->get_result();
$this->statement->close();
return TRUE;
}
public function prepareStmt($qry, $types){
$index = 0;
if(!empty($this->statements)){
$index = count($this->statements);
}
$this->statements[$index] = $this->conID->prepare($qry);
if(!$this->statements[$index]){
return FALSE;
}
$this->types[$index] = $types;
return $index;
}
public function executeStmt($index, array $vars){
$this->statements[$index]->bind_param($this->types[$index], ...$vars);
if(!$this->statements[$index]->execute()){
return FALSE;
}
$this->insertID = $this->statements[$index]->insert_id;
$this->queryResult = $this->statements[$index]->get_result();
return TRUE;
}
public function closeStmt($index){
$this->statements[$index]->close();
}
public function get_insID(){
return $this->insertID;
}
public function nextRow(){
return $this->queryResult ? $this->queryResult->fetch_array(MYSQLI_BOTH) : FALSE;
}
public function getNumRows(){
return $this->queryResult ? $this->queryResult->num_rows : 0;
}
}

And now, EventLogger.php:

<?php

class EventLogger
{
private $type;
private $comments;
private $domain;
private $db;

public function __construct($connection, $int $type, $cm = "", $dom = "") {
$this->type = $type;
$this->comments = filter_var($cm, FILTER_SANITIZE_STRING);
$this->domain = filter_var($dom, FILTER_SANITIZE_STRING);
$this->db = $connection;
}

public function set_event(int $type, $cm = "", $dom = ""){
$this->type = $type;
$this->comments = filter_var($cm, FILTER_SANITIZE_STRING);
$this->domain = filter_var($dom, FILTER_SANITIZE_STRING);
}

public function log(){
$qry = "INSERT INTO bsc_event_log (evnt_type, evnt_comment, evnt_domain) VALUES (?,?,?)";
$vars = [$this->type,$this->comments,$this->domain];
$this->db->query($qry, $vars, "iss");
}

public function set_type(int $t){
$this->type = $t;
}

public function set_comments($str){
$this->comments = filter_var($str, FILTER_SANITIZE_STRING);
}

public function set_domain($dom){
$this->domain = filter_var($dom, FILTER_SANITIZE_STRING);
}
}

And here is how you would use this. Somewhere near the top of the script you need:

require_once('DBMysqli.php');
require_once('EventLogger.php');
$db = new DBMysqli();
$events = new EventLogger($db, 0);

And then everywhere you want to throw an event into the database, you would:

$events->set_event(1, "The comment/description of the event", "some.domain.com");
$events->log();

...where the 1 in this example is a number that corresponds to the type of event. You can have a table of event types and descriptions specific to your application, and then use that type column in the database table when displaying the events to sort or filter on type. The domain argument is there so you can have this database catching events for multiple sites/domains, and this allows you to filter/sort on that column as well. Typically, I would already have a $domain variable declared somewhere in the app, and so popping it into the event arguments is easy enough.

That wraps up writing events into the database. The next article will show the code for displaying that table in some meaningful ways.