Multiple Checkbox Insert as Multiple Records

[Hi_There]

I am relatively new to ColdFusion. It's simplicity, along with Dreamweaver's insert/update/delete wizards, have allowed me to build some pretty neat applications for basic data insertion, retrieval and deletion and display.

However, I need to be able to insert multiple selections from checkboxes as multiple records in a table, and I'm running into problems. I'm guessing this sort of thing is the basic premise of pretty much any shopping cart out there, but I can't seem to find any good information on this in either my "Developing with CFMX Manual" or my "CFMX Web App. Construction Kit" book, or the web for that matter. (The only examples shown for either the form page or the action page are for one, static checkbox!!!)

The basic premise of what I'm trying to achieve:

User is able to select various items from multiple checkboxes. (ITEM_ID)
This selection is associated with the user. (CUSTOMER_ID)
The selection is associated with a point in time such as a year. (INSTANCE_ID)
These IDs all need to be inserted into a 3 column table expecting integers for ITEM_ID, CUSTOMER_ID, and INSTANCE_ID

Example of FORM Values that would be passed: ( 147,148,149 , 23445 , 4 ) = (3 ITEM_IDs, CUSTOMER_ID, and INSTANCE_ID)

Typical Error upon submission to insert action page: Count of read-write columns does not equal count of values (Which is to be expected, I'm gathering multiple values via the checkboxes).

How can I insert each checkbox selection as a separate record in the table???

Such as:
( 147 , 23445 , 4 )
( 148 , 23445 , 4 )
( 149 , 23445 , 4 )

My Code:

Form:

<form method="post" name="form2" action="process_form_action_page.cfm">

<table>

<cfoutput query="Stored_Procedure1">

<tr>
<td><input type="checkbox" name="ITEM_ID" value="#Stored_Procedure1.ITEM_ID#"></td>
<td>#Stored_Procedure1.ITEM_TITLE#</td>
</tr>

</cfoutput>

</table>

<input type="hidden" name="CUSTOMER_ID" value="#CUSTOMERS.CUSTOMER_ID#">
<input type="hidden" name="INSTANCE_ID" value="#INSTANCE.INSTANCE_ID#">
<input type="submit" value="Insert record">

</form>


Action Page:


<cfquery datasource="ORDERS">

INSERT INTO PRODUCT_CUSTOMER (ITEM_ID, CUSTOMER_ID, INSTANCE_ID) VALUES (
<cfif IsDefined("FORM.ITEM_ID") AND #FORM.ITEM_ID# NEQ "">
#FORM.ITEM_ID#
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.CUSTOMER_ID") AND #FORM.CUSTOMER_ID# NEQ "">
#FORM.CUSTOMER_ID#
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.INSTANCE_ID") AND #FORM.INSTANCE_ID# NEQ "">
#FORM.INSTANCE_ID#
<cfelse>
NULL
</cfif>
)

</cfquery>
</cfif>

Thanks!

[Dan_Bracuk]

Your form.item_id field will not exist if nothing is selected. You can use the isDefined function to check for this.

If at least one box is checked, you will get a list. The easiest way to loop through a list is with the cfloop tag.

Details are in the cfml reference manual. If you don't have one, the internet does. Google is your freind. To find a tag, submit the name of the tag, complete with angle brackets. To find a function, submit "coldfusion functionname".

[Hi_There]

I don't care if the form.item_id field will not exist if nothing is selected. I'll deal with that later.

I know how to use Google, thank you.

I merely want to know how to combine the loop with a submit, in a nice concise example. Trust me, I've looked through a combined 5" worth of CF Manuals and searched Google to no avail. I just need a simple example that I can re-tool.

[Dan_Bracuk]

<cfloop list="#form.something#>
<cfquery>
insert into etc
</cfquery>
</cfloop>

[azadisg]

no need to get so harsh - listen to a good advice and follow it. if you have searched this forum you would have found several examples of how to do what you want to do... and since you posted code as well good people will suggest other things to improve in your code apart from giving you advice how to do what you want to do. anyway:

action page:
<cfif isdefined("form.item_id")>

<cfloop list="#form.item_id" index="x">
{your insert query goes here}
</cfloop>
</cfif>

[Hi_There · Accepted solution]

I'm not against taking good advice. If I was a little too "harsh", I apologize... but at least I finally got a good response.

I figured out what I wanted anyway. (Sabaidee, yours was the closest answer to what I eventually came up with. Thanks for the effort, I appreciate it. I will definitely implement your logic <cfif isdefined("form.item_id")> to check if the checkbox is selected.)

What I came up with as a solution:

---

<!---The form page containing multiple checkboxes to be inserted as individual records--->
<html>
<body>

<form action="form_processor.cfm" method=post>

<input type="hidden" name="CUSTOMER_ID" value="#CUSTOMERS.CUSTOMER_ID#">
<input type="hidden" name="INSTANCE_ID" value="#INSTANCE.INSTANCE_ID#">

<!---Checkboxes--->
<input name="ITEM_ID" type="checkbox" value="147">147
<input name="ITEM_ID" type="checkbox" value="148">148
<input name="ITEM_ID" type="checkbox" value="149">149

<input type="submit" value="Insert Multiple Records">

</form>
</body>
</html>


<!---Form processor page: "form_processor.cfm" which executes multiple record database insertion--->

<cfloop INDEX="Checked_ITEM" LIST="#Form.ITEM_ID#">

<cfquery Name="#Check_Box_Insert#" DataSource="MyDB">

INSERT INTO MyTABLE (CUSTOMER_ID,INSTANCE_ID,ITEM_ID)
VALUES ('#Form.CUSTOMER_ID#', '#Form.INSTANCE_ID#', '#Checked_ITEM#')

</cfquery>

</cfloop>

---

If all checkboxes are selected, the data inserts as:

147 , 23445 , 4
148 , 23445 , 4
149 , 23445 , 4

(I realize that there is a lot more that can be embellished in both the form page and the processor page.)

[BKBK]

Two things.

1) I would replace the code in the action page by the version below. This one is easier to follow, hence to debug.

2) Apply cfoutput to the input tags, for example, as follows

<cfoutput>
<input type="hidden" name="CUSTOMER_ID" value="#CUSTOMERS.CUSTOMER_ID#">
<input type="hidden" name="INSTANCE_ID" value="#INSTANCE.INSTANCE_ID#">
</cfoutput>

As they now stand, your form is literally sending the strings #CUSTOMERS.CUSTOMER_ID# and #INSTANCE.INSTANCE_ID# to the database.

[BKBK]

Without cfoutput, the line

<input type="hidden" name="CUSTOMER_ID" value="#CUSTOMERS.CUSTOMER_ID#">

will cause the form to send the text #CUSTOMERS.CUSTOMER_ID# literally to the database.