Mysql row difference

Question

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

In particular, I have a table containing a list of settings for each user.

The user can easily change these settings in gui.

Now I'm trying to do this: whenever a user saves new data, I want to find the difference between the old data and the data being saved, to find out which columns have been changed, and then save to login ...

The way I'm doing it now is to read the line corresponding to the user, before saving and comparing, the variable variable to find the changed data, but I find it slow and I wonder if there is a more reasonable way to do this, maybe inside a mysql query (perhaps using a temporary table?) or using some php mysql function that I don't know about ...

Hope you have some ideas for me.

(I checked this question: https://stackoverflow.com/questions/218499/mysql-diff-tool , but this turns out to be very different from what I'm 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 in which any column values ​​have been changed or added.

Edit

To do this in MySQL, you will need them in pairs of name values, 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 line, and Transaction ID 2 is the new line:

 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 correct, you should:

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