Let's suppose that you needed to provide a customer with a page that would allow them to update personnel records; 20 records at a time to set a value from a drop-down. On the page would be a table with 20 names and 20 identical HTML SELECT drop downs. When you submit the form, all the selects combine into one long list.
Here is how to handle looping through that returned list to only update those records that have new data selected:
In this example I am submitting the form page back to itself so the top of my ColdFusion page will be a conditional statement checking for the existence of the FORM structure. You can target specific elements of the form, not just the FieldNames.
<cfif StructKeyExists(FORM, "FieldNames")>
<cfset LoopPosition=0>
<cfloop index="LoopIndex" list="#FORM.FruitID#">
<cfset LoopPosition=LoopPosition+1>
<cfif LoopIndex NEQ 0>
<cfquery name="SetFruit" datasource="MyDatabase">
UPDATE TablePerson
SET FruitID = #LoopIndex#
WHERE PersonID = #ListGetAt(FORM.PersonID, LoopPosition)#
</cfquery>
</cfif>
</cfloop>
</cfif>Next, we include two different queries. The first query is the succession set of 20 employees to update. The query is designed to allow the user to start at the beginning and progress through all employees and set the value from a drop down select. The second query is used to populate the select; in this case I'm setting each employee to a favorite fruit.
<cfquery name="GetPerson" datasource="MyDatabase">
SELECT TOP (20) PersonID, PersonName
FROM TablePerson
WHERE FruitID IS NULL
ORDER BY PersonName
</cfquery>
<cfquery name="GetFruit" datasource="MyDatabase">
SELECT FruitID, FruitName
FROM ListFruit
ORDER BY FruitName
</cfquery>Last section is the actual form, fields, and submit button. The form action is set to submit back to itself no matter what the name of the CFM file using CGI.SCRIPT_NAME. The results from the first query build 20 rows at a time because of the TOP (20) limitation I placed on the query. The CFLOOP builds the drop down of fruit by ID and name. I have to use a CFLOOP while inside the CFOUPUT because I'm already progressing through the GetPerson query.
<form action="<cfoutput>#CGI.SCRIPT_NAME#</cfoutput>" method="post" name="FruitForm">
<table border="1" cellpadding="2" cellspacing="0">
<tr>
<th>NAME</th>
<th>FRUIT</th>
</tr>
<cfoutput query="GetPerson">
<tr>
<td>#GetPerson.PersonName#</td>
<td>
<input name="PersonID" type="hidden" value="#GetPerson.PersonID#" />
<select name="FruitID">
<option value="0"></option>
<cfloop query="GetFruit">
<option value="#GetFruit.FruitID#">#GetFruit.FruitName#</option>
</cfloop>
</select>
</td>
</tr>
</cfoutput>
<tr>
<td colspan="100%" align="right">
<input name="SubmitFruit" type="submit" value="SUBMIT" />
</td>
</tr>
</table>
</form>Once you submit the form, the conditional statement at the top checks to see if the FORM structure exists and sets a LoopPosition to zero. There isn't a CurrentRow for looping through a list, so as we loop over the contents of FORM.FruitID we update our current position by one.
If the index is not equal to zero (the value of the first OPTION in our form, and not set by the query) then we need to update the database. We have two lists inside two form field values; FruitID contains a list of any selections made, and PersonID is unique identifier for each PersonName.
The LoopPosition gives us the position inside the list of FORM.FruitID numbers that are not zero, which we can then use to determine which list position to get from the list of FORM.PersonID and perform the update.
If you find this post useful please leave a comment and let me know how you used the information.
