Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

How to keep history of record updates in MySQL?

Tags:

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.

like image 910
Proxium Avatar asked Mar 29 '10 09:03

Proxium

People also ask

Is there a MySQL option feature to track history of changes to records?

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.

How do you keep track of database changes?

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.

How does MySQL store historical data?

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.

Does MySQL have change tracking?

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.

1 Answers

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.

like image 157
wimvds Avatar answered Oct 05 '22 07:10

wimvds
Sign in to Comment

Related questions

Comment associative array in PHP Documentor
Create an Oracle function that returns a table
Autoincrement uniqueidentifier
Aborting jQuery().load()
Installing a new version of a deployment project over old version
How to adjust line height in TextView with multiple type sizes?
On a multicore x86, is a LOCK necessary as a prefix to XCHG?
Inserting multiple rows using JdbcTemplate
Metro-like theme for WPF [closed]
Convert InputStream(Image) to ByteArrayInputStream
Server polling with JavaScript
Why is mysqli giving a "Commands out of sync" error?

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!