Displaying only changed attributes from SQL

Hi, I am trying to create an HTML table full of historical data of user accounts, but I only want to display the data that has changed. I am having issues, because the current data of the user accounts is stored in a different SQL table than the historical data, and the attributes that I would like to match up appear in a JSON string in the database with the historical data. Is there a clean, efficient way to go about checking if the data for each of the categories has changed, for each of the changes to the user account?
For clarification’s sake:
Here is the SQL I am fetching the current state of the user accounts from:
SELECT * FROM userAccounts
And the historical data:
SELECT * FROM historicalAccounts where id='$row["id"]'

This works as intended, however the second table does not have the same rows as the first, instead it has a row called JSON that contains the majority of the attributes of each user. I am able to create a table that contains the information I want, however I am looking for a snippet of PHP code that will allow me to only display the attributes that were changed from each of the past updates of the information. Thanks!

not with this DB setup.

Ok, thank you for the quick response. Unfortunately, I did not design the database nor do I have access to change it to a better setup. Do you know of a way to do it at all?

you would have to get the json (and actual) data and run it through a couple of array functions (mostly array_diff() (and its variants))

1 Like

It would be better if you could provide some example data from both tables. Are you using a newer version of MySQL that includes json support? If so there *might be a more logical way to get at the data you’re after in the historical table. Otherwise using application code to do so is really the only option.

Unfortunately I can’t provide any of my data, but I think through tinkering I have found a working solution. Thank you for your help!

ok —

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.