Page 1 of 1

tracking changes to MySql table

Posted: Tue Sep 11, 2012 10:30 am
by rhecker
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?

Re: tracking changes to MySql table

Posted: Tue Sep 11, 2012 5:20 pm
by Christopher
rhecker wrote: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.
Why do you care about the differences? I think just make a date stamped snapshot of all the data before the change. Then you could nicely GROUP BY change date for a given client ID and see the history by date. The view code would deal with how to present the data. Unless you are worried about the amount of data archived I think it simplifies it to archive it all.

Re: tracking changes to MySql table

Posted: Tue Sep 11, 2012 6:17 pm
by rhecker
Why do I care about the date? The invoicing system I will build will need to be able to charge for partial months of service. That's the main reason. Becausing invoicing is monthly.

But I also want to know exactly what service has changed. I can easily recieve notification if changes have been made, but then I have to figure out exactly what has changed; that's why I need the comparison.

Re: tracking changes to MySql table

Posted: Wed Sep 12, 2012 6:39 am
by rhecker
I have decided that an update trigger in the MySQL database is the best way to handle this issue.