It always bugs me how the audit record is handled. It is ineffeicient and it does not show the lookup value of the key field on another table. Therefore this is some attempt to make the audit record better. Maybe developer can also implement this in the future. There are some caveat of the this code. It is just assuming that the there is one primary key for each table. Also it is following a naming convention of the table name, primary key and lookup value as: ‘table’,’table_id’,’table_name’. This is so that in order to find the lookup value, the code looks for the primary key and assumes the column name follows the naming convention. Create the two tables and paste the code into the before audit log event.
There are two tables that you need to add to the database and also the code. Please let me know if there are anything else to improve.
CREATE TABLE IF NOT EXISTS audit_log (
audit_log_id INT NOT NULL AUTO_INCREMENT,
date_time DATETIME NULL,
ip VARCHAR(45) NULL,
user VARCHAR(45) NULL,
table VARCHAR(300) NULL,
action VARCHAR(45) NULL,
description LONGTEXT NULL,
PRIMARY KEY (audit_log_id))
CREATE TABLE IF NOT EXISTS audit_log_value (
audit_log_value_id INT NOT NULL AUTO_INCREMENT,
audit_log_id INT NULL,
column_name VARCHAR(300) NULL,
old_value LONGTEXT NULL,
new_value LONGTEXT NULL,
description LONGTEXT NULL,
PRIMARY KEY (audit_log_value_id))
function generateAuditInsertSQL(array $oldvalues, array $newvalues, int $audit_log_id): string
{
// Helper: turn a PHP value into an SQL literal
$sqlLiteral = function ($v): string {
if ($v === null) return 'NULL'; // SQL NULL (no quotes)
if ($v === '') return "''"; // empty string
return "'" . addslashes((string)$v) . "'";
};
$allKeys = array_unique(array_merge(array_keys($oldvalues), array_keys($newvalues)));
$values = [];
foreach ($allKeys as $key) {
$oldRaw = $oldvalues[$key] ?? null;
$newRaw = $newvalues[$key] ?? null;
// Compare with null/empty equivalence
$oldCmp = ($oldRaw === null || $oldRaw === '') ? '' : (string)$oldRaw;
$newCmp = ($newRaw === null || $newRaw === '') ? '' : (string)$newRaw;
if ($oldCmp !== $newCmp) {
// Default: no description
$descText = null;
// If *_id, try to resolve names for description
if (substr($key, -3) === '_id') {
$table = substr($key, 0, -3);
$lookupOld = ($oldRaw === null || $oldRaw === '') ? null
: DB::DBLookup(DB::PrepareSQL(
"SELECT `{$table}_name` FROM `{$table}` WHERE `{$table}_id` = :1",
$oldRaw
));
$lookupNew = ($newRaw === null || $newRaw === '') ? null
: DB::DBLookup(DB::PrepareSQL(
"SELECT `{$table}_name` FROM `{$table}` WHERE `{$table}_id` = :1",
$newRaw
));
if ($lookupOld !== null || $lookupNew !== null) {
$descText = "[" . ($oldRaw === null ? '' : $oldRaw) . "] " . ($lookupOld ?? '')
. " => [" . ($newRaw === null ? '' : $newRaw) . "] " . ($lookupNew ?? '');
} else {
$descText = null;
}
}
$values[] = "("
. $audit_log_id . ", "
. "'" . addslashes($key) . "', "
. $sqlLiteral($oldRaw) . ", "
. $sqlLiteral($newRaw) . ", "
. $sqlLiteral($descText)
. ")";
}
}
if (!$values) return '';
return "INSERT INTO audit_log_value (audit_log_id, column_name, old_value, new_value, description) VALUES\n"
. implode(",\n", $values) . ";";}
$data = array();
$data["table"] = $table;
$data["action"] = $action;
$data["ip"] = $params[0];
$data["user"] = $params[1];
$data["date_time"] = date("Y-m-d H:i:s");
DB::Insert("audit_log", $data );
$sql = generateAuditInsertSQL($oldvalues, $newvalues, DB::LastId());
DB::Exec($sql);
return false;