Dynamically populate check boxes with database information

I have a ColdFusion used to input data into an Oracle database. Most of it is pretty straight forward controls. Now, though, I need to show a series of check boxes. The check boxes need to be dynamically populated with data from the database based on a previous selection. For example, if a user wants to submit a record for the HR department, then a set of checkboxes will display at the bottom based on the HR selection. The user needs to check all that apply, then submit the form. The checkbox data will be stored in one field in the database. I have the form displaying the checkboxes properly, but I do not know how to get it to grab the string values that get checked in order to store them in the table. Any help would be greatly appreciated. Thanks.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James RodgersWeb Applications DeveloperCommented:
are the check boxes the only fields being submitted to this page? do you know the names of the checkboxes or are the completely dynamic?
dodgerfanAuthor Commented:
There are other fields being submitted. The names and values are supposed to be dynamic, based on info from the database table.
James RodgersWeb Applications DeveloperCommented:
>>The checkbox data will be stored in one field in the database

all the checkboxes are to be stored as cb1,cb2,cb3 in one field?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

dodgerfanAuthor Commented:
Yes, the checkbox data will be stored in one field in the table, comma delimited. cb1, cb2, cb3 is the right idea. That data (cb1, cb2, cb3) gets pulled from another table and is displayed based on a prior selection. For example, Option one may display cb and cb2. Option two all three, etc.
James RodgersWeb Applications DeveloperCommented:
i think i'm missing something ....

you are submitting a form with several fields and checkboxes

you want to store the checkbox values as a comma delimited list in a single database field

the names of the field cannot be determined before submitting the form,
can you preface the field names with cb to identify the checkboxes?
James RodgersWeb Applications DeveloperCommented:
if yuocan preface the checkbox name with an identifier (eg cb) then this will get the values into a single variable as a comma delimited list


<cfif isDefined("form.fieldNames")>
      <cfset listOfCbValues="">
      <cfloop index="fieldName" list="#form.fieldNames#">
              <cfif ucase(left(fieldName,2)) eq "CB">
                  <cfif Len(listOfCbValues)>
                        <cfset listOfCbValues=listOfCbValues & ", ">
                  <cfset listOfCbValues=listOfCbValues & #evaluate(fieldName)#>

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dodgerfanAuthor Commented:
That looks to me like you have the idea.I apologize if I did not state my question more clearly. I confused myself a little. I'll give this a shot and let you how it works. Thanks.
James RodgersWeb Applications DeveloperCommented:
>>I apologize if I did not state my question more clearly

no worries, it looks like we're on the right track... thats what matters
ok just a query can't the names of the checkbox be hardcoded to CB..
instead of giving dynamic names.

i am saying this because u already know that these checkboxes are for HR / SOME other stuff.

all u have to do is pass that id or value in an hidden field to the form submission page...

and their store this id / value and then in the next colum just the value of CB in a comma delimited format.
by this u can avoid the loop on the fieldnames....

Try this, I use it to keep track of developers and their application teams.  This piece of code will check a checkbox if the specific users teams are listed in all the teams

Team: 1,2,3,4,5,6,7,8,9,10
MyTeam: 2,3,15.5,4

This would display the all 10 checkboxes with 2,3, and 4 checked
Have fun

<cfquery name="listTeam" username="#request.dbuser#" password="#request.dbpass#" datasource="#request.dsn#">

<cfquery name="getMyTeam" username="#request.dbuser#" password="#request.dbpass#" datasource="#request.dsn#">

<cfif parameterexists(getMyTeam) and IsQuery(getMyTeam)>
      <cfloop query="getMyTeam">
            <cfset strMyTeams = ListAppend(strMyTeams,getMyTeam.TEAM)>

<table class="borderTop996633" cellpadding="0" cellspacing="0" align='center'>
      <tr valign="top" bgColor="##990000">
            <td class="whiteHeader13px" colspan="2">Add\Remove Application Teams</td>
      <cfloop query="listTeam">
            <td colspan="2">
                  <cfif ListContains(strMyTeams, listTeam.TEAM_CD) NEQ 0>
                        <table><tr><td><cfinput class="checkbox" type="checkbox" name="myTeams" value="#listTeam.TEAM_CD#" checked="Yes"/></td><td class="label">#listTeam.TEAM_DISP_NA#</td></tr></table>
                        <!--- <cfinput type="checkbox" value="#listTeam.TEAM_CD#" name="myTeams" > --->
                        <table><tr><td><cfinput class="checkbox" type="checkbox" name="myTeams" value="#listTeam.TEAM_CD#" checked="No"/></td><td class="label">#listTeam.TEAM_DISP_NA#</td></tr></table>
                        <!--- <cfinput type="checkbox" value="#listTeam.TEAM_CD#" name="myTeams"> --->
dodgerfanAuthor Commented:
OK, I have not tried the suggestion from trr1jdg yet. I'll take a look. As for Jester_48, I tried the following code:

  <CFSET listofCbValues="">
  <CFLOOP index="fieldName" list="#FORM.FieldName#">
    <CFIF UCASE(LEFT(FieldName, 8)) EQ "CALNAME_">
        <CFIF Len(listofCbValues) GT 0>
            <CFSET listofCbValues=listofCbValues & ", ">
        <CFSET listofCbValues=listofCbValues & #evaluate(filedName)#>

Now, when I submit the data to the database, the the delimited list is submitted as On, On, On, On, instead of actually submitting the values displayed, i.e. HR, Admin etc. Did I miss something? I was wondering if the evaluate part of this is incorrect. Thanks for all of the help.

dodgerfanAuthor Commented:
Sorry, the On, On, On is submitted to the database field correctly, but I want it to submit the values displayed.
James RodgersWeb Applications DeveloperCommented:
>>On, On, On is submitted to the database field correctly, but I want it to submit the values displayed.

what are the values frio the checkboxes?

set the checkbox value to the group name ie

<input type="checkbox" name="cb_213345" value="hr">

a checkbox will only submit the value if it is checked

if the value is "on" then thre is no way to capture "hr" as all that is submitted from a form is the name value pair of the form elements
James RodgersWeb Applications DeveloperCommented:
when you generate the checkbox in the form do it  something like this

<tr><td><input type="checkbox" name="cb_213345" value="#dept#">#dept#</td></tr>
dodgerfanAuthor Commented:
I'll go back and work my way through this with your latest comments. I think we finally reached the root of my original problem. The value of the checkbox is supposed to be dynamically generated from the one of the database tables, then whichever ones are checked should be the delimited string that is inserted into the table that stores the info from the form.
can you post the SQL statement that pulls the dynamic data to generate the checkboxes?
dodgerfanAuthor Commented:
I got it. Thanks for all of the help. Jester_48, your code helped me gte this straightened out. Thanks for everyone's input.
James RodgersWeb Applications DeveloperCommented:
np... glad i could help

thanks for the points!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.