问题描述
我有一个动态表单与多个复选框和提交我想运行 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 anothercfloop
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从动态表单复选框创建数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!