How to make dynamic form fields update?

[EvolvedDSM]

This is going to be an ugly post, fair warning! :)

I have a form that gets it's labels and text input field names dynamically from a database. Rather than having the form hardcoded, we are able to add new columns to a table and the form automatically adds new labels and fields via CFQUERY.

When the form is completed and they submit (method post), the database is supposed to update. However, because our input field names are being populated via query, we can't write a static update query when we add new form items.

So, I do have a solution to this (in my head), but I'm stuck at a bit of logic and am having trouble getting it out in code. I'm looking for a way to write #FORM.#fieldname## (here is where it gets ugly). Since the form submits by post method, I am left with a bunch of FORM.fieldname's on my action page (and fieldname is generic for what the actual variables are).

So here's how I am updating:
I have a table (call it table1) that contains a column of all the column names of my data table (table2). This table is used for query purposes only, no data is updated here from the form.
I query table1 to call all the names of the columns in table2.
<cquery datasource="exdb" name="exname">
SELECT columnName
FROM table1
</cfquery>
Then I write the update query which will update items as they are looped using CFOUTPUT:
<CFOUTPUT QUERY="exname">
<cfquery datasource="exdb" name="exupdate">
UPDATE table2
SET #columnName# = #FORM.<columnName>#
WHERE itemID = #FORM.itemID#

This is my dilemma. As you can see, the cfoutput is a loop.. so let's take the first item in the loop, call it "name1". So our update query says SET name1 = #FORM.name1#, and this will update the table with whatever was submitted in name1's input field. The cfoutput loops again, this time it's name2. SET name2 = #FORM.name2#. Now the form does include a hidden input of itemID to specify where the table should be updating, as you can see above. So, now that you see how this works, my problem is getting #FORM.<columnName># to be FORM.name1, FORM.name2, etc.
In Coldfusion you would write #FORM.variable# to call a FORM item. However, since the variable is pretty much undefined and we use a query to populate it, we need to do #FORM.#variable## where #variable# needs to be resolved before #FORM._______#

Are you with me on this? It's very complex in explanation, but if you need more info, I can provide it more clearly. Let me know what you guys think. Really, I have the solution, but getting #FORM.#columnName## to resolve is another problem. I think what i need is to figure out the order of operation for that statement, so #columnName# resolves first.

[insuractive · Accepted solution]

You'll want to use the scope structure notation when dealing with your form variables. The variable Form.SomeVar can also be written as Form["SomeVar"]. Using this syntax, you can do something like this:

UPDATE table2
SET #columnName# = #FORM[columnName]#
WHERE itemID = #FORM.itemID#

You may also want to look into the following to help optimize your code:
1) check out how to use <cfqueryparam> - it might speed your code up a bit.
2) depending on the DB you are using, you may be able to combine all of your UPDATE SQL commands inside 1 <cfquery> </cfquery> block. This will allow you to do all your database actions with 1 DB connection, instead of a bunch (works in MSSQL, not sure about Oracle or MySQL)

[Dan_Bracuk]

You can loop through a form. Here is some code I wrote a couple of days ago. It works. Bear in mind that addNewRow is a udf, so it won't be in the cfml reference manual.

<cfloop collection="#form#" item="idx">
<cfscript>
if (left(idx, 4) is "drug"){
ThisQty = form[idx];

if (ThisQty gt 0) {
ThisDrugNum = mid(idx, 5, len(idx) - 4);

x= addNewRow(drugs_submitted,"drugid,qty","#ThisDrugNum#,#ThisQty#");
} // thisQty gt 0
} // form field starts with drug
</cfscript>
</cfloop>

[EvolvedDSM]

Thanks Michael. I looked at your response and figured I would just give it a try and hope for the best, but it WORKS! And thank you Dan, I'll give your loop a try and see how it works out for me.