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 : MySQL Diff Tool , but that turns out to be a lot different than what i am looking for)
Thanks in advance!
Source: Tips4all, CCNA FINAL EXAM
You can do this with the array_diff_assoc() function in PHP.
ReplyDeleteHere 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