Dynamically update multiple rows in one form

This is one of those functions that I have avoided for a long time.

I decided that is was time for me to tackle it so here goes.

Scenario:

You have a form with multiple rows.

You need to perform CRUD on the records all in one place. (Brain starting to hurt yet?)

Don't worry. It's fairly easy.

Step 1 - Retrieve your records:

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

Step 2 - Build Your Form.

#currentRow# is used to give each row a unique identifier and I pass the row count as a hidden field to use to loop through the fields once submitted (see red highlights below).

<form action="formLoopUpdate.cfm" method="post">
  <table>
    <cfloop query="getArt">
      <tr> <cfoutput>
          <td>
          <input type="text" name="art#currentRow#" value="#artname#" />
          <input type="hidden" name="artID#currentRow#" value="#artID#" />
          </td>
        </cfoutput> </tr>
    </cfloop>
  </table>
  
  <input type='hidden' name='totalNumberOfRecords' value='<cfoutput>#getArt.recordCount#</cfoutput>'>
  
  <input type="submit" name="save" value="Save" />
</form>

3. Submit and save

No need for commentary here. It's explains itself.

<cfif structKeyExists(form, "save")>
   
        <cfloop from="1" to="#form.totalNumberOfRecords#" index="counter">
          <!--- extract the values of the dynamic form fields --->
          <cfset art   = form["art"   & counter]>
          <cfset artID   = form["artID"   & counter]>
    
            <cfquery datasource="cfartgallery"> 
                    update art 
                    set artname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#art#"> 
                    where artid = <cfqueryparam cfsqltype="cf_sql_integer" value="#artid#"> 
            </cfquery> 
    
         </cfloop>
    
    
    </cfif>


That's it.

Let me know what you think or how this could have been done differently.

-MB



Another example:



<cfloop from="1" to="#form.totalNumberItems#" index="counter">


<!--- update category order --->

<cfif isdefined("form.categoryOrder#counter#")> 


<cfset categoryOrder = form["categoryOrder"   & counter]>

<cfset categoryID   = form["categoryID"   & counter]>


<cfquery name="updateCategoryOrder" datasource="#application.var#"> 

update Checklist_Category 

set sortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#categoryOrder#"> 

where categoryID = <cfqueryparam cfsqltype="cf_sql_integer" value="#categoryID#"> 

</cfquery>  


</cfif>    



<!--- update checklist item order --->

<cfif isdefined("form.checkListOrder#counter#")> 


<cfset checkListOrder = form["checkListOrder"   & counter]>

<cfset checkDetailID    = form["checkDetailID"    & counter]>


<cfquery name="updateCheckListOrder" datasource="#application.var#"> 

update Checklist_Admin 

set sortOrder = <cfqueryparam cfsqltype="cf_sql_integer" value="#checkListOrder#"> 

where checkDetailID = <cfqueryparam cfsqltype="cf_sql_integer" value="#checkDetailID#"> 

</cfquery> 


</cfif>

</cfloop>


</cfif>