How to log user actions to db using the Zend Framework

Question

Hi guys,

I am building a cms at the moment using the zend framework and have implemented a text based log which will log any errors and allows me to log any custom messages I may want recorded.

As part of the cms I need to provide some sore of revision history for pages, blog posts etc. My thoughts on this were to simple have a logs table to store the user, action, datatime, sql run, table name the query was executed on and the record.

To get the revision history for a page you would just query the log table for the table name and recordid for the current record viewed. Order by date desc. You could then provide the functionality to just revert to the revision desired by running the update statement in the sql column of the log table. If its an insert then figure something out to convert the insert into an update or dont allow the user to revert to the initial insert.

Is this the best way to accomplish this or am I being a bit simple.

Answer 1

It would be better to add a "revision" column to the tables you want to support revisions, so there will be multiple revisions per document in that table.

You could also add an "active" column that is 1 for the revision you want to be active, and 0 for other revisions of the same document. That makes it easy to query for the revision you want to show, simply with WHERE active = 1. It also makes it easy to change which revision is active.

I would not recommend storing complete SQL statements in a log table. That's an SQL injection security risk.