Mysql (almost) full audit

Question

I am looking for a way to make a simple event log for my tables. I have several tables that can be modified by different users, and I want to track:

- who made the change
- when 
- what was before update
- what is the new value
- which table and which record & column

somthing seems to be fine:

20:00:00 | john | update | products | 113 | product_name | "xbox" | "xbox 360"
20:00:10 | jim  | update | products | 113 | product_name | "xbox 360" | ""
20:01:00 | jim  | delete | products | 113

So, I read that triggers may be the answer, but as I understand it, it seems to me that I need to have a complete new table for each column that I want to track. Triggers are not ideal for this work, since I want to register who made the changes, and from what I read, this is impossible.

I was thinking of creating 3 different functions for CRUD (insert, update, delete) and just before executing the request, to check what has changed, and make a log, and then run the request. But from here it seems very slow and complicated.

Is there any better way?

thank

---

, , , , , , , , .

, , , , .

mysql :

• id (a_i, primary)
• create_date (datetime)
• user_id (int)
• table_name (tinytext)
• record_id (int)
• cell_name (tinytext)
• action_type (tinytext)
• old_value ()
• new_value ()

. "INSERT" "DELETE", , .

function log_query($action_type, $table, $values, $parameters){

if ($action_type == 'UPDATE'){

    log_updates($action_type, $table, $values, $parameters);

    $query = "UPDATE $table SET ";
    foreach ($values as $key => $value){
        $query .= $key."='";
        $query .= $value."', ";
    }
    unset($value);

    $query = substr($query, 0, -2);

    $query .= ' WHERE ';

    foreach ($parameters as $key => $value){
        $query .= $key."='";
        $query .= $value."' AND ";
    }
    unset($value);

    $query = substr($query, 0, -4);

    $result = mysql_query($query);

    }
} 

:

function log_updates($action_type, $table, $values, $parameters){
$where = " WHERE ";
$user_id = '1234'; //example
foreach ($parameters as $key => $value){
        $where .= $key."='";
        $where .= $value."' AND ";
}
unset($value);

$where = substr($where, 0, -4);

foreach ($values as $key => $value){
    $result = mysql_query("SELECT $key, id FROM $table $where");

    $row = mysql_fetch_row($result);
    $old_value = $row[0];
    $record_id = $row[1];

    if ($action_type == 'UPDATE'){
        if ($old_value != $value){
            $logger = mysql_query("INSERT INTO auditing (event_date, action_type, user_id, table_name, record_id, cell_name, old_value, new_value)
                                    VALUES (NOW(), '$action_type', $user_id, '$table', '$record_id', '$key', '$old_value', '$value')");
            if (!$logger) echo mysql_error();
        }
    } 


    }
    unset($value);

}

, , log_query:

$update = Array('name' => 'barbara', 'description' => 'new name');
$parameters = Array('id' => '1', 'name' => 'barbi');
log_query('UPDATE', 'checktable', $update, $parameters);

, "" . , , . . :

UPDATE checktable SET name='barbara', description='new name' WHERE id='1' AND name='barbi'

, . . - .

Answer 1

, .

• , .
• , , , ( - SET @user='someone' .
• INSERT, UPDATE DELETE, / - , OLD.

, coluns (a, b, c) (user_id, modtime, a, b, c).

:

• ( )
• deluxe, /
• "" (.. group_members groups, groups, group_members.

, , , , , .