Skip to main content
J
Joe_Science
Known Participant
January 14, 2010
Question

update and insert using checkboxes. Help!!

  • January 14, 2010
  • 3 replies
  • 932 views

I need to be able to insert and/or update into the database.  I am using MySQL and CF8.   What is the query for updating and also inserting the data.  I am a novice with coldfusion.  Here is my output below and what needs to get inserted into the database where programid = #programid#. Thanks in advance.

<cfoutput query="getProducts" startrow="#url.startRow#" maxrows="#maxrows#">

     <tr>

          <td><input type="checkbox" value="#productID#" name="productid" <cfif getProducts.price neq "">checked</cfif></td>

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

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

          <td>#getProducts.department#</td>

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

          <td><input type="text" name="price" value="#dollarformat(getProducts.price)#"></td>

     </tr>

</cfoutput>

    This topic has been closed for replies.

    3 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    January 18, 2010

    I doubt whether checkboxes are the appropriate choice for you. Checkboxes are for on/off situations. Whereas, you seem to want to save text or numbers.

    In any case, I cannot even see where the checkbox tag ends. What are all those values doing there?

    Owainnorth
    Owainnorth
    Inspiring
    January 18, 2010

    Use examples from the code I've done to check for the existence of the variable CHK_<id> or similar. Then wrap the necessaries inside that.

    Owainnorth
    Owainnorth
    Inspiring
    January 15, 2010

    Hi matey

    Not sure what you mean by the mention of programid? Do you mean productID? Also, what relevance does the checkbox have? From what I can tell, you want to update the price for any checked products, so let's go with that for now and hope I'm not massively mistaken. Tabular bulk update screens are never fun, but hey ho it's what we do.

    Try using your CFOUTPUT to create your rows like so:

    <tr>
        <td><input type="checkbox" value="1" name="productid_#productID#"

                    <cfif getProducts.price neq "">checked</cfif></td>
        <td>#getProducts.modelnumber#</td>
        <td>#getProducts.modeldescription#</td>
        <td>#getProducts.department#</td>
        <td>#getProducts.categoryname#</td>
        <td><input type="text" name="price_#productID#" value="#dollarformat(getProducts.price)#"></td>
    </tr>

    So you're actually *naming* your FORM fields as, for example, FORM.productid_2342. Its price field is called price_2342. Therefore, once submitted, do a loop over the FORM scope. Do a REMatch() on the key name for "productid_", if it matches then strip off the "productid_" and you've got your ID. Get the relevant "price_" field and you've then got the ID and price you need to do an update to the database.

    J
    Joe_ScienceAuthor
    Known Participant
    January 15, 2010

    Not sure I quite understand. What is the exact code for the loop and also the update/insert query.

    Owainnorth
    Owainnorth
    Inspiring
    January 16, 2010

    Right, I've knocked up some code that I've tested, so hopefully this'll sort you out. Paste this in as an entire page:

    <cfif structKeyExists(FORM,"sub_update_prices")>
        <!--- Loop through the entire FORM scope --->
        <cfloop collection="#FORM#" item="key">
            <!--- if this key starts with "PRICE_" --->
             <cfif reFindNoCase("^PRICE_[0-9]",key) >
                <cfset thisID = replace(key,"PRICE_","") />
                <cfset thisPrice = FORM[key] />
                <cfquery datasource="DEV">
                    UPDATE        products
                    SET            pro_price = <cfqueryparam cfsqltype="cf_sql_numeric" scale="2" value="#thisPrice#" />
                    WHERE        pro_id = <cfqueryparam cfsqltype="cf_sql_numeric" value="#thisId#" />
                </cfquery>
            </cfif>
        </cfloop>
    </cfif>

    <cfquery datasource="DEV" name="qGetProducts">
        SELECT         pro_id,
                       pro_description,
                       pro_price
        FROM           products
    </cfquery>

    <!--- A table containing all your products --->
    <form action="<cfoutput>#CGI.SCRIPT_NAME#</cfoutput>" method="post">
        <table style="border:1px solid black;">
            <tr>
                <th>Product</th>
                <th>Price</th>
            </tr>
            <cfoutput query="qGetProducts">
                <tr>
                    <td>#pro_description#</td>
                    <td>
                        <input type="text" name="price_#pro_id#" value="#pro_price#" />
                    </td>
                </tr>
            </cfoutput>
        </table>
        <input type="submit" name="sub_update_prices" value="Update Prices" />
    </form>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices