CODEWITHSUNDEEP
phpmysql
user2415204
user2415204

Reputation: 61

MySQL: How to get changes of last UPDATE

I am working on a database application with MySQL and PHP. At this moment I'm trying to get the changes caused by the last UPDATE. My first way to solve the problem is

These are three mysql-connections...

Is there any way to shorten this?

Upvotes: 6

Views: 4052

Answers (3)

rcpayan
rcpayan

Reputation: 535

Let me tell you how I do that,

When I update a row, firstly I get which row I'm updating and I call them active records. Then I compare each column of active records with the form fields. That's how I know which column has changed.

And if you want to store changed columns, create history table that would be like;

id        (for primary key)
tablename (which table i'm updating)
recordid  (which row i'm updating)
column    (which columns has been changed)
oldvalue  (active record value)
newvalue  (form value-updated value)
date      (obvious)
user      (who did this change)

After that, you can use your imagination for structures how you want to use.

Upvotes: 1

Erez Pilosof
Erez Pilosof

Reputation: 81

you can use the following hack using variables:

update table set 
col=(@oldValue:=col),col=newValue
where id=1234;
select @oldValue;

Upvotes: 1

Orangepill
Orangepill

Reputation: 24645

You could do an before update trigger that will push an entire copy of the record to a history table that also contains additional state data you wish to store (updated date, user etc.)

This way you will have a complete revision history of what happened with what records and it should happen transparently. only think to remember is you should drop any unique constraints from the history table.

Hope this helps.

Upvotes: 3

Related Questions