Menu
I have to create a code in PHP that will allow me to keep the history of record updates in MySQL database so I can find by date an old revision.
Here is the example of what I actualy want to achive: http://en.wikipedia.org/w/index.php?title=Tunisia&action=history
The data are mostly Numbers that we record about the company to generate reports and to extract indices.
I plan to use codeigniter for it's simplicity and I'm looking for idea about a framework or an opensource project that use the same approach to keep history of modifications in the database.
910
Create a table called changes . It would contain the same fields as the master table but prefixed with old and new, but only for those fields which were actually changed and a TIMESTAMP for it. It would be indexed with an ID . This way, a SELECT report could be run to show the history of each record.
At the basic database level you can track changes by having a separate table that gets an entry added to it via triggers on INSERT/UPDATE/DELETE statements. Thats the general way of tracking changes to a database table. The other thing you want is to know which user made the change.
All current and historical records in one table - since each row will have an id that will allow me to identify which records are part of the same group. Current and historical records in separate tables - each time the current record is updated, a copy of it pre-update is put into the history table.
MySQL CDC Setup: Using Binary LogsMySQL binary logs provide a very efficient way to track data changes for MySQL CDC. They contain events that describe the modifications to data.
The easiest solution (depending on your specific needs) would probably be to add an on update/insert/delete trigger to your table, so you can perform extra logging when data is inserted/updated/deleted. That way even manual interventions on the db will be covered...
Check http://dev.mysql.com/doc/refman/5.1/en/triggers.html for more information.
157
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
