ColdFusion SQL插入循环 [英] Coldfusion SQL Insert Loop

问题描述

基本上，我传递一个单一变量下的多个值

例如，ischecked是我用来传递设备值的变量。如果我要选择两个设备，请按提交并转储变量＃form.ischecked＃在我的处理页面，我会得到一个值，例如41,42。我需要一种方法来拆分这些值，并认为一个cfloop和插入将是完美的。

这一切都在cfc中完成，如果这很重要。

 < cfset devicearray = ArrayNew（1）> 
< cfset temp = ArrayAppend（devicearray，#ischecked＃）> 
< cfset test = ArrayToList（devicearray，，）> 
< cfset length = ListLen（test）> \ 
 \\this循环需要选择的设备数量，并输出列表的长度。 
  

我使用这个来确定插入循环应该运行多长时间。
我也可以只检查数组的长度，但我也将使用列表的另一个目的。

 < cfset devicetest =＃form.ischecked＃> 
 
< cfset usertest =＃form.userid＃> 
 
 \\form.ischecked是包含设备ID的变量
 
 \\form.userid是包含用户ID的变量
 
< cfquery name =loopquerydatasource =Test> 
< cfloop from =1to =＃length＃index =i> 
 
 \\loop从1到length，前面指定的设备数量
 
 INSERT INTO贷款（DeviceID，UserID）
 VALUES #Evaluate（devicetest＃i＃）＃，＃Evaluate（userID＃i＃）＃）
< / cfloop> 
< / cfquery> 
  

所以基本上就是我坚持，循环遍历的值，但它寻找devicetest1而不是设备测试（因为索引），但我不能为我的生活弄清楚如何传递值，以便它分别挑选每一个。

我看过一些例子，人们已经附加了索引（i）的值，然后用它来插入，但是没有真正理解它是如何工作的。

感谢，
乔丹

解决方案

不理解 deviceArray 的要点是什么。您说 form.isChecked 已经是一个包含设备ID列表的列表。如果它是从表单提交进来，它已经以逗号分隔。

因此，除了 listlen，没有真正需要做任何事情

您的代码可能不在上下文中，但要完成，请确保您 param form.isChecked 和 form.userID

 < cfparam name =form.isCheckeddefault => 
< cfparam name =form.userIDdefault => 
  

此时，我还会亲自做一些错误检查，

 < cfif listLen（form.isChecked）NEQ listLen（form.userID）> 
<！--- abort or do something else ---> 
< / cfif> 
  

没有必要为每个循环写一个单独的插入。大多数数据库将允许您使用一个语句插入多个行。因为你只是循环遍历 form.userID 和 form.isChecked 中的每个值，你可以a listGetAt - 确保使用 cfqueryparam 来清理您的数据输入。注意，我只是假设你的 deviceId 和 userId 值是整数。

 < cfquery name =insertdatasource =test> 
 INSERT INTO Loan（DeviceID，UserID）
 VALUES 
< cfloop from =1to =＃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> 
  

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

解决方案

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>

这篇关于ColdFusion SQL插入循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！