Logging of all queries run through phpMyAdmin

We are using a developing DB and a production DB for
our CMS. Since there can be changes to the data on the
production while we are working on new features it was
constantly a hustle to deploy the changes made on the
development system to the live version.

I wrote a small function that will log all queries run
on phpMyAdmin, so we allways know exactly what changes
where made.

I split up the log files to represent the different
types of queries run and also made it configurable
which types of queries will be logged (most people
won't use select and show, but just in case). Also
there is one logfile where all queries are stored and a
seperate logfile for the different types (so you can
see changes to the table layout in one view)

Hope you like it. This could have saved us a lot of
trouble if I thought about it earlier.

// FILE ./config.inc.php
// For each server can be specified
$cfg['Servers'][$i]['log_query'] = FALSE; //
possible values in an
array('insert','update','delete','alter','select','show','explain','maint')

// FILES ./libraries/dbi/mysql.dbi.lib.php (line 100)
&& ./libraries/dbi/mysql.dbi.lib.php (line 104)
// (if anybody knows a better place to put this, it'd
be great)
// in the function PMA_DBI_try_query add
PMA_log_query($query);

// FILE maybe ./libraries/common.lib.php
// add logging function
/**
* Checks the query and writes to the according logfile
*/
function PMA_log_query($sql_query) {
global $cfg;
if(is_array($cfg['Server']['log_query'])) {
//check for log directory
if(!is_dir('./logs')) mkdir('./logs');

        //check for server specific directory
        $name = \($cfg\['Server'\]\['verbose'\]\) ?

$cfg['Server']['verbose'] : $GLOBALS['server'];
if(!is_dir('./logs/' . $name)) mkdir('./logs/' . $name);

        //check for DB specific directory
        if\(\!is\_dir\('./logs/' . $name . '/' .

$GLOBALS['db'])) mkdir('./logs/' . $name . '/' .
$GLOBALS['db']);

        //set the type of query || see sql.php line 305
        $type = 'select';       
    if \(preg\_match\('@^EXPLAIN\[\[:space:\]\]+@i',

$sql_query)) $type = 'explain';
else if (preg_match('@^DELETE[[:space:]]+@i',
$sql_query)) $type = 'delete';
else if
(preg_match('@^(INSERT|LOAD[[:space:]]+DATA|REPLACE)[[:space:]]+@i',
$sql_query)) $type = 'insert';
else if (preg_match('@^UPDATE[[:space:]]+@i',
$sql_query)) $type = 'update';
else if (preg_match('@^SHOW[[:space:]]+@i',
$sql_query)) $type = 'show';
else if
(preg_match('@^(CHECK|ANALYZE|REPAIR|OPTIMIZE)[[:space:]]+TABLE[[:space:]]+@i',
$sql_query)) $type = 'maint';
else if
(preg_match('@^(CREATE|ALTER)[[:space:]]+@i',
$sql_query)) $type = 'alter';

        //write the logfile     
        if\(in\_array\($type,$cfg\['Server'\]\['log\_query'\]\)\) \{
        $sql\_log = fopen\('./logs/' . $name . '/' .

$GLOBALS['db'] . '/' . $type . '.log', 'a');
$end = (substr($sql_query, -1, 1) == ';') ? '' : ';';
fwrite($sql_log, $sql_query . $end . "\r\n");
fclose($sql_log);

        $sql\_log = fopen\('./logs/' . $name . '/' .

$GLOBALS['db'] . '/complete.log', 'a');
$end = (substr($sql_query, -1, 1) == ';') ? '' : ';';
fwrite($sql_log, $sql_query . $end . "\r\n");
fclose($sql_log);
}
}
}