Dynamic Multiple Queries

[kramers]

I have a problem. On one page I have several items that will be discounted, they are being passed in form variables to the next page where the query is being executed. So I have form variables that look like this form.itemid1=1, form.itemdiscount1=40, formitemid2=17, form.itemdiscount2=70, etc.

There could be as many as say 20 of these, or as few as 0. I am passing to the next page all of these items, and discounts, as well as the number of items.

My problem is when I run my query to update the table, I can't put my query in a cfloop and run a counter (e.g. update table set columnname=#form.itemdiscount#counter## where othercolumnname=#form.itemid#counter##) Is there some way I can dynamically do this. The only solution I've come up with so far is nasty and involves cfifing over and over.

 

[bowline]

What about doing the loop, but using the loop to build the SQL script, then executing the script once the loop has completed?

Just a thought...seems like you are handling it the right way, just need to narrow it down to one script first.

 

[kramers]

I think I follow, but how do I get around the having a #variable# inside another #variable#?

 

[bowline]

Ah - for that, just use "evaluate". I am doing something similar on one of my sites. Here is how it works:

1. Setup the name of the variable that you are going to want to evaluate
<cfset variables.ResultTextId = "form.q#i#WriteInText">
2. Then evaluate it to get the value contained in that variable
<cfset variables.ResultText = #evaluate(variables.ResultTextId)#>

 

[NorthStarDA]

how about looping through the form scope like so:

<cfset tempArray = ArrayNew(1)>
<cfloop list="#FORM.fieldnames#" index="i">
<cfset temp = ArrayAppend(tempArray, "#i#='#Evaluate(i)#'")>
<cfset ItemList = ArrayToList(tempArray, ",")>
</cfloop>

<cfquery datasource="#Application.DSN#" name="UPDATEQUERY">
UPDATE table SET #ItemList# WHERE criteria
</cfquery>

thats assuming your field names are the same in the form as in the database.

 

[kramers]

Thanks for the additional ideas. I've already used bowline's solution but this way I have options. Thanks. I was really banging my head against a wall for a while there.