Solved

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

Posted on 2007-04-04
14
329 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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 500 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

739 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