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

Maybe back things up a bit first. Can you briefly summarize the purpose of the form in plain english (not code)? (There may be a better approach) — Leigh, Dec 04 '12 at 20:29
Absolutely! Basically the form that uses this cfc is part of a query output that displays all Devices in the database. I want the users to be able to select multiple devices to check in and pass that data to my cfc using one variable. This variable would then be used as part of an insert to complete the check in process. — jorblume, Dec 04 '12 at 20:34
So the form lets a *single* user check out one or more devices? Reason for asking is the code mentioned `form.userid` contains id's (plural) as if many users and devices were involved? If it is only a single user, this may be much easier than you are thinking. — Leigh, Dec 04 '12 at 20:39
form.userid contains the userids attached to the devices, not the person doing the check out. There should only be one person doing the checkout, but he/she should be able to check out multiple machines under different users. Hope that helps. — jorblume, Dec 04 '12 at 20:42
So form.userId should only contain one value while form.isChecked will hold multiple - and all values in form.isChecked are meant to be associated with the single value in form.userId? — Joe C, Dec 04 '12 at 20:45
*he/she should be able to check out multiple machines under different users* Hm.. If Matt's response answers your question, then you are doing something different than described. If there is only a single userID involved, it is even simpler. All you need is an [INSERT / SELECT](http://stackoverflow.com/questions/13508291/coldfusion-mssql-how-to-insert-multiple-rows-with-one-unique-id-in-one-submi/13536203#13536203) — Leigh, Dec 04 '12 at 20:49
Form.userid should be able to handle multiple values if needed, but it might not for every situation. Same with form.deviceid. Should be able to handle multiple values if needed, but not always. I want to pass in multiple values per variable depending on how many machines are being checked in and if they are under different users. An example submission might be form.userid="rob, bob" and form.deviceis="42,43" into my cfc — jorblume, Dec 04 '12 at 20:50
How do you (@jorblume) differentiate which device is supposed to match up to which user? That needs to be figured out before trying to insert the data. — Joe C, Dec 04 '12 at 20:58
How do you know what device is assigned to whom? Is there a limit, like one device per user? So #42 => loaned to "rob" and #43 => loaned to "bob". — Leigh, Dec 04 '12 at 20:58
I guess I had planned on it being sequential. since the loop goes through each value per iteration, each device should match up with its username based on its place in the list. Since I make people fill out the form completely, each device number should have all its matching fields. IE form.ischecked: 38,39 form.userid: bob, rob the loop should match up 38 with bob and 39 to rob. If you have a better idea I would love to hear it! — jorblume, Dec 04 '12 at 21:03
So, "Rob,Rob,Bob" and "38,39,40" would map to Rob getting 38 & 39 and Bob getting 40? That's fine, but the answer you accepted won't handle that. — Joe C, Dec 04 '12 at 21:08
@phantom42 Correct, which is why I'm having issues implementing it at the moment :( @@Leigh That was the intention, but it seems to be more difficult than I anticipated. — jorblume, Dec 04 '12 at 21:11
@jorblume - That is why we wanted you to elaborate on the requirements - *before* suggesting code. I had a feeling it was not as simple as it appeared .. (Edit) At this point you may want to open a new thread. Post the current form code and explain what you *want* it to do, and we can suggest the best way to go about it. It definitely sounds possible, but we need to know all of the requirements up front. — Leigh, Dec 04 '12 at 21:14
@jorblume - It is not a problem. I just think you may have started backward on this one. It sounds like you were really asking "how do I design my form to get xyz result?". For that it is better to start by posting the db tables, and your existing form code, and go from there. — Leigh, Dec 04 '12 at 21:24
We have two answers so, only if you say you are interested, I'll show you a potentially more efficient way to do this. Basically you put the loop inside the query instead of the query inside the loop. — Dan Bracuk, Dec 05 '12 at 01:20
Solidify the logic first, then worry about improving the sql. None of our suggestions will work reliably with multiple user id's if `form.isChecked` is a checkbox that may not always exist. — Leigh, Dec 05 '12 at 04:28
I'm terribly sorry for bothering you guys, here is an updated question with more info and a picture, hope this helps http://stackoverflow.com/questions/13729320/design-advice-multi-user-multi-device-submission-site — jorblume, Dec 05 '12 at 17:56

score 7 · Answer 1 · answered Dec 04 '12 at 20:38

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>

Okay that was wayyyy simpler than what I was trying to do. Cheers mate. — jorblume, Dec 04 '12 at 20:48

score 2 · Accepted Answer · answered Dec 04 '12 at 20:36

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>

To be safe, you should also wrap that insert query in a conditional making sure that form.userId has at least one value in it. — Joe C, Dec 04 '12 at 21:14
@jorblume - Nothing against the suggestions made, but given that your requirements keep changing, I have serious doubts any of the code snippets here will behave as you expect - consistently. We simply do not have enough information about your input form and requirements. — Leigh, Dec 04 '12 at 21:35
That's a fair statement. I wrote this up based on how I understood the requirements in the original post. It seems most of my assumptions were correct, but you'd do best to really nail down the form logic and design and revisiting once that's done. — Joe C, Dec 04 '12 at 21:39
Very nifty solution. However this only works in `SQL2008` (or above). See [this](http://stackoverflow.com/a/2624775) get an idea how you can get it to work in `SQL2005`. — MadushM, Oct 23 '13 at 20:42

Coldfusion SQL Insert Loop

2 Answers2

Linked