Checkbox help needed! - database inserting and updating

[Joe_Science]

I am using CF8 and MySQL.  I have a query that is returning a list of products. The query I have written returns a list of products each with a checkbox and a input text field for price.

I need to be able to insert the record into another table called specials and also be able to update it if necessary, If it's checked it gets inserted and updated. thus if i go back and do an update I can see which items were checked and the corresponding prices.

I need help writing the insert and update queries. Any help would be much appreciated,  Thanks in advance.  Below is the form.

<cfoutput query="getProducts">

<tr<cfif currentrow mod 2> class="odd"</cfif>>

    <td><div align="left"><input type="checkbox" name="OrderCompleted" value="##"></div></td>

            <td>#getProducts.modelnumber#</td>

            <td>#getProducts.modeldescription#</td>

            <td>#getProducts.categoryidparent#</td>

            <td>#getProducts.categoryname#</td>

<td><input type="text" name="price" value=""></td>

</tr>

</cfoutput>

[Aquitaine]

First, you want some kind of unique ID (whether guid or int + identity) in the products table (or wherever your getProducts query is pulling from). Let's call that productID. In this case, an int + identity will be a bit easier to work with, so unless you have bajillions of products, it should be fine. That means (at the risk of telling you what you already know) that every product will have a productID whose value will be '1' for the first record in the table, 2 for the second, and so on.

Then, all you need in your checkboxes & price field is to have this product ID somewhere in the field name. There may be a more graceful way, but what I typically do is this (inside your cfoutput query="products"):

<cfset checkBoxName = "OrderCompleted" & productID>

<cfset priceFieldName = "price" & productID>

<tr<cfif currentrow mod 2> class="odd"</cfif>>

    <td><div align="left"><input type="checkbox" name="#checkBoxName#" value="#productID#"></div></td>

            <td>#getProducts.modelnumber#</td>

            <td>#getProducts.modeldescription#</td>

            <td>#getProducts.categoryidparent#</td>

            <td>#getProducts.categoryname#</td>

<td><input type="text" name="#priceFieldName#" value=""></td>

</tr>

Then, on the form processor, run your getProducts query again:

<cfif isDefined("form.submit")>

     <cfoutput query="getProducts">

          <cfset productIDFieldName="##form.orderCompleted" & productID & "##">

           <cfset priceFieldName="##form.priceFieldName" & productID & "##">

           <cfset productIDVal=evaluate(productIDFieldName)>

           <cfset priceFieldVal=evaluate(priceFieldName)>

          <!--- then check if the productIDVal is in your specials table, and if it is, UPDATE, and if it isn't, INSERT --->

     </cfoutput>

</cfif>

Unless you need help just on the syntax for the INSERT and UPDATE queries? The only thing to really understand above the above method is the use of dynamic form field names and the evaluate function. You might get a better answer from some of the pros around here, but I use this method all the time. If it's on a secure site (say, an intranet) you could also just keep a comma-delimited list of productIDs in the form - say, under that first bold cfset section:

<input type="hidden" name="productIDList" value="#productID#" />

and then, instead of re-running your getProducts query in the processor, you'd replace the cfoutput query line with:

<cfloop list="#form.productIDList#" index="x">

and then you'd have '##form.orderCompleted' & x & '##' instead of the productID query - this insulates you from someone else changing your products table while you're working on the first form, which would break things.