Submitting a 50 field form to multiple tables; regular POST, AJAX POST or other?

Question

Long time reader of Stackoverflow; first time poster, so hope you'll be gentle :)

I have a form on a page consisting of about 50 fields of varying types (checkboxes/text/decimal/date etc. etc.). The values are pulled in from about 8 tables through one query roughly like so:

SELECT * FROM p
LEFT JOIN pd on p.id=pd.id
LEFT JOIN pc on p.id=pc.id
LEFT JOIN pie on p.id=pie.id
etc.
WHERE p.id = xxx

I started the day thinking I'd just use a simple POST on the form, write a bunch of validation and update queries (overriding every single existing value with whatever is in the form) and be done with it, but I am questioning my judgement here.

Specifically, it feels wrong to be overriding an existing value if it hasn't changed, and I'm slightly worried about what happens if the db updating fails half way through (thinking of handling that with Transactions). I am comfortable with this on smaller forms, but if staff has only changed 1 or 2 fields, this feels like a lot of writing for nothing. My next thought then was to make it AJAX based on a per field level. Changing any field submits the change and saves it. Feels like it might make more sense, even if I'd prefer to avoid js if I could. A third option of course is to turn it into multiple forms with multiple submit buttons, say one per tab (the form is already divided up into tabs), with the downside then being reloading the page more often as it needs more submitting (though here too AJAX could of course be used).

Should I even be putting this much thought into it (spent the better part of the day so far reading up on old threads here...)?! There's a bit of financial data involved here, so my main concerns are reliability and performance, but I'm also curious if there is any kind of best practice that others follow?

--- UPDATE AFTER IMPLEMENTING CHOSEN ANSWER BELOW ---

Being a long time reader of SO, I always appreciate the threads where the person asking the question follows up later on, so thought I'd do so myself. Not sure of correct protocol or formatting.

As per above, I ended up going with barnyr's solution, which essentially uses javascript to compare the form on submit with the original values and then posts the changes to mysql (using jquery post). Here's some things to think about if you are considering a similar scenario:

1. Off the bat, jquery's serialize does not send checkbox/radio values if they are not selected. I see their logic, but to me this doesn't make sense. I used the plugin at http://tdanemar.wordpress.com/2010/08/24/jquery-serialize-method-and-checkboxes/ to resolve this.

2. If you edit a value on the page, then save it and then edit it again, back to the original value, you will get a 'nothing changed' message as compared to the initial values set on page load, nothing has changed. It's logical, but I hadn't considered this till after I was all done and testing. I don't really see any way around this that would warrant keeping the complexity that comes with this solution over a simple 'override everything on form submit', so if you are building a public application where you care about your users, I would NOT recommend you use this methodology.

3. In terms of normalization, this solution is beautiful as I can keep rows from being added to tables linked to the main one containing userid, unless content is added to those specific fields. However, if point 2 was a big issue for me, it would cut a lot of complexity out of the code to just store all these values displayed in the one big form in one big table. I'm pretty much a newbie wrt normalization (so go easy on the pitchforks) and this is of course mainly a consequence of all data being displayed in just one form. If you are using multiple forms, this no longer applies.

4. Part of the system involves a lot of numbers, summed in other parts of the form, and doing all of this via AJAX means you have to be very careful and clear on what exactly is changing when the user hits save. For example they can change the program price, and the total price should also update, but as you are submitting via AJAX and there is no proper reload, you have to code all this back into the system.

Points 2,3 and 4 could be worked around in this case, but had I known what I know now when starting, I probably would have gone with one big table with all data, and a simple edit entire row on form submit. Then again, I would have learned a lot less, so no regrets :) Hope this is of help to those finding this thread at a later stage.

Answer 1

Well, I would compare the values that are on the page with the values that the user has changed. Then I would POST the changed values to server, dynamically create my query and update only the fields that have changed.

Also, you should definitely use transactions if you are updating multiple tables.

Answer 2

UPDATE - the initial approach didn't handle other input types so well. I've changed the code to handle common input types as well as using DOM properties for initial values, which avoids having run any code on load of the page:

Here's the link: http://jsfiddle.net/rLwca/5/ and here's the updated function:

    //Initial setup no longer needed. the DOM has the default states anyway...

//heres where we filter the elements for ones which have changed
$("#My50PageForm").submit(function(){        
    var elems = $("#My50PageForm :input").filter(function(value){
        var elem=$(this),
            type=this.tagName +"_"+(elem.attr("type")||""); // uniquely name the element tag and type

        switch (type){
            case "INPUT_radio": case "INPUT_checkbox":                    
                return elem.prop("checked")!=elem.prop("defaultChecked");
            case "INPUT_text": case "INPUT_textarea": case "INPUT_":                 
                return elem.val()!=elem.prop("defaultValue");
            case "SELECT_":
                var options=$(this).find('option'),
                    defaultValue=options.first().val(); //use the first element's value as default in case no defaults are set
                options.each(function (i,o) {
                    defaultValue=this.defaultSelected?this.value:defaultValue;
                });
                return $(this).val()!=defaultValue;

            default:
                console.log("type "+type+" not handled");
                return false;
        }
     });

    if(elems.length){
        console.log(elems.serialize());
        return false;
        $.post("http://jsfiddle.net/example.cfm",
               elems.serialize());
    }else{
       alert("nothing changed");   
    }         

    return false;
});

Original code below:

Here's a link to a minimal example of sending what's changed:

http://jsfiddle.net/UhGQX/

$(document).ready(function(){
//Copy all form valued into a data attribute called 'original' when the page loads
$("#My50PageForm :input").each(function(elem){
    $(this).data("original",$(this).val());
});

//here's where you check what has changed
$("#My50PageForm").submit(function(){        

    var elems = $("#My50PageForm :input").filter(function(value){
        var elem=$(this),
        original=elem.data("original");
        console.log(original);
        //check that original isn't 'undefined' and that it's been changed
        return original && elem.val()!==original
    });
    if(elems.length){
        //post the data back to your server for processing
        $.post("http://jsfiddle.net/example.cfm",
               elems.serialize());
    }else{
     alert("nothing changed");   
    }         

    return false;
});
});

The key bits are:

• When the page loads, use jQuery to make a copy of the initial values of each form field
• when the submit is fired, compare each field's current value to the one saved when the page loaded.
• if there are changes, post the data back to the server.

Other approaches might be, allowing the whole form to be posted:

• store the data on the server in session
• re-run the select you used to populate the page, then compare that against what's been posted by your form

Answer 3

I would avoid database writes based on client onChange or onBlur events. While it's easy to code, it's based on the assumption that the user has actually decided the appropriate value for that field. That's an unsafe assumption. Some people proofread. Others abandon forms halfway through. Also, if the form field is a select, the onChange event may fire before it should.

I would also avoid storing data in the session scope as suggested by barnyr. We've been burned by users inadvertently changing their session variables by opening new browser tabs.

An approach I often take is to create a query object of the form fields and use QofQ to decide if I need to update anything. Here is a simple example.

<cfscript>
RecordsToUpdate = QueryNew("a");
FormValues = QueryNew("id,name","integer,varchar");
Delim = Chr(30);
</cfscript>

<cfloop list="#form.fieldnames#" index="ThisElement">
<cfset ThisValue = form[ThisElement]>
<cfif left(ThisElement, 12) is "NewCategory_">
 not relevent here
<cfelse>
<cfscript>
ThisId = RemoveChars(ThisElement,1,17);
AddNewRow(FormValues,"id#Delim#name", "#ThisID##Delim##ThisValue#", Delim);
// AddNewRow is a udf
</cfscript>
</cfif>
</cfloop>

<cfquery name="RecordsToUpdate" dbtype="query">
select FormValues.*
from FormValues, GetCategories
where id = CategoryId
and name <> CategoryName
</cfquery>

<cfloop query="RecordsToUpdate">
update query
</cfloop>

By the way, use of Chr(30) is something I learned from Adam Cameron right here, on Stack Overflow.