Forms Processing: Updating Only Changed Data
We have several lengthy forms that are going to be used for updating records and various data. We are also looking at recording which form fields have been updated. Is there a means for updating only those records which have changed? I was thinking of looping through a form collection, comparing submitted vs. existing database values, storing any changed values and then having a CFC process the SQL update. If this is a good starting point, how would I be able to update only those fields that need to be changed? For example if I have 100 fields and only 10 have been updated, how can I write a query to update only those 10 fields? Thanks in advance. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331546 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Forms Processing: Updating Only Changed Data
The first thing that jumps out at me would be to have hidden form fields in the form that contain the original values. Then, when you're processing the form any value that doesn't match its corresponding hidden value has been changed. thanks, eric cobb ecar technologies, llc http://www.cfgears.com Blizzm wrote: We have several lengthy forms that are going to be used for updating records and various data. We are also looking at recording which form fields have been updated. Is there a means for updating only those records which have changed? I was thinking of looping through a form collection, comparing submitted vs. existing database values, storing any changed values and then having a CFC process the SQL update. If this is a good starting point, how would I be able to update only those fields that need to be changed? For example if I have 100 fields and only 10 have been updated, how can I write a query to update only those 10 fields? Thanks in advance. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331555 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Forms Processing: Updating Only Changed Data
Some of the answer depends on your overall architecture. The first step would be to retrieve the relevant database record using the ID passed from your form. That would give you, in some manner (a query record, a bean, whatever) a representation of the existing data. Then you could write a routine that compares the values of the retrieved record/bean with those submitted in the form. You might also think of this as two different tasks: 1) updating the record, and 2) recording (elsewhere) the changes. The second task is the audit, of course. That's why you're doing the comparison. As for the first, there's no particular reason to worry about which fields have been changed when you're updating the record. Just use all the form fields and do an update. By definition, the changes will be recorded and the data that has not changed will remain the same. That avoids the necessity of writing a complex dynamic update statement, which could be a big benefit with as many fields as you seem to have. -- Thanks, Tom Tom McNeer MediumCool http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331562 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Forms Processing: Updating Only Changed Data
After reading my answer, I realize the way I worded it may be confusing. Here's an example. On your form: input type=hidden name=Val1_orig value=#val1# input type=text name=Val1 value=#val1# On your processing page. cfif form.Val1 neq form.Val1_orig !--- this element has been changed. --- /cfif That's the basics of what I was trying to get at. thanks, eric cobb ecar technologies, llc http://www.cfgears.com Eric Cobb wrote: The first thing that jumps out at me would be to have hidden form fields in the form that contain the original values. Then, when you're processing the form any value that doesn't match its corresponding hidden value has been changed. thanks, eric cobb ecar technologies, llc http://www.cfgears.com Blizzm wrote: We have several lengthy forms that are going to be used for updating records and various data. We are also looking at recording which form fields have been updated. Is there a means for updating only those records which have changed? I was thinking of looping through a form collection, comparing submitted vs. existing database values, storing any changed values and then having a CFC process the SQL update. If this is a good starting point, how would I be able to update only those fields that need to be changed? For example if I have 100 fields and only 10 have been updated, how can I write a query to update only those 10 fields? Thanks in advance. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331565 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
