Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-04-04
14
Medium Priority
?
334 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:ckuhter1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 18854468
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 18854581
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
 

Author Comment

by:ckuhter1
ID: 18857752
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:ckuhter1
ID: 18857821
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 18859183

 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
 

Author Comment

by:ckuhter1
ID: 18859648
the subreason can go with any reason.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18859770

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
 
LVL 39

Expert Comment

by:gdemaria
ID: 18859793

 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
 

Author Comment

by:ckuhter1
ID: 18860001
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 18860226

 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
 
LVL 39

Expert Comment

by:gdemaria
ID: 18860447

 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
 

Author Comment

by:ckuhter1
ID: 18860771
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
 

Author Comment

by:ckuhter1
ID: 18865411
Thank you again it worked like a charm!
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18866424

 Excellant, glad to hear!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question