Diff on a row in mysql

Question

I am wondering if there is an easy way to compare a couple of rows of MySQL data.

In particular, what i have is a table containing a list of setting values for each user.

A user can modify these settings in a gui in no problem.

Now, what i am trying to do is : whenever a user saves new data, i want to find the diff between the old data and the to-be-saved data to find out which columns where changed and later save to a log...

The way i am doing this right now is reading the row corresponding to the user before saving and comparing it, variable by variable to find the changed data but i find it slow and i am wondering if there is a smarter way to do this, perhaps inside a mysql query (maybe using a temporary table?) or by using some php mysql function i don't know about...

I hope you have some ideas for me.

(I checked this question : https://stackoverflow.com/questions/218499/mysql-diff-tool , but that turns out to be a lot different than what i am looking for)

Thanks in advance!

Answer 1

You can do this with the array_diff_assoc() function in PHP.

Here we have two rows of data in php arrays.

$newValues = array_diff_assoc($afterRow, $beforeRow);

It will return an array where any column values have changed or been added.

Edit

To do this in MySQL, you'd need them in name value pairs, something like this:

Prefs
----------------------------------------------
UserID   TransactionID   Name       Value
----------------------------------------------
1        1               Font       Sans Serif
1        1               Color      Red
1        1               Height     100
1        1               Width      400

1        2               Font       Verdana
1        2               Color      Red
1        2               Height     100
1        2               Indent     50

TransactionID 1 is the old row and Transaction ID 2 is the new row:

SELECT * FROM Prefs new
  LEFT JOIN Prefs old
  ON new.Name = old.Name
    AND new.Value = old.Value
  WHERE UserID = 1
    AND new.TransactionID = 2
    AND old.TransactionID = 1
    AND (old.Name IS NULL OR old.Value IS NULL)

If my logic is right, should yield:

----------------------------------------------
UserID   TransactionID   Name       Value
----------------------------------------------
1        2               Font       Verdana
1        2               Indent     50