ColdFusion Muse

Forms, Datasets, Looping and Updating - a Simple Example

Recently my good friend and colleague Mike Klostermeyer - who everyone would recognize as a brilliant programmer and guru if he would just learn to blog - suggested that I include some simpler posts among my obscure troubleshooting play-by-plays. Here's one that most CF programmers have had to overcome at some point. Now before we go on I have to point out that there are 4 or 5 ways to do this - not counting things like Hibernate and the "black box" stuff that ships with many frameworks. What I'm illustrating here is the capabilities of the language. Moreover, if you have to support any legacy code (as 95% of us do) then you don't always have other options. You have to find a solution that works in context. With that in mind let's proceed.

Our problem stems from a typical form you might create as a part of an admin toolkit. Let's suppose you select multiple records from the database and load them into the form. When you submit the form you want all the values of the form fields to "update" the values in the table. Simple right? To start with we have 2 assumptions:

  • Each row has a primary key - let's make it an "identity" field called "id" for simplicity sake.
  • On submission we are simply going to update every record with the values. It's a small data set so I'm not optimizing here. I don't care if a value has changed or not - I'm just going to update it.

Now let's say we have the following data from the table "famousLargeMouths":

ID FirstName LastName
1 Mick Jagger
1 Angelina Jolie
The first task is to select all these records, load them into a form, and update them in a single action on submit.

Beginning Attempt

Now a beginning programmer might do something like this:

<input type="hidden"
    name="totRecords"
    value="#qryLips.recordcount#"/>

    
<cfoutput query="qryLips">
ID: <input type="text" name="id" value="#id#"/>
firstname: <input type="text" name="firstname" value="#firstname#"/>
firstname: <input type="text" name="lastname" value="#lastname#"/><br>
</cfoutput>
<input type="submit" name="go"/>


That would give us a form that looks like this:

ID: firstname: lastname:
ID: firstname: lastname:

Submitting such a form would give you the following output:

struct
FIRSTNAME Mick,Angelina
ID 1,2
LASTNAME Jagger,Jolie


To handle the values submitted we would do the following:

<cfloop from="1" to="#form.totRecords#" index="ct">
<cfquery name="update" datasource="mydsn">
    UDPATE     famousLargeMouths
    SET        firstname = '#listgetat(form.firstname,ct)#',
            lastname = '#listgetat(form.lastname,ct)#'
    WHERE    id = #listgetat(form.id,ct)#'>
    
</cfquery>    
</cfloop>


That's pretty easy right? Anyone see an issue? Let's suppose we add an additional record for "Robert Downey, Jr.". Now our form looks like this:

ID: firstname: lastname:
ID: firstname: lastname:
ID: firstname: lastname:

Perhaps you see the problem right away. Robert Downey, Jr. has a comma in his name (We probably don't "need" the comma, but let's just say...). That means when we submit the results are going to look like this:

struct
FIRSTNAME Mick,Angelina,Robert
ID 1,2,3
LASTNAME Jagger,Jolie,Downey, Jr.

Anyone? Bueller? The addition of the comma after "Downey" means the list length for "lastname" is now 4 and not 3. The code we have created is going to truncate Mr. Downey's name (though not of course his ego). We could add some JavaScript to tease out commas and replace them with pipe symbols before submission and then add a replace function to put them back in for our update query. That would work I suppose, but I suggest that we get away from the this listGetAt() approach altogether. It seems error prone.

The Fix

Here's my approach to this simple problem. Note, as I said before this is just one way to solve this issue. The point here is to get you thinking and add another arrow to your CF quiver. My approach would be to use independent form names. Instead of trusting a single form name and field to carry all the data for a database column, I'm going to create separate names that represent the "cell" (both the row and the column). Here's my new sample code:

<input type="hidden"
    name="allIds"
    value="#Valuelist(qryLips.id)#"/>

    
<cfoutput query="qryLips">
firstname: <input type="text" name="firstname_#id#" value="#firstname#"/>
firstname: <input type="text" name="lastname_#id#" value="#lastname#"/><br>
</cfoutput>
<input type="submit" name="go"/>

Now, when I submit my form the form values look like this:

struct
allIds 1,2,3
Firstname_1 Mick
Firstname_2 Angelina
Firstname_3 Robert
Lastname_1 Jagger
Lastname_2 Jolie
lastName_3 Downey, Jr.

I've managed to encapsulate each value in its own form field which makes the use of commas a non issue. Now all I need to do is loop through it and update like so:

<cfloop list="#form.allids#" index="id">
<cfquery name="update" datasource="mydsn">
    UDPATE     famousLargeMouths
    SET        firstname = '#form["firstname_" & id]#',
            lastname = '#form["lastname_" & id]#'
    WHERE    id = #id#    
</cfquery>    
</cfloop>

This keeps me from various search and replace expressions. It also kind of reflects more clearly the "Row and column" organization of a table in the DB - so I personally find it easier to understand from the outset.

Ok, Muse readers, I know you have your keyboard at the ready. Let's hear how you solve this particular problem and why you think it's a better approach. I look forward to comments as always.

Comments
Snake's Gravatar I'd just use an EXTJS grid :-)
# Posted By Snake | 1/5/11 2:36 PM
Phillip Senn's Gravatar I cfloop list="#form.FieldNames#" Index="FieldName"
and then examine the contents of FieldName, but I like your method of using form.AllIDs.
A bit Off Topic is working with checkboxes. They really threw me for a loop until I wrapped my head around how to use them correctly.
If they're named the same, then the field is a list of the options checked. If none are checked, then the field doesn't exist at all.
# Posted By Phillip Senn | 1/5/11 3:01 PM
Kurt Bonnet's Gravatar Mark,

I do something similar to what you do.

I use the field naming convention of:
<prefix>_<field/property Name>_<rowNumber>


so

<code>
Row1
<input type="hidden" name="expenses_id_1" value="546" >
<input type="text" name="expenses_description_1" value="Travel" >
<input type="text" name="expenses_amount_1" value="12.50" >

Row2
<input type="hidden" name="expenses_id_2" value="876" >
<input type="text" name="expenses_description_2" value="Meals" >
<input type="text" name="expenses_amount_2" value="75.46" >
</code>

I have a reusable parsing/filtering routine where I just pass in the prefix I want to filter on, it loops over form or url collection, and it returns an array of structs like so:

<code>
[
{
id: 546,
description: "Travel",
amount: 12.50
}
,
{
id: 876,
description: "Meals",
amount: 75.46
}
]
</code>

What I like about this approach is it gets you AWAY from the form/url structures real fast and into a more standard/consistent data structure really quick. I hate dealing with field names like "<fieldname>_<token>" to get a value in my PROCESSING LOGIC. Now that I'm looking at this routine again, the data returned kind of mirrors a query. I suppose I could have my routine return a QUERY object instead of an array of structs, or struct of structs. Arrays/structs have always got the job done fast and efficiently though.

Anyway this approach is also especially useful when you're dynamically adding sets of fields to a form where you don't know the IDs of the records ahead of time, and you may need the records (on the server side) in the order they were entered via the GUI.


I will SOMETIMES use the ID field as the 3rd token in the field name depending on the particular needs of the form and processing logic. I simply pass in a parameter to my filtering/parsing routine to indicate I'd like it to return a STRUCT of STRUCTS keyed by the 3rd field token in which case it will return:

<code>
<input type="hidden" name="expenses_id_546" value="546" >
<input type="text" name="expenses_description_546" value="Travel" >
<input type="text" name="expenses_amount_546" value="12.50" >

...


{
546: {
id: 546,
description: "Travel",
amount: 12.50
}
,
876: {
id: 876,
description: "Meals",
amount: 75.46
}
}
</code>


In the above case you COULD of course leave out the ID field altogether since you have it's value in the top level structure already, I just left it in in my example.



I know Brian Kotek has a utility for handling a scenario such as this also. See URLs below. I like the concept of it very much, however, there's just something about using field names like "employee[1].mode[1]" that I'm having a hard time getting over. One really nice thing about Brian's approach is that it handles deep nesting really well, while your technique and mine only handles shallow nesting easily. I'm in the middle of re-evaluating how I handle these scenarios, I'm going to be looking at Brian's approach again. Maybe I'll get over the field naming conventions this time :)

http://formutils.riaforge.org/
http://www.briankotek.com/blog/index.cfm/2007/9/4/...
# Posted By Kurt Bonnet | 1/6/11 12:16 AM
luis  Samul's Gravatar Hi , i wrote the following code :

<cfquery name="qryLips" datasource="#APPLICATION.dataSource#">
SELECT id, firstname, lastname
FROM famousLargeMouths
</cfquery>

<cfform>
<cfinput type="hidden"
name="allIds"
value="#Valuelist(qryLips.id)#"/>

<cfoutput query="qryLips">
firstname: <cfinput type="text" name="firstname_#id#" value="#firstname#"/>
lastname: <cfinput type="text" name="lastname_#id#" value="#lastname#"/>
</cfoutput>

<cfinput type="submit" name="go"/>

</cfform>

<cfloop list="#form.allids#" index="id">
<cfquery name="update" datasource="#APPLICATION.dataSource#">
UDPATE famousLargeMouths
SET   firstname = '#form["firstname_" & id]#',
lastname = '#form["lastname_" & id]#'
WHERE   id = #id#
</cfquery>
</cfloop>


im getting the following error :

Error Executing Database Query.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UDPATE famousLargeMouths SET   firstname = 'yon', lastname = 'vanerg' at line 1

The error occurred in C:httpdocs/formsmultiple2.cfm: line 50
48 : SET   firstname = '#form["firstname_" & id]#',
49 : lastname = '#form["lastname_" & id]#'
50 : WHERE   id = #id#
51 : </cfquery>
52 : </cfloop>
# Posted By luis Samul | 2/12/13 12:16 AM
chad's Gravatar the final code sample in the above blog and the most recent comment above mine will result in errors because they have the word "UDPATE" (misspelled) rather than "UPDATE"
# Posted By chad | 7/31/18 3:23 PM
chad's Gravatar this code is verified to work (provided you set up your database and tables names correctly):

FORM PAGE:
<cfquery name="qryLips" datasource="yourDSname">
SELECT *
FROM famousLargeMouths
ORDER BY id ASC
</cfquery>

<p>
<cfoutput query="qryLips">
#firstname# - #lastname# #Valuelist(qryLips.id)#<br>
</cfoutput>

<p>
<cfform method="post" action="actionpage.html">

<cfoutput query="qryLips">
<input type="hidden" name="allIds" value="#Valuelist(qryLips.id)#"/>
firstname: <input type="text" name="firstname_#id#" value="#firstname#" />
firstname: <input type="text" name="lastname_#id#" value="#lastname#" /><br>
</cfoutput>

<br>

<input type="Submit" name="go" value="Click Here To Update">
</cfform>

ACTION PAGE:

<cfloop list="#form.allids#" index="id">
<cfquery name="update" datasource="yourDSname">
UPDATE famousLargeMouths SET firstname = '#form["firstname_" & id]#', lastname = '#form["lastname_" & id]#'
WHERE id = #id#
</cfquery>
</cfloop>

SUBMISSION COMPLETE
# Posted By chad | 7/31/18 4:44 PM
chad's Gravatar oops -
firstname: <input type="text" name="lastname_#id#" value="#lastname#" /><br>

should be:

lastname: <input type="text" name="lastname_#id#" value="#lastname#" /><br>
# Posted By chad | 7/31/18 4:45 PM



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.