Displaying only changed attributes from SQL

PHP
1

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!

2

not with this DB setup.

3

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?

4

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
5

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.

6

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!

7

ok —

8

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