Here is a quick follow up to a blog entry I did back in February: Ask a Jedi: Updating multiple records at once. I had more than one person ask me to modify the code from that example to handle multiple fields per record. The template I had created only allowed you to edit the name. Here is a simple example that builds upon the first version and allows you to edit the description and price as well.

First, let's modify the display a bit to show the additional fields:

<cfquery name="getArt" datasource="cfartgallery" maxrows="10"> select artid, artname, description, price from art </cfquery>

<form action="test2.cfm" method="post"> <table> <tr> <th>Name</th> <th>Description</th> <th>Price</th> </tr> <cfloop query="getArt"> <tr valign="top"> <cfoutput> <td><input type="text" name="art_#artid#" value="#artname#" /></td> <td><textarea name="desc_#artid#">#description#</textarea></td> <td><input type="text" name="price_#artid#" value="#price#"></td> </cfoutput> </tr> </cfloop> </table> <input type="submit" name="save" value="Save" /> </form>

Compared to the first version, this one gets additional columns in the query and displays them in the table. Notice that I used a similar naming scheme for my two new columns: desc_X and price_X. X represents the ID (primary key) of the art record.

Saving these new columns just requires a bit more code:

<cfif structKeyExists(form, "save")> <cfloop item="field" collection="#form#"> <cfif findNoCase("art_", field)> <cfset artid = listLast(field, "_")> <cfset desc = form["desc_" & artid]> <cfset price = form["price_" & artid]> <cfquery datasource="cfartgallery"> update art set artname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">, description = <cfqueryparam cfsqltype="cf_sql_longvar" value="#desc#">, price = <cfqueryparam cfsqltype="cf_sql_numeric" value="#price#" scale="2"> where artid = <cfqueryparam cfsqltype="cf_sql_integer" value="#artid#"> </cfquery> </cfif> </cfloop> </cfif>

Since I already grabbed the ID record from the art_X field, I could then use that to grab both the description and price values. I then just added those two fields to the query. The code could use a bit of validation. In the past when I've done something like this I've used an array of results. So for each update I could display which saved ok and which did not, or perhaps just output the rows that didn't update properly. You could also add jQuery validation to the form in about five minutes as well.

Shoot - I just tried and it took me 6 minutes. Dang. Here is the full version with jQuery validation added.

<cfif structKeyExists(form, "save")> <cfloop item="field" collection="#form#"> <cfif findNoCase("art_", field)> <cfset artid = listLast(field, "_")> <cfset desc = form["desc_" & artid]> <cfset price = form["price_" & artid]> <cfquery datasource="cfartgallery"> update art set artname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">, description = <cfqueryparam cfsqltype="cf_sql_longvar" value="#desc#">, price = <cfqueryparam cfsqltype="cf_sql_numeric" value="#price#" scale="2"> where artid = <cfqueryparam cfsqltype="cf_sql_integer" value="#artid#"> </cfquery> </cfif> </cfloop> </cfif>

<cfquery name="getArt" datasource="cfartgallery" maxrows="10"> select artid, artname, description, price from art </cfquery>

<html>

<head> <script src="/jquery/jquery.js"></script> <script src="/jquery/jquery.validate.js"></script> <script> $(document).ready(function(){ $("#myForm").validate({ rules: { <cfoutput query="getArt"> art_#artid#:"required", desc_#artid#:"required", price_#artid#: { required:true, number:true, min:0 }, </cfoutput> } }); }); </script> </head>

<body>

<form action="test2.cfm" method="post" id="myForm"> <table> <tr> <th>Name</th> <th>Description</th> <th>Price</th> </tr> <cfloop query="getArt"> <tr valign="top"> <cfoutput> <td><input type="text" id="art_#artid#" name="art_#artid#" value="#artname#" /></td> <td><textarea id="desc_#artid#" name="desc_#artid#">#description#</textarea></td> <td><input type="text" id="price_#artid#" name="price_#artid#" value="#price#"></td> </cfoutput> </tr> </cfloop> </table> <input type="submit" name="save" value="Save" /> </form>