4

I have decided to create a new table in my database that will be used for auditing all actions performed on my database.

My table "activity" is fairly simple and contains the following columns:

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

What I am doing at the moment is when a query is performed I then have another query after it which inserts information into my "activity" 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 am having to do this manually for each query and I have over 100 queries in my system and I really don't want to have to type a individual query to record what happens.

I was wondering is there any features in MySQL that can tell me what actions have been performed or is there any libraries or techniques in PHP that can help me?

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

Thanks

tomaytotomato
  • 3,788
  • 16
  • 64
  • 119

3 Answers3

2

Create ON INSERT, ON_UPDATE and ON_DELETE triggers for your table that write details of all changes to an audit activity table

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • 1
    How to assign the user_id to specific audit. If user_name/user_id is assigned to for record for eg. insertion made by user xyz, deletion by abc, updation by xyz. Because login table may contains user details. How to send these current user details to ON INSERT, ON_UPDATE, ON_DELETE – Rohan Khude Feb 15 '16 at 12:02
1

What about to create triggers?

Thia is not good idea to make auditing in PHP.

rkosegi
  • 14,165
  • 5
  • 50
  • 83
1

How about you make a new method to your class, say $db->ql() (from query/log) that first makes the normal query, than takes the mysql_affected_rows() and it does something like

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

That way you'll have a complete history of your queries, not only the type of query and the table it was run on.

Sorin Buturugeanu
  • 1,782
  • 4
  • 19
  • 32
  • 1
    You shouldn't inline your query variables, especially those with content as as unpredictable as `$query`, into your SQL. If possible, use placeholders; or else, use the proper "quote" mechanism to properly escape the string. – bart Feb 16 '12 at 12:33
  • @bart, you are correct. you should `mysql_real_escape_string()` the `$query` right before you insert it into the `logs` table. – Sorin Buturugeanu Feb 16 '12 at 19:07