A way to record what actions are performed in the database? -MySQL / PHP

Question

I decided to create a new table in my database, which will be used to check all the actions performed in my database.

My activity table is pretty simple and contains the following columns:

• activity_id - primary key
• user_id - user foreign key
• action - type of query being executed (SELECT, UPDATE, DELETE)
• table - name of the affected table
• string - name of the affected string (s)

What I'm doing at the moment is when the request is executed. After that, I have another query that inserts information into the table. "

CODE:

//query $db->query("DELETE FROM foo WHERE id = '$foo->id'"); //activity record query $db->query("INSERT INTO acitivity ( user_id, action, table, row ) VALUES ('$user->id', 'Deleted' , '$foo->id', 'foo')"); 

As you can see, I need to do this manually for each request, and I have more than 100 requests in my system, and I really do not want to enter an individual request to record what is happening.

I was wondering if there are any functions in MySQL that can tell me what actions were performed or are there any libraries or methods in PHP that can help me?

Or am I doomed to write out 100 individual queries in my system?

thanks

Answer 1

Create ON INSERT, ON_UPDATE, and ON_DELETE triggers for your table, which write all changes to the audit table.

Answer 2

How to create triggers?

Thia is not a good idea to do an audit in PHP.

Answer 3

How about how you create a new method for your class, say $db->ql() (from query/log ), which first makes a normal request, than it accepts mysql_affected_rows() , and it does something like

 insert into logs set when = now(), query_string = "{$query}", affected_rows = "{$affected_rows}" 

Thus, you will have a complete history of your queries, not just the type of query and the table on which it was launched.