PHP pdo，如何将执行到数据库的每个查询作为日志存储到同一数据库中的表

Question

working with php5.5 and mysql5.5, have developed an application with the PDO connection. now as log table I want to create a table(db_log):

id--------auto
query ----query 
create----curent time
user------session-user

AND store every insert, update and delete action on database to a mention table, for example there table named( tbl_temp) with some coumns and there user come and run query like

(DELETE * FROM tbl_temp where id = 1) from the user (user1)

here when this query runs like from the page of (delete.php).. it should save the query on the table of db_log

id = 1
query = DELETE * FROM tbl_temp where id = 1
create = datetime
user = user1

so that how i will be able to record every action of user on database and control the user activity,

1 - here do i need to pass the query to db_log in every, page, or i can build a class 2- is there any good solution or example on web to learn.

Answer 1

You can create insert/update/delete triggers on all tables that you want to log. In that triggers you copy the data into your log table.

For instance:

delimiter |
CREATE TRIGGER log_insert_tbl_temp BEFORE INSERT ON tbl_temp
FOR EACH ROW BEGIN
   insert into db_log (table_name, action, id)
   select 'tbl_temp', 'insert', NEW.id;       
END
|
delimiter ;