CODEWITHSUNDEEP
phpmysqlhtml
trainoasis
trainoasis

Reputation: 6720

How to implement history of mysql events?

I have a MySQL table with next fields:

id int(11) primary auto_increment
data(text)
user varchar(20)
date text

I have a web based UI with php, where I show the data(data,user and date), and people can add or delete data as well. The results are seen into the table properly.

Now I want to implement a history somehow. So, if I deleted for instance a row one time, I want this to be seen in a lets say 'history' sql table. I want to store all the events(add or delete) by user and date ofcourse, that's all.

How should I approach this?

Upvotes: 1

Views: 608

Answers (4)

trainoasis
trainoasis

Reputation: 6720

I actually made my own "history" now. I created another table just for history entries and wrote another function (php) that performs every time that someone adds/deletes something and writes data into the pre-created table. Then I just show the data in a "History" page and it works well enough.

Upvotes: 0

Oliver Tappin
Oliver Tappin

Reputation: 2541

I would have it so that all instances of the table are saved but you add a new column within your structure which shows 'live' rows.

For example:

------------------------------------------------
- id - user - data - live- date                -
------------------------------------------------
- 1  - 53   - ???  - 0   - 2012-07-16 15:51:45 -
- 1  - 53   - ???  - 0   - 2012-07-16 15:45:45 -
- 1  - 53   - ???  - 1   - 2012-07-16 14:51:42 -
- 1  - 53   - ???  - 0   - 2012-07-16 15:12:45 -
- 1  - 53   - ???  - 0   - 2012-07-16 13:54:21 -
- 1  - 53   - ???  - 0   - 2012-07-16 11:41:33 -
------------------------------------------------

I would then query the table to find the 'live' rows by adding in WHERE live=1;, that way, the rest is history.

Upvotes: 0

Horen
Horen

Reputation: 11382

It's not that easy to answer with the information provided. In other words: Your question is very general.

I'll try to answer anyways: I think you should set up a table history, where the foreign key would be the user_id. Then you just save every action (add, delete, etc) in that table together with the user_id and a timestamp and say the data that was added or deleted or whatever other information could be useful. (Update: The way @Gibbs proposed is one possibility)

When you want to display the history you can easily select all actions taken by one user ordered by the timestamp.

One more thing: It is much better to use a datetime format for the date (e.g. timestamp) than using a VARCHAR.

Upvotes: 1

Gibbs
Gibbs

Reputation: 304

I would recommend using triggers.

This article should help you grasp an approach using triggers: http://codespatter.com/2008/05/06/how-to-use-triggers-to-track-changes-in-mysql/

Upvotes: 2

Related Questions