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.
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 ;