Storing changes to table data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
eoinoc333
Forum Newbie
Posts: 2
Joined: Sat Oct 20, 2007 9:33 am

Storing changes to table data

Post 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
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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
eoinoc333
Forum Newbie
Posts: 2
Joined: Sat Oct 20, 2007 9:33 am

Post 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
Post Reply