• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

How to check form checkboxes based on db query

Hi Experts,

FIELD1: I have the following string in a db field: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

FIELD2: I have another string in another field: 2,3,4,7,10,11,13

I am using FIELD1 with the following code to display 12 checkboxes.  I am displaying a page that lists all FIELD1 numbers as checkboxes.  As the user checks the available checkboxes and submits the form, I am inserting the selected checkboxes into FIELD2.

I am now trying to setup an update web form that will display all available numbers from FIELD1 and check those boxes if they are contained in FIELD2.

I have attached a screen shot, so I hope that helps.

Also, I have attached my code, since I am having trouble displaying it here in the comments... I am using the cfloop through FIELD1 to display all the checkboxes.


Comment: The database field is in the following format 1,2,3,4,5 and I am using numberformat to display at 001,002,003,004 ect...
all-available-checkboxes.PNG
previously-selected-checkboxes.PNG
code-sample.txt
0
ehart12
Asked:
ehart12
  • 5
  • 5
1 Solution
 
_agx_Commented:
Can you post the structure of your 2 tables?  Because I'm not clear on whether the values are being stored in separate rows or as a big delimited "string"

ie   Row  | Value
      #1    |  2
      #2    | 3
      #3    | 4

OR

      #1    |  "2,3,4"
0
 
ehart12Author Commented:
The actual strings are stored in two seperate tables and look like this;

Table#1 | 1,2,3,4,5,6

Table#2 | 2,3,4

Table#1contains all the available options
Table#2contains only the selected options from Table#1

Does that help?
0
 
_agx_Commented:
But are the values in the same row

                       SomeColumn
        Row #1   2,3,4

or each in a separate row?

                       SomeColumn
        Row #1   2
        Row #2   3
        Row #3   4

If they're all in one row, any chance you can change the structure? Because storing lists isn't recommended (.. and tasks like this are exactly why :) .
0
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
ehart12Author Commented:
Oh no, that does not sound good...  

All of the numbers are stored in a single row  (1,2,3,4,5...) and I have no flexability to change the field format.

I could take the string of numbers (1,2,3,4,5...) and break them apart and insert them each into their own row in a temporary table. Would that make what I am asking for any easier?
0
 
_agx_Commented:
and I have no flexibility to change the field format.

Ok, lets just try and work with what you have.  (But going forward storing lists violates good db design rules. It's usually the wrong way to go 99% of the time. )

Anyway back to your query. I think I'm still missing something. If #query.serial_number[row]# contains a list of values ie "1,2,3,4"  how is this code generating one checkbox per value?

<cfloop from="1" to="#query.recordcount#" index="x">
      <cfoutput>
            <input type="checkbox" value="#query.serial_number[x]#" name="cbox2" onclick="countCheckboxes()">
....

It should be an easy solution, I'm just not seeing where these "lists" of values come in :)
0
 
ehart12Author Commented:
Here is the complete code that i use to list all of the checkboxes, one checkbox for each number in the list.

Example: The list string of 1,2,3,4,5 would give me 5 checkboxes

<cfloop from="1" to="#query.recordcount#" index="x">
<cfoutput>
<input type="checkbox" value="#query.serial_number[x]#" name="cbox2" onclick="countCheckboxes()">
SN#numberformat(query.serial_number[x], 000)#
<cfif i lt query.recordcount>,</cfif>
</cfoutput>
</cfif>
</cfloop>
0
 
ehart12Author Commented:
When the form is submitted, the variable #cbox2# lists all the checkboxes in the 1,2,3,4,5... format.  As you know, if a checkbox is not checked, it is undefined and not sent with the form.
0
 
_agx_Commented:
Ok, then your query column doesn't really contain a list.  I think we just crossed wires on that.

Anyway, if you want to compare the loop value against your list of previously checked values, do this:

           <!--- simulate your list --->
           <cfset previouslyCheckedValues = "2,3,4,7,10,11,13">

          <cfloop from="1" to="#query.recordcount#" index="x">
                <cfoutput>
                 <input type="checkbox" value="#query.serial_number[x]#"
                           name="cbox2"
                           onclick="countCheckboxes()"
                         <cfif listFindNoCase( previouslyCheckedValues, query.serial_number[x])>checked</cfif>
                 />
                  SN#numberformat(query.serial_number[x], 000)#
                  <cfif i lt query.recordcount>,</cfif>
             </cfoutput>
        </cfloop>
0
 
ehart12Author Commented:
That worked perfectly! You had me worried for a second ;)

Thank you!
0
 
_agx_Commented:
Lol.. so did you with the "lists" ;-) But once I realized "field1" wasn't actually a list (doh), the solution was more workable :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now