Coldfusion create array from dynamic form checkbox

Question

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).

Answer 1

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.