Inserting multiple records in to a db table

[dmmacd]

I am a bit stuck and need some help inserting multiple records in to a db table.

I have a form that is going to insert several products at once in to a cart table. The customer types in the quantity of each product they want, then they hit the submit button to insert those products in to the cart table. I am not sure how to loop through and insert all the products at once and as seperate records!?

The form looks like this:

<cfoutput query=&quot;productDetails&quot;>
<form action=&quot;cart.cfm?action=insert&quot; method=&quot;post&quot;>
<input type=&quot;text&quot; value=&quot;0&quot; name=&quot;QTY&quot; maxlength=&quot;5&quot; size=&quot;2>
<input type=&quot;hidden&quot; name=&quot;prodID&quot; value=&quot;#prodID#&quot; / >
<input type=&quot;hidden&quot; name=&quot;price&quot; value=&quot;#price#&quot; / >
<input type=&quot;hidden&quot; name=&quot;stamp&quot; value=&quot;#stamp#&quot; / >
</cfoutput>
<input type=&quot;submit&quot; name=&quot;submit&quot;></form>

How do I loop through and insert all of the records at once?Also, if the value of &quot;QTY&quot; is &quot;0&quot;, how do I prevent that record from being inserted?

Thank you!!

 

[CFProg]

<form action=&quot;cart.cfm?action=insert&quot; method=&quot;post&quot;>
<cfoutput query=&quot;productDetails&quot;>
<input type=&quot;text&quot; value=&quot;0&quot; name=&quot;QTY_#prodID#&quot; maxlength=&quot;5&quot; size=&quot;2>
<input type=&quot;hidden&quot; name=&quot;prodID_#prodID#&quot; value=&quot;#prodID#&quot; / >
<input type=&quot;hidden&quot; name=&quot;price_#prodID#&quot; value=&quot;#price#&quot; / >
<input type=&quot;hidden&quot; name=&quot;stamp_#prodID#&quot; value=&quot;#stamp#&quot; / >
</cfoutput>
<input type=&quot;submit&quot; name=&quot;submit&quot;>
</form>

This will create input fields for each row of your products in your database. _#prodID# refers to the primary key in each row.

At the action page, you should know how many products you have and what they are. Probably execute a query to find that out. You should expect all those form fields for each of your products. Create an insert query for each product, this will give you flexibility in checking QTY_#prodID# for each product to see if it is a 0. In which case don't insert.

 

[dmmacd]

Hey, thanks for the reply!

Actually, I don't know how many products there are...the product lists are created dynamically and will be changing often. That's where I am running in to problems. On the action page I am unsure how to create a query for each product (to find out how many there are and what they are), nor how to create a query to insert each product.

Thanks.

 

[ecobb]

Try something like this on your action page:

<cfquery name=&quot;get_products&quot;>
Select ProdID
From Products
</cfquery>

<cfoutput query=&quot;get_products&quot;>
<cfif IsDefined(&quot;Form.prodID_#prodID#&quot>
<cfset ProductID = Form.prodID_#prodID#>
<cfif ProductID NEQ 0>
<cfquery name=&quot;insert_stuff&quot;>
Insert Into Whatever(ProdID, etc, etc, etc)
Values(#ProductID#, etc, etc, etc)
</cfquery>
</cfif>
</cfif>
</cfoutput>

Hope This Helps!

Ecobb
- I hate computers!