MySQL Logging to MySQL

Author: Wayne Eggert
Date: 03/14/05
Difficulty: Novice

Introduction
This tutorial explains a straightfoward way of logging MySQL queries to a MySQL table. Why would you want to do this? Well, one possiblity is for tracking queries that were run in your application and which users ran them. This is especially useful if you have a backend with multiple users and want to keep track of who caused a system meltdown =)

Create the Database Table

Function
Now add the function below to a global include file or create a new file called functions.php to include in every file that you want to perform SQL query logging.

<?
function mysql_execute_query($sql){
    global $PHP_SELF;
    global $HTTP_SESSION_VARS;
    global $HTTP_SERVER_VARS;

    $pagename = basename($PHP_SELF);
    $username = $HTTP_SESSION_VARS['username'];
    $ip = $HTTP_SERVER_VARS['REMOTE_ADDR'];

    $sqlcheck = substr(strtoupper($sql),0,6);

    $cleansql = addslashes($sql);
    // log ip address, username, query
    if($sqlcheck != 'SELECT'){
        $log_sql = "INSERT INTO systemlog (username,sqlcommand,page,ipaddress,dateTime) VALUES ('".$username."','".$cleansql."','".$pagename."','".$ip."','".date("Ymdhis",time())."')";
        mysql_query($log_sql);
    }

    return mysql_query($sql);
}
?>


Explanation:

• The first few lines allow the function to access some global system variables.
• The file name, user name & ip address are stored in variables.
• $sqlcheck grabs the first 6 characters of the SQL query being ran (to determine if it is a SELECT, UPDATE, DELETE, etc)
• Finally, as long as the query is not a SELECT statement, the query is stored in the database. You may choose to store SELECT statements, but in my case I was only interested in SQL statements that caused data to change.

This function can then be used instead of the "mysql_query()" function as such:

mysql_execute_query($your_sql_query);

That's all there is to it. Happy SQL logging!


Comments: