Solved

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

Posted on 2007-04-04
14
322 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
14 Comments
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:ckuhter1
Comment Utility
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
Comment Utility

 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
Comment Utility
the subreason can go with any reason.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

 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
Comment Utility
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
Comment Utility

 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
Comment Utility

 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
Comment Utility
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
Comment Utility
Thank you again it worked like a charm!
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

 Excellant, glad to hear!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now