0

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.

Cœur
  • 37,241
  • 25
  • 195
  • 267
SAR
  • 1,765
  • 3
  • 18
  • 42

1 Answers1

0

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 ;
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • so it mean i have to create same table with same columns and triger on insert and update and delete, here i want every action to be create as new row on the log table – SAR Jun 07 '14 at 13:19
  • depends on what you want to log. If you want to log all data then yes - you have to create a backup table of every table. – juergen d Jun 07 '14 at 13:22
  • you mean it's kind of replication type but i need log table that store every action on database and keep the track of changes as every users have same write so need to know in case what which user have dont. – SAR Jun 07 '14 at 13:24