Technical references: CFML Javascript Update One to Many relationship tables

CFML/Javascript Update Table Page

You have list of users. Those users can belong to 0 to many roles. I needed a simple way to update this table. This is my first approach.

Here's the basic view of the tables and their relationship.

Here's the header portion of the code with the two Javascript functions and the call to my first database table.
alterRole populates a text box that will be submitted when we get ready to save the changes.
resetButton re-enabled all the pressed Add/Delete buttons so that the user can begin again.

<html>
<title>Edit Roles</title>
<head>
<script type ="text/javascript">
function alterRole(action,roleId){
  document.getElementById(roleId).disabled="disabled";
  if(action == 1){
    var txt = document.getElementById("Add").value;
    txt = txt + ";" + roleId;
    document.getElementById("Add").value = txt;
  }else{
    var txt = document.getElementById("Del").value;
    txt = txt + ";" + roleId;
    document.getElementById("Del").value = txt;
  }
}

function resetButton(){
  var inputs = documents.getElementByTagName("BUTTON");
  for (var i=0; i<inputs.length;i++){
    if (inputs[i].type == 'button'){
      input[i].disabled = false;
    }
  }
  document.getElementById("Add").value = "";
  document.getElementById("Del").value = "";
}
</script>

<cfquery name="users" datasource="MYDB">
  select PID, my_UserID from user_list order by userid
</cfquery>
<cfset user_id = 1>
</head>
<cfheader name = "Expires" value "#Now()#">

Below is the first part of the body. This portion is executed when we press Load (user roles) or Save. If we pressed Load then the page loads the currently selected Roles (and available) for the User we selected. If we press Save, then the page inserts into or remove from the intermediate table the UserIDs and RoleIDs. The Form.Add and Form.Del is populated in the last portion of the code.

<body><form action="edit_roles.cfm" method="post">
<cfif isDefined("form.load") or isDefined("form.save")>
  <cfset thisUserID = #form.user_id#>
  <input name="UserName" type="text" value='<cfoutput>#form.user_id#</cfoutput>' size="10" hidden>
  <cfif isDefined("form.save")>
    <cfif len(#form.Add#) gt 0>
      <cfset this1 = #form.Add#>
      <cfset this1 = right(this1,len(this1)-1)>
      <cfset this2 = this1.split(";")>
      <cfloop array = "#this2#" index = "item">
        <cfquery name = "user_to_role" datasource="MYDB">
          insert into user_to_role(user_pid,roleid) values (#thisUserId#,'#item#')
        </cfquery>
      </cfloop>
    </cfif>
    <cfif len(#form.Del#) gt 0>
      <cfset this1 = #form.Del#>
      <cfset this1 = right(this1,len(this1)-1)>
      <cfset this2 = this1.split(";")>
      <cfloop array = "#this2#" index = "item">
        <cfquery name ="user_to_role" datasource="MYDB">
          delete from user_to_role where user_pid= #thisUserId# and roleid = '#item#'
        </cfquery>
      </cfloop>
    </cfif>    
<cfelse>
  <cfset thisUserId = 0>
  <input name ="UserName" type ="text" value ="" size ="10" hidden>
</cfif>

This section populates the entire list of Users and pre-selects the user if we're coming from Save action. This section also has the query that contains the entire list of Roles available and Roles that are already defined for the user.

<h2>User Roles Management</h2>
<h3>Users:
<select name ="user_id" id ="user_id">
  <cfoutput query="users">
    <cfif #PID# eq #thisuserid#>
      <option value="#PID#" selected>#my_USERID#</option>
    <cfelse>
      <option value="#PID#">#my_USERID#</option>
    </cfif>
  </cfoutput>
</selected>
<input name ="Load" type ="Submit" value ="Load"/>
<input name ="Save" type ="Submit" value ="Save"/>
</h3><br>
<cfif #thisuserid# gt 0>
  <cfquery name="user_to_roles" datasource ="MYDB">
    select user_pid, rolename, roles.roleId, ownerid 
    from roles left outer join user_to_role 
    on roles.roleid = user_to_role.roleid 
    and user_pid = #thisuserid# where roletype = 'User' order by rolename
</cfquery>

This section populates a table with the available Roles. If the selected User is already associated with a role then we provide the option to remove, otherwise we have the option add. When we add or delete a role from the selected User, then the RoleID is added to the appropriate text box that will be submitted when we press Save.

<table id ="roles_table">
  <thead>
    <tr>
      <th>RoleName</th>
      <th>Owner</th>
      <th><button type="button" onClick="javascript:resetbutton();">Reset Buttons</button></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <cfoutput query = "user_to_roles">
    <tr>
      <th>#RoleName#</th>
      <th>#Ownerid#</th>
      <cfif len(#user_pid#)>
        <button type="button" onClick="javascript:alterRole(0,'#roleid#');" value ="#roleid#" id = "#roleid#" name = "del_btn">
        Remove</button>
      <cfelse>
        <button type="button" onClick="javascript:alterRole(1,'#roleid#');" value ="#roleid#" id = "#roleid#" name = "add_btn">
        Add</button>
      </cfif>
      <th></th>
      <th></th>
    </tr>
    </cfoutput>
  </tbody>
  </table>
</cfif>
<input name ="Add" id ="Add" type ="text" value ="" size ="100" hidden>
<input name ="Del" id ="Del" type ="text" value ="" size ="100" hidden>
</form>
</body>
</html>

For next version, I plan on using AJAX on every Add/Delete action so that we don't have to submit the entire page. Also, some IF branches can be slimmed down if we move some common actions into a function in CFC.

---

Technical references

Wednesday, August 9, 2017

CFML Javascript Update One to Many relationship tables

CFML/Javascript Update Table Page

No comments:

Post a Comment

AWS WAF log4j query