dodgerfan
asked on
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.
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?
ASKER
There are other fields being submitted. The names and values are supposed to be dynamic, based on info from the database table.
>>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?
all the checkboxes are to be stored as cb1,cb2,cb3 in one field?
ASKER
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.
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
>>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
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....
Regards
Hart
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....
Regards
Hart
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
I.E.
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#" >
SELECT * TEAMS
</cfquery>
<cfquery name="getMyTeam" username="#request.dbuser# " password="#request.dbpass# " datasource="#request.dsn#" >
SELECT THE FIELD THAT CONTAINS THE STRING OF TEAMS FOR THE SPECIFIC USER (in my case)
</cfquery>
<cfif parameterexists(getMyTeam) and IsQuery(getMyTeam)>
<cfloop query="getMyTeam">
<cfset strMyTeams = ListAppend(strMyTeams,getM yTeam.TEAM )>
</cfloop>
</cfif>
<table class="borderTop996633" cellpadding="0" cellspacing="0" align='center'>
<tr valign="top" bgColor="##990000">
<td class="whiteHeader13px" colspan="2">Add\Remove Application Teams</td>
</tr>
<cfloop query="listTeam">
<tr>
<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.TE AM_DISP_NA #</td></tr ></table>
<!--- <cfinput type="checkbox" value="#listTeam.TEAM_CD#" name="myTeams" > --->
<cfelse>
<table><tr><td><cfinput class="checkbox" type="checkbox" name="myTeams" value="#listTeam.TEAM_CD#" checked="No"/></td><td class="label">#listTeam.TE AM_DISP_NA #</td></tr ></table>
<!--- <cfinput type="checkbox" value="#listTeam.TEAM_CD#" name="myTeams"> --->
</cfif>
</td>
</tr>
</cfloop>
</table>
I.E.
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#
SELECT * TEAMS
</cfquery>
<cfquery name="getMyTeam" username="#request.dbuser#
SELECT THE FIELD THAT CONTAINS THE STRING OF TEAMS FOR THE SPECIFIC USER (in my case)
</cfquery>
<cfif parameterexists(getMyTeam)
<cfloop query="getMyTeam">
<cfset strMyTeams = ListAppend(strMyTeams,getM
</cfloop>
</cfif>
<table class="borderTop996633" cellpadding="0" cellspacing="0" align='center'>
<tr valign="top" bgColor="##990000">
<td class="whiteHeader13px" colspan="2">Add\Remove Application Teams</td>
</tr>
<cfloop query="listTeam">
<tr>
<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#"
<!--- <cfinput type="checkbox" value="#listTeam.TEAM_CD#"
<cfelse>
<table><tr><td><cfinput class="checkbox" type="checkbox" name="myTeams" value="#listTeam.TEAM_CD#"
<!--- <cfinput type="checkbox" value="#listTeam.TEAM_CD#"
</cfif>
</td>
</tr>
</cfloop>
</table>
ASKER
OK, I have not tried the suggestion from trr1jdg yet. I'll take a look. As for Jester_48, I tried the following code:
<CFIF ISDEFINED("FORM.FieldName" )>
<CFSET listofCbValues="">
<CFLOOP index="fieldName" list="#FORM.FieldName#">
<CFIF UCASE(LEFT(FieldName, 8)) EQ "CALNAME_">
<CFIF Len(listofCbValues) GT 0>
<CFSET listofCbValues=listofCbVal ues & ", ">
</CFIF>
<CFSET listofCbValues=listofCbVal ues & #evaluate(filedName)#>
</CFIF>
</CFLOOP>
</CFIF>
#listofCbValues#
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.
<CFIF ISDEFINED("FORM.FieldName"
<CFSET listofCbValues="">
<CFLOOP index="fieldName" list="#FORM.FieldName#">
<CFIF UCASE(LEFT(FieldName, 8)) EQ "CALNAME_">
<CFIF Len(listofCbValues) GT 0>
<CFSET listofCbValues=listofCbVal
</CFIF>
<CFSET listofCbValues=listofCbVal
</CFIF>
</CFLOOP>
</CFIF>
#listofCbValues#
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.
ASKER
Sorry, the On, On, On is submitted to the database field correctly, but I want it to submit the values displayed.
>>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
textbox=value
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
textbox=value
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>
<tr><td><input type="checkbox" name="cb_213345" value="#dept#">#dept#</td>
ASKER
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?
ASKER
I got it. Thanks for all of the help. Jester_48, your code helped me gte this straightened out. Thanks for everyone's input.
np... glad i could help
thanks for the points!
thanks for the points!