Help saving multiple checkboxes from a html table into individual records in a SQL table

I am using cold fusion and tSQL and trying to store multiple reasons and subreasons for a customer that is calling in with a SQL table from a table of checkboxes in coldfusion/html.  There are about 16 subreasons and 5 reasons so I would rather not make separate fields for each.  Currently my SQL table has data and I can display it, but I have not been able to write a query to update or insert the data from checkboxes.
My SQL table fields are:
issue_id (pkey)    
reason_id  
subreason_id  
log_id

where the log_id associates the various reasons and subreasons for the contact.  The reason_id and subreason_id's come from tables with the various reasons and subreasons in them.

When displaying the output I am able to use nested cfloops to display all the data in a table and then i check if the SQL table has a matching column and row to display the check.  
Even if I could get an insert to work with this I am having a hard time thinking of ways you can do an update if the data needed modified.  Any advice would be appreciated and if you need more information please let me know.
ckuhter1Asked:
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.

David ToddSenior DBACommented:
Hi,

One way to put this into one field is to use bitmasking on the check boxes, in a similar way to what happens with radio buttons.
ie
value = 0
if box1.ischekec
value = value + 1
if box2.ischekec
value = value + 2
if box3.ischekec
value = value + 4
etc ...

HTH
  David
0
SidFishesCommented:
it looks as if you are trying to store reasons and subreasons in the same table...??

this would be a poor choice and not sure you could make the insert work without a lot of messing about

The data structure that i'd recommend is

call_log

CallID
customerID
CallDate

reason_log

ReasonLogID
ReasonID
callID


subReason_log

SubReasonLogID
ReasonID

this is really a much better structure as it allows for any level of reasons and subreasons

it's also easy to update and insert as you've got a simple structure


         ---Calls----
        /               |
      --R1-------- R2--
      /      \       /      \
    SR1   SR2 SR3   SR4


etc...
0
ckuhter1Author Commented:
SidFishes I have used a similar structure like that before, but it doesnt seem right for some reason for this project unless the ReasonID under subReason_log should be ReasonLogID is that what you meant?  I think that would work and either way thanks for the normalization tip (still learning db's).  I still need help with how I can get the data from the checkboxes to the insert.  I am afraid I may have to manually key it, but I really prefer the idea of keeping it set up with nested loops so it will be dynamic in case a reason or subreason was added.  I tried to name the checkbox variable based on the reasonid and the subreasonid, but cold fusion didnt like the format form.#reasonid#,#subreasonid# for a form variable.  I can name each checkbox the same but then I get a long nasty string in that form variable and I am unsure how I can break it up to insert it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ckuhter1Author Commented:
if this helps here is how my input is setup
<form>
   <cfloop query="reason_list">
      <cfloop query="subreason_list">
         <cfoutput>
            <input type="checkbox" name="#reason_list.reason_id#,#subreason_list.subreason_id#" value =1>
         </cfoutput>
      </cfloop>
   </cfloop>
....

I left out the table stuff and the part that checks to see if the value exists because I am focusing mostly on how the checkbox should be named and how I can get that data broken apart for the SQL part.
0
gdemariaCommented:

 Is your subreason a child of one and only one reason?

 Like:

 Reason A:  Not Happy
  Sub Reason1 : I didn't like the people
  Sub Reason2 : I didn't like the place
 Reason B: Can't aford it
  Sub Reason3: Membership is too much
  Sub Reason4: Food is too much

Or can any subreason go with any reason?

0
ckuhter1Author Commented:
the subreason can go with any reason.
0
gdemariaCommented:

Then, why not have it like this?


Please select your reason:

<cfoutput query="reason_list">
  <input type="checkbox" name="reasonIDs" value="#reason_list.reason_id#"> #reason_list.phrase#
</cfoutput>


Select your second reason:

<cfoutput query="subreason_list">
  <input type="checkbox" name="subreasonIDs" value="#subreason_list.subreason_id#"> #subreason_list.phrase#
</cfoutput>

0
gdemariaCommented:

 How many of each reason / subreason, can they check?   One each or any number of each?

 I ask because It seems your data structure is set up for only one of each and it of course matters on how to insert them..

0
ckuhter1Author Commented:
They can have any number of reason and suubreason, but more then likely it will not be more then 3 or 4.  Having 2 separate inputs is fine when I only want one reason/subreason combo, but since they can select multiple reasons and we need a subreason for each reason I had to nest them like I did.  The way I have it setup is like a grid with reasons on the right and subreasons along the top and the user just selects the proper cell.
0
gdemariaCommented:

 Ok, I get it.  You can have as many reasons as you wish, but the reaons come in pairs; one reason with one subreason.

 Rather than varying the name so much,  my preference would be to make the name a constant with a counter such as "reason_1, reason_2, etc"   If the box is checked, it will contain the two values reason_id and subreason_id.    Makes looping through the possible checks much easier...


 Here's what I mean...

   <cfset counter = 0>
   <cfloop query="reason_list">
      <cfloop query="subreason_list">
          <cfset counter = counter + 1>
         <cfoutput>
            <input type="checkbox" name="reason_#counter#"
                       value="#reason_list.reason_id#,#subreason_list.subreason_id#">
         </cfoutput>
      </cfloop>
   </cfloop>
  <cfoutput>
   <!--- keep track of the total number of checkboxes for your action's loop ---->
   <input type="hidden" name="totalCheckboxes" value="#counter#">



 To process it...

 <cfloop index="kk" from="1" to="#form.totalCheckboxes#">
    <cfif IsDefined("form.reason_#kk#")>
       <cfset variables.reasonID = listFIrst(form['reason_' & kk])>
       <cfset variables.subreasonID = listLast(form['reason_' & kk])>

       ---- do insert / update here ----

    </cfif>
 </cfloop>

0

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
gdemariaCommented:

 Is Log_ID  a pointer to your  main table?

 LOGS - contains the caller name, the date of the issue, etc..

 And Issues is the table holding the reasons?
 There are only the 3 fields in Issues, right?  3 Foreigh keys pointing to other tables and one primary key for the table.

 
0
ckuhter1Author Commented:
Yes Log_ID points to the customer contact in the contact table which has name, date, tn, etc and issues has the 3 foreign keys a primary key and also one other field (just to mark if the complaint was resolved either 0 (no) or 1 (yes).  

I will try the counter suggestion you gave me a little later tonight and let you know if it works.  It looks good and seems like it will when i go over it in my head.  Thank you so much for your assistance.  I will report back later today or tomorrow and let you know for sure!
0
ckuhter1Author Commented:
Thank you again it worked like a charm!
0
gdemariaCommented:

 Excellant, glad to hear!
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.