tracking changes to MySql table
Posted: Tue Sep 11, 2012 10:30 am
Simplified scenario is: A database manages services ordered by clients. So there is a client table, a service table, and a table with just the fields client_id and service_id to hold what clients have ordered what services. My problem is that I need to track changes made to the services_clients table.
If a client goes into their control panel and changes their services, they are presented with a dynamically generated list of checkboxes for the services. After form submission, first all entries in services_clients containing the client_id are deleted, then the post array only contains services that are chosen, and that re-populates the services_clients table.
In order to track the changes to service orders and cancellations, itt seems natural to accumuate this data in a table, call it "changes" that contains the client_id, the service_id, the MySQL datestamp, and a boolean for whether the service was added or deleted.
The point where I am really stumped is finding the best way to compare the services the client had before the change to the services after the change. What I have thought of so far is to save the array to the POST variable that is used to populate the list of checkboxes in the first place. So when the page opens, the client may see five out of 20 services checked. Then the items in this array would be compared to the array of newly selected items, after the client submits the form. Using the array_diff function, the two arrays would be examined for changes (and whether the change was an addition or cancellation), then the "changes" table would be updated with each change encountered.
I'm wondering if there would be a better way to accomplish what I am after. Maybe with stored procedures? Maybe mySQL has a way to track changes to a table that I am unaware of?
If a client goes into their control panel and changes their services, they are presented with a dynamically generated list of checkboxes for the services. After form submission, first all entries in services_clients containing the client_id are deleted, then the post array only contains services that are chosen, and that re-populates the services_clients table.
In order to track the changes to service orders and cancellations, itt seems natural to accumuate this data in a table, call it "changes" that contains the client_id, the service_id, the MySQL datestamp, and a boolean for whether the service was added or deleted.
The point where I am really stumped is finding the best way to compare the services the client had before the change to the services after the change. What I have thought of so far is to save the array to the POST variable that is used to populate the list of checkboxes in the first place. So when the page opens, the client may see five out of 20 services checked. Then the items in this array would be compared to the array of newly selected items, after the client submits the form. Using the array_diff function, the two arrays would be examined for changes (and whether the change was an addition or cancellation), then the "changes" table would be updated with each change encountered.
I'm wondering if there would be a better way to accomplish what I am after. Maybe with stored procedures? Maybe mySQL has a way to track changes to a table that I am unaware of?