Solved

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

Posted on 2007-04-04
14
323 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
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
 

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

910 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

22 Experts available now in Live!

Get 1:1 Help Now