You Dream it...We Build it... 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 |
Beginning Attempt
Now a beginning programmer might do something like this:
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:
<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:
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:
<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.
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.
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/...
<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>