I'm looking for a way of making simple event log for my tables. I have few tables that can be changed by various users, and I want to keep track on:\[code\]- who made the change- when - what was before update- what is the new value- which table and which record & column\[/code\]somthing like will be great:\[code\]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\[/code\]So i read that triggers could be the answer but as far as I read it seems that I need to have a complete new table for each column I want to keep track on. Triggers are not perfect for this job also because I want to log who made the change, and from what I read this is not possible.I thought of making 3 different functions for CRUD (insert, update, delete), and just before making the query, to check what is changed and to make the log and then run the query. But from here it seems to be very slow and complicated.Is there another better way ?ThanksOk, I checked again the triggers and its not what I was looking for, so I wrote simple functions that will check for each query you want to log if the new values are different, and if so - it logs it.The main problem is, and I didn't measure it, but it obviously slower.first you need to make a new mysql table as follow:
- id (a_i, primary)
- creation_date (datetime)
- user_id (int)
- table_name (tinytext)
- record_id (int)
- cell_name (tinytext)
- action_type (tinytext)
- old_value (text)
- new_value (text)
after that, write the functions. I didn't write yet the 'INSERT' and 'DELETE' sections but I think i should be much easier.\[code\]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); }} \[/code\]and :\[code\]function log_updates($action_type, $table, $values, $parameters){$where = " WHERE ";$user_id = '1234'; //exampleforeach ($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 = http://stackoverflow.com/questions/3816839/$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);}\[/code\]to call the functions you need to first order the parameters to find the specific row, and the new values in to arrays, and after that call the log_query function:\[code\]$update = Array('name' => 'barbara', 'description' => 'new name');$parameters = Array('id' => '1', 'name' => 'barbi');log_query('UPDATE', 'checktable', $update, $parameters);\[/code\]This will actualy will check if the 'name' has changed and if the description is changed. for each one, if it change, it will insert new record into 'auditing' table specifying the exact change.after loging the change, it will run the update query. in our example:\[code\]UPDATE checktable SET name='barbara', description='new name' WHERE id='1' AND name='barbi'\[/code\]hope this is helping. It tested for now and works. If there will be updates - I'll post it here.
