Page 1 of 1

Storing changes to table data

Posted: Sat Oct 20, 2007 9:40 am
by eoinoc333
Hi all,

I am working on a project where users will create and change database information. I want to keep a historical record/audit trail/change information about each record. A record contains several editable fields. One approach I'm thinking of is storing a complete copy of a row (within my PHP code) before calling an UPDATE on it. Then, some type of diff function could be used to show what changed upon each update.

I have been told of the possible use of triggers, but am only using MySQL 4.1.21.

Do you know of a script of PHP class which has been already written for such a functionality?

Thanks.
Eoin

Posted: Sat Oct 20, 2007 9:44 am
by aaronhall
If you maintain an ID that is common to all revisions of a particular dataset, you'd simply insert the new copy in the same table along with a timestamp... no update necessary.

I'm sure if you searched, you'd find a few diff visualization tools for php

Posted: Sat Oct 20, 2007 10:31 am
by eoinoc333
Thanks, the solution sounds pretty straight forward.

I think I will keep the newest revision in table A, and all revisions in table B. There will be searches on records in Table A, so I guess it's best to keep its record count as low as possible, particularily when there will be few thousand entries.

Regards,
Eoin