本文介绍了Coldfusion从动态表单复选框创建数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个动态表单与多个复选框和提交我想运行 CFSTOREDPROC 在另一个 cfloop 使用只有复选框的值改变了状态。



下面是一个粗略的概念,我想测试,但我相信我的方式,我创建我的数组将有问题。如果任何人可以提供可能的解决方案的反馈,我将不胜感激。



HTML / CF FORM:

 < form action = self.cfmmethod =postname =permissions> 
< input type =hiddenname =User_IDvalue =< CFOUTPUT>#User_ID#< / CFOUTPUT>>
< table>
< CFLOOP QUERY =getNthPermission>
< tr>< td>#getNthPermission.Permission_Name#< / td>< td>< input type =checkboxvalue =#getNthPermission.Permission_ID#name =#getNthPermission.Permission_Name #< CFIF LISTVALUECOUNT(VARIABLES.UserPermission_ID_List,getNthPermission.Permission_ID)NEQ 0> CHECKED< / CFIF>< / td>< / tr>
< / CFLOOP>
< / table>
< input type =submitname =submitvalue =Update>
< / form>

COLDFUSION动作:

 < CFSET VARIABLES.Permission_ID_List = ValueList(getUserPermission.Permission_ID)> 
< cfset changed_permissions = ArrayNew()>
< CFLOOP QUERY =getNthPermission>
//如果在表单创建时检查,但用户未选中该表单,则将其添加到我的数组中。
< CFIF LISTVALUECOUNT(VARIABLES.UserPermission_ID_List,getNthPermission.Permission_ID)NEQ 0 AND!IsDefined(FORM。#getNthPermission.Permission_Name#)>
< cfset changed_permissions [getNthPermission.Permission_ID]>
< CFELSEIF LISTVALUECOUNT(VARIABLES.UserPermission_ID_List,getNthPermission.Permission_ID)EQ 0 AND IsDefined(FORM。#getNthPermission.Permission_Name#)>
//如果在构建表单时未选中,但用户检查该表单将其添加到我的数组。
< cfset changed_permissions [getNthPermission.Permission_ID]>
< / CFIF>
< / CFLOOP>

//现在循环刚刚创建的数组,并传递存储过程我的值

 < CFLOOP from =1to =#arrayLen(changed_permissions)#index =i> 
< CFSTOREDPROC DATASOURCE =#MYDB_DSN#PROCEDURE =Update_UserPermission>
< CFPROCPARAM DBVARNAME =@ PermissionListVALUE =#changed_permissions [i]#TYPE =INCFSQLTYPE =cf_sql_longvarchar>
< CFPROCPARAM DBVARNAME =@ User_IDVALUE =#FORM.User_ID#CFSQLTYPE =cf_sql_integer>
< / CFSTOREDPROC>
< / CFLOOP>

更新



我正在使用的数据库配置,我不是设置的一部分,它有这些表:




  • 权限表,ID)

  • 用户表(user_name,ID)

  • User_Permissions表(Permissions_ID,User_ID) - 包含用户有权访问的每个权限的条目。



未选择,我添加一个新条目或删除一个。

解决方案

三个表是一个很好的设置。我害怕它是一个单一的表或值存储为csv lists {shudder}。仍然无法可视化您的表单真实很好,但..从你所描述的听起来像是更简单的做一个查询,而不是循环。



首先,给予复选框相同的名称,如Permission_IDList,因此id将作为列表提交。然后使用该ID列表与以下选项之一:



选项1:DELETE / INSERT ALL



我通常使用简单的的方法是删除所有现有用户权限。然后使用INSERT / SELECT插入权限。使用INSERT / SELECT的两个大的优点是它消除了循环的需要,并且还提供内置的验证。



这是最简单的选项之一,但它不像选项2那样精确,因为它每次都删除所有记录。所以技术上,它可能做一些工作比在许多情况下需要。虽然除非你处理大量的记录,差异通常可以忽略不计。沿着这些线的东西(未测试):

  ---首先删除用户的所有现有权限
DELETE FROM User_Permissions
WHERE User_ID =< cfqueryparam value =#FORM.User_ID#cfsqltype =cf_sql_integer>

---现在重新插入当前/新的权限
INSERT INTO User_Permissions(User_ID,Permissions_ID)
SELECT u.ID AS User_ID
,p.ID AS Permissions_ID
FROM UserTable u CROSS JOIN权限表p
WHERE u.ID =< cfqueryparam value =#FORM.User_ID#cfsqltype =cf_sql_integer>
AND p.ID IN

< cfqueryparam value =#FORM.Permission_IDList#cfsqltype =cf_sql_integerlist =true>



选项2:DELETE已更改/已添加INSERT



另一个选项是使用查询来标识和删除已删除的权限,即取消选中。然后使用INSERT / SELECT插入已添加的权限。其余保持不变。它比选项1有点复杂,但更精确的是,它只删除或添加实际改变。同样,未测试,但是类似这样:

  ---现有ID不在新列表中是联合国检查
DELETE FROM User_Permissions
WHERE User_ID =< cfqueryparam value =#FORM.User_ID#cfsqltype =cf_sql_integer>
AND Permissions_ID NOT IN

< cfqueryparam value =#FORM.Permission_IDList#cfsqltype =cf_sql_integerlist =true>



INSERT INTO UserPermissions(User_ID,Permissions_ID)
SELECT u.ID AS User_ID
,p.ID AS Permissions_ID
FROM UserTable u
CROSS JOIN权限表p
LEFT JOIN UserPermissions up ON up.User_ID = u.ID AND up.Permissions_ID = p.ID
---此用户和权限不存在
WHERE up.Permissions_ID IS NULL
AND u.ID =< cfqueryparam value =#FORM.User_ID#cfsqltype =cf_sql_integer>
AND p.ID IN

< cfqueryparam value =#FORM.Permission_IDList#cfsqltype =cf_sql_integerlist =true>


注意:务必将两个查询都包含在事务中,被视为单个单元,即成功或失败。


I have a dynamic form with multiple checkboxes and on submit I want to run a CFSTOREDPROC in another cfloop using only the checkbox values that changed status.

So far below is a rough concept that I am trying to test but I am sure the way I am creating my array will have issues. If anyone can provide feedback on possible solutions I would appreciate it.

HTML/CF FORM:

<form action="self.cfm" method="post" name="permissions">
 <input type="hidden" name="User_ID" value="<CFOUTPUT>#User_ID#</CFOUTPUT>">
<table>
<CFLOOP QUERY="getNthPermission">
<tr><td>#getNthPermission.Permission_Name#</td><td><input type="checkbox" value="#getNthPermission.Permission_ID#" name="#getNthPermission.Permission_Name#" <CFIF LISTVALUECOUNT(VARIABLES.UserPermission_ID_List,getNthPermission.Permission_ID) NEQ 0>CHECKED</CFIF>></td></tr>
</CFLOOP>
</table>
<input type="submit" name="submit" value="Update">
</form>

COLDFUSION ACTION:

<CFSET VARIABLES.Permission_ID_List = ValueList(getUserPermission.Permission_ID)>
    <cfset changed_permissions=ArrayNew()>
    <CFLOOP QUERY="getNthPermission">
    //If it was checked when the form was created but was unchecked by the user add it to my array.
    <CFIF LISTVALUECOUNT(VARIABLES.UserPermission_ID_List,getNthPermission.Permission_ID) NEQ 0  AND !IsDefined(FORM.#getNthPermission.Permission_Name#)>
    <cfset changed_permissions[getNthPermission.Permission_ID]>
    <CFELSEIF LISTVALUECOUNT(VARIABLES.UserPermission_ID_List,getNthPermission.Permission_ID) EQ 0  AND IsDefined(FORM.#getNthPermission.Permission_Name#)>
    //If it wasn't checked when the form was built but was checked by the user add it to my array.
    <cfset changed_permissions[getNthPermission.Permission_ID]>
    </CFIF>
    </CFLOOP>

//Now loop through the array that was just created and pass the stored procedure my values

<CFLOOP from="1" to="#arrayLen(changed_permissions)#" index="i">
    <CFSTOREDPROC DATASOURCE="#MYDB_DSN#" PROCEDURE="Update_UserPermission">
        <CFPROCPARAM DBVARNAME="@PermissionList" VALUE="#changed_permissions[i]#" TYPE="IN" CFSQLTYPE="cf_sql_longvarchar">
        <CFPROCPARAM DBVARNAME="@User_ID" VALUE="#FORM.User_ID#" CFSQLTYPE="cf_sql_integer">
    </CFSTOREDPROC>
</CFLOOP>

Update:

I am working with a database configuration that I was not part of setting up, which has these tables:

  • Permissions table (permission_name, ID) - List of permissions
  • User table (user_name, ID)
  • User_Permissions table (Permissions_ID, User_ID) - Contains an entry for each permission a user has access to.

So as the checkboxes are selected/unselected I either add a new entry or remove one. Which a stored procedure that was already in place does (or I hope it does).

解决方案

Three tables is a good setup. I was afraid it was a single table or the values were stored as csv lists {shudder}. Still not able to visualize your form real well, but .. from what you described it sounds like it would be simpler to do this with a few queries, rather than looping.

First, give the checkboxes the same name like "Permission_IDList", so the id's will be submitted as a list. Then use that list of ID's with one of the following options:

Option 1: DELETE / INSERT ALL

An approach I often use with simple junction tables is to DELETE all existing user permissions first. Then INSERT the new permissions using an INSERT/SELECT. Two big advantages of using an INSERT/SELECT is that it eliminates the need for looping, and also provides built in validation.

This is one of the simplest options, but it is not as precise as option 2 because it deletes all records, every time. So technically it may do a bit more work than is needed in many cases. Though unless you are dealing with a huge amount of records, differences are usually negligible. Something along these lines (not tested):

    --- First remove all existing permissions for User
    DELETE FROM User_Permissions
    WHERE  User_ID = <cfqueryparam value="#FORM.User_ID#" cfsqltype="cf_sql_integer">

    --- Now re-insert current/new permissions
    INSERT INTO User_Permissions  ( User_ID, Permissions_ID )
    SELECT  u.ID AS User_ID
            , p.ID AS Permissions_ID
    FROM    UserTable u CROSS JOIN PermissionTable p
    WHERE   u.ID = <cfqueryparam value="#FORM.User_ID#" cfsqltype="cf_sql_integer">
    AND     p.ID IN
           (
                <cfqueryparam value="#FORM.Permission_IDList#" cfsqltype="cf_sql_integer" list="true">
           )

Option 2: DELETE changed / INSERT added

Another option is to use a query to identify and DELETE permissions that were removed, ie unchecked. Then use an INSERT/SELECT to insert permissions that were added. The rest remain unchanged. It is a little bit more complex than option 1, but is more precise in that it only removes or adds what actually changed. Again, not tested, but something like this:

    --- existing id's NOT in the new list were UN-checked
    DELETE FROM User_Permissions
    WHERE  User_ID = <cfqueryparam value="#FORM.User_ID#" cfsqltype="cf_sql_integer">
    AND    Permissions_ID NOT IN
           (
                <cfqueryparam value="#FORM.Permission_IDList#" cfsqltype="cf_sql_integer" list="true">
           )


    INSERT INTO UserPermissions ( User_ID, Permissions_ID )
    SELECT u.ID AS User_ID
            , p.ID AS Permissions_ID
    FROM   UserTable u
                CROSS JOIN PermissionsTable p
                LEFT JOIN UserPermissions up ON up.User_ID = u.ID AND up.Permissions_ID = p.ID
    --- this user + permission does not already exist
    WHERE  up.Permissions_ID IS NULL
    AND    u.ID = <cfqueryparam value="#FORM.User_ID#" cfsqltype="cf_sql_integer">
    AND    p.ID IN
        (
                    <cfqueryparam value="#FORM.Permission_IDList#" cfsqltype="cf_sql_integer" list="true">

        )

NB: Be sure to wrap both queries in a transaction so they are treated as a single unit, ie either both succeed or both fail.

这篇关于Coldfusion从动态表单复选框创建数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-27 14:06
查看更多