title: SQL query inner join tables, print to HTML <select> tag
body: <p>I have been tasked with updating an old application and ran into a head scratch-er.  The application runs on Railo(ColdFusion) with a MySQL 5 database.</p>
  
  <p>The application is using Roles based authentication to manage users.  The previous developer appeared to have no code to modify/add/delete users and just modified the database columns directly.  I have taken care of the add/delete user part, no problem.  The modify part is where I stumble.</p>
  
  <p>Below is the basic table layout for Roles and Members.</p>
  
  <pre><code>MEMBERS TABLE
  id | username | role
   1 | user1    | Administrator,User,Group1,Group2
   2 | user2    | User,Developer
   3 | user3    | SuperUser,Group1,Group2
   4 | user4    | SuperUser,Developer
   5 | user5    | Guest
  
  ROLES TABLE
  role_id | role_name
        1 | Administrator
        2 | Developer
        3 | SuperUser
        4 | Guest
        5 | User
        6 | Group1
        7 | Group2
  </code></pre>
  
  <p>To modify a user's roles, my first thought was to use a simple HTML select tag and a couple CFQUERY.  Something similar to the code below.</p>
  
  <pre><code>&lt;select name="role" multiple="multiple" size="7"&gt;
  &lt;cfoutput&gt;
  &lt;option value="#GetRole.role_name#" &lt;cfif GetRole.role_name = GetUser.role&gt;checked&lt;/cfif&gt;&gt;#GetRole.role_name#&lt;/option&gt;
  &lt;/cfoutput&gt;
  &lt;/select&gt;
  &lt;CFQUERY NAME="GetRole" DATASOURCE="#request.dataSource#"&gt;
    SELECT role_name
    FROM roles
  &lt;/CFQUERY&gt;
  
  &lt;CFQUERY NAME="GetUser" DATASOURCE="#request.dataSource#"&gt;
    SELECT a.id, a.username, a.role, b.role_name
    FROM members a
    LEFT JOIN roles b ON b.role_name = a.role
    WHERE a.id = #FORM.id#
  &lt;/CFQUERY&gt;
  </code></pre>
  
  <p>The select tag would pull from GetRole and populate all available roles.  The GetUser query would find the current roles the user is a member of from the list in the members table.  Then I would simply use the "checked" flag in the select tag.  That is where I hit a wall.  No matter what path I take I keep running into array to list issues.</p>
  
  <p>When I add a new user I simply use" role = '#ListQualify(FORM.role,"")#' and that takes all the roles in my select tag, adds a comma, and spits them into the members.role column.</p>
  
  <p>I even tried going step by step and creating an array:</p>
  
  <pre><code>&lt;cfset roleArray = ArrayNew(1)&gt;
  
  &lt;cfloop query = "GetUser"&gt;
  &lt;cfset temp = ArrayAppend(roleArray, "#role#")&gt;
  &lt;/cfloop&gt;
  
  &lt;cfset roleList = ArrayToList(roleArray, ",")&gt;
  </code></pre>
  
  <p>I tried doing a SQL FIND_IN_SET() as well.</p>
  
  <p>No matter what I try I get the dreaded array to Boolean errors.</p>
  
  <p>Suggestions?  Should I try a different approach than an HTML select tag?  Is there something I overlooked?  Any help would be most appreciated.</p>
  
  <p>Hopefully I posted this question well, only my second time to post a question here. :D</p>
  
  <p>-------------------- UPDATE --------------------</p>
  
  <p>I wanted to add that the application uses IsUserInRole() throughout the site to check if a user has access to certain areas or not.  For example:</p>
  
  <pre><code> &lt;cfif IsUserInRole("Administrator")&gt;
   Do stuff here as an administrator.
   &lt;/cfif&gt;
  </code></pre>
  
  <p>It is also used with multiple groups, and ColdFusion automatically finds each group seperated by commas:</p>
  
  <pre><code>&lt;cfif IsUserInRole("Administrator,Group1,Group2")&gt;
  </code></pre>
  
  <p>Is there a better way for ColdFusion to manage Roles?</p>
  
  <p>-------------------- UPDATE #2 --------------------</p>
  
  <p>I discovered the original developer used CFLOGIN to set the roles for users.  I have heard horror stories about CFLOGIN, so should it be nixed in favour of something better?  Set a user's role in session variables instead perhaps?</p>
  
  <pre><code>&lt;cfquery name="qryGetUserDetails" datasource="#request.datasource#"&gt;
  SELECT *
  FROM members
  WHERE username = &lt;cfqueryparam cfsqltype="cf_sql_varchar" value='#Trim(FORM.username)#'&gt;
  &lt;/cfquery&gt;
  &lt;cflogin&gt;
    &lt;cfloginuser name="#FORM.username#" password="#FORM.password#" roles="#qryGetUserDetails.role#"&gt;
  &lt;/cflogin&gt;
  </code></pre>
  
  <p>Notice the cfqueryparam? :D  I applied what I learned from you guys quickly! :D</p>