Coldfusion SQL Insert Loop

Question

Got stuck with an issue and thought I might see if anyone had any ideas on how to fix it.

Basically, I pass in multiple values under a singular variable, and I want to use a loop to extract each individual value and insert it at the same time.

For example, ischecked is the variable I use to pass in device values. If I were to select two devices, press submit and dump the variable #form.ischecked# in my processing page, I would get a value that says 41,42 for example. I need a way to split these values up, and figured a cfloop and insert would be perfect for that.

This is all done in a cfc, if that matters.

        <cfset devicearray = ArrayNew(1)>
        <cfset temp = ArrayAppend(devicearray, #ischecked#)>
        <cfset test = ArrayToList(devicearray, ",")>
        <cfset length= ListLen(test)>\
        \\this loop takes the amount of devices selected, and outputs the length of the list. 

I use this to find out how long the insert loop should go for. I could have also just checked the length of the array originally, but I was going to use the list for another purpose as well.

    <cfset devicetest = #form.ischecked#>

        <cfset usertest = #form.userid#>

        \\form.ischecked is the variable that contains the device IDs

        \\form.userid is the variable that contains the User IDs

        <cfquery name="loopquery" datasource="Test">
        <cfloop from="1" to="#length#" index="i">

        \\loop from 1 to "length", the number of Devices selected as specified earlier

        INSERT INTO Loan (DeviceID, UserID)
        VALUES ("#Evaluate("devicetest#i#")#","#Evaluate("userID#i#")#" )
        </cfloop>
        </cfquery>

So basically that's where I'm stuck at, the loop goes over the values but it looks for devicetest1 instead of device test (because of the index), but I can't for the life of me figure out how to pass in the values so that it picks out each one individually.

I've seen some examples where people have appended the index (i) with the value, then used it to insert, but didn't really understand how it would have worked.

Thanks, Jordan

Answer 1

I think you're complicating this quite a bit. The below will work to loop through the list in your form variable.

<!--- dummy data --->
<cfset form.userid = 75>
<cfset form.ischecked = '46,47'>

<cfloop list="#form.ischecked#" index="i">  
 <cfquery name="loopquery" datasource="Test">
 INSERT INTO Loan (DeviceID, UserID)
 VALUES (
    <cfqueryparam cfsqltype="cf_sql_integer" value="#i#">,
    <cfqueryparam cfsqltype="cf_sql_integer" value="#form.userid#">
    )
 </cfquery>
</cfloop>

Answer 2

I'm not understanding what the point of deviceArray is. You say form.isChecked is already a list containing a list of device id's. If it's coming in from a form submission, it is already comma delimited.

As such, there is no real need to do anything but a listlen to get the length of it.

Your code may be taken out of context, but to be complete, make sure you param form.isChecked and form.userID

<cfparam name="form.isChecked" default="">
<cfparam name="form.userID" default="">

At this point, I would also personally do some error checking to make sure that the lengths of the two variables match.

<cfif listLen(form.isChecked) NEQ listLen(form.userID)>
    <!--- abort or do something else --->
</cfif>

There's no need to actually write a separate insert for each loop. Most databases will allow you to insert multiple rows with one statement. Since you're just looping through each of the values in form.userID and form.isChecked, you can just do a listGetAt - making sure to use a cfqueryparam to sanitize your data inputs. Note that I just assumed your deviceId and userId values are integers. Change those as necessary.

<cfquery name="insert" datasource="test">
    INSERT INTO Loan (DeviceID, UserID)
    VALUES
    <cfloop from="1" to="#listLen(form.userID)#" index="i">
       <cfif i GT 1>
           ,
       </cfif>
       (
           <cfqueryparam value="#listGetAt(form.isChecked,i)#" cfsqltype="CF_SQL_INTEGER">,
           <cfqueryparam value="#listGetAt(form.userID,i)#" cfsqltype="CF_SQL_INTEGER">
       )
    </cfloop>
</cfquery>