How to update multiple rows and columns at once in a database table from a user-submitted form.

By Abdullah Yahya

Most database operations as a result of user input would either insert, update or delete one or more rows with one or more fields from a database. The table below shows the most common types of database operations and how they're coded.

  1. INSERT one record
    Coding Method: Single CFQUERY tag
    Example:

    <cfquery ....>
              INSERT INTO table (field1, field2, ...) VALUES (value1, value2, ....)
    </cfquery>

  2. DELETE one record
    Coding Method: Single CFQUERY tag
    Example:

    <cfquery ....>
              DELETE FROM table WHERE field = value
    </cfquery>

  3. UPDATE one record
    Coding Method: Single CFQUERY tag
    Example:

    <cfquery ....>
              UPDATE table SET field1 = value1, field2 = value2, ..... WHERE id_field = id
    </cfquery>

  4. INSERT multiple records
    Coding Method: Loop over a CFQUERY tag
    Example:

    <cfloop ....>
              <cfquery ....>
                        INSERT INTO table (field1, field2, ....) VALUES (value1, value2, ....)
              </cfquery>
    </cfloop>


  5. DELETE multiple records
    Coding Method: Single CFQUERY tag using comma-delimited list of IDs in WHERE clause
    Example:

    <cfquery ....>
              DELETE FROM table WHERE id_field IN (id1, id2, ....)
    </cfquery>

  6. UPDATE multiple records
    Coding Method:
    1. Solution 1:
      Delete all rows and then insert new rows by looping over a CFQUERY tag with an INSERT sql statement.
      Drawback: If primary keys of the old rows are linked to other tables as foreign keys and if those keys were automatically numbered by the database server, you would not be able to insert the new rows using the same old keys which breaks the link between the tables. If the keys were manually created, you'd have to do extra work and save the old key values and use them when inserting the new rows making sure each new row gets the correct old key.
    2. Solution 2:
      Update all rows by looping over a comma-delimited list of submitted form values using the index of each item in the list as a counter to determine which row to update.
      Drawback: If you leave some fields empty, not all returned comma-delimited lists will have the same number of items thereby causing errors in which field and/or row to update.
    3. Solution 3:
      Embed the primary key of each field's row to the value of the name attribute of each form element and include a hidden form element containing a comma-delimited list of each rows primary key id. On submission, loop through the list of primary key values and dynamically generate the SQL to update the rows in the table.

Writing code to perform the operations above is fairly straightforward although sometimes updating multiple records with multiple fields at once may not seem trivial. While working for a non-profit in downtown San Francisco, I was faced with fixing a bug caused whenever users would not enter in all fields in a form to update a bunch of records and their fields. When the form was submitted, an error would occur because the method of updating the rows in the database table relied on the returned comma-delimited list of values from the form submission. Since Coldfusion MX ignores empty elements in a list (e.g. a,b,c,,,,,,,d is equal to a,b,c,d), the looping over the elements in the lists resulted in either an error or some fields getting wrong values. In this article, I’m going to show you how to update multiple records and fields in a table that will ALWAYS work regardless of whether the user leaves or makes any fields empty in a form.

Let’s say you have a web-based form like the one below that you use to make daily updates to some product information.

Product Name Regular Price Sale Price Quantity On Sale?
Apples
Oranges
Bananas
Peaches

A sample database table called PRODUCTS that could store this information is:

COLUMN DATA TYPE LENGTH ALLOW NULLS  
productid
int
4
 
Primary Key
productName
varchar
100
 
 
regularPrice
varchar
50
 
 
salePrice
varchar
50
YES
 
quantity
int
4
 
 
onSaleYN
bit
1
 
 

The contents of this PRODUCTS table would be something like:

productid productname regularprice saleprice quantity onSaleYn
1
Apples
2.25
1.75
20
1
2
Oranges
3.25
2.50
30
1
3
Bananas
4.25
3.50
25
0
4
Peaches
5.25
4.50
30
0

Normally the form would be prepopulated with the data from the table by querying the rows in the table and outputting it in a loop to create the form above as in

<cfquery name = "getproducts" datasource="somedatasource" .... >
          SELECT * FROM products
</cfquery>

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

<table>
<tr>
      <td> Product Name</td>
      <td> Regular Price </td>
      <td> Sale Price </td>
      <td> Quantity </td>
      <td> On Sale? </td>
</tr>

<cfoutput query="getproducts">
<tr>
      <td>#productName#</td>
      <td><input name="regularprice_#productid#" type="text" value="#regularprice#" size="15"></td>
      <td><input name="saleprice_#productid#" type="text" value="#saleprice#" size="15"></td>
      <td><input name="quantity_#productid#" type="text" value="#quantity#" size="15"></td>
      <td>
            <select name="onSaleYN_#productid#">
                  <option value="1" <cfif onSaleYN is 1> selected </cfif>>Yes</option>
                  <option value="0" <cfif onSaleYN is 0> selected </cfif>>No</option>
            </select>
     </td>
</tr>
</cfoutput>

<tr>
     <td colspan="5"><input type="submit" name="Submit" value="Submit"></td>
</tr>

</table>

<cfoutput>
     <input type="hidden" name="listofids" value="#ValueList(getproducts.productid)#">
</cfoutput>

</form>

Notice that the name attribute of each form element is dynamically generated so that it contains the product id which is needed to know which row the item belongs to. Also, a hidden form element named listofids contains a comma-delimited list of product ids that will be updated. Viewing the source of this code when its displayed in a browser would show:

<table>
<tr>
      <td> Product Name</td>
      <td> Regular Price </td>
      <td> Sale Price </td>
      <td> Quantity </td>
      <td> On Sale? </td>
</tr>

<tr>
      <td><input name="regularprice_1" type="text" value="3.25" size="15"></td>
      <td><input name="saleprice_1" type="text" value="2.50" size="15"></td>
      <td><input name="quantity_1" type="text" value="30" size="15"></td>
      <select name="onSaleYN_1">
            <option value="1" selected >Yes</option>
            <option value="0" >No</option>
      </select>
</tr>
<tr>
      <td><input name="regularprice_2" type="text" value="4.25" size="15"></td>
      <td><input name="saleprice_2" type="text" value="2.50" size="15"></td>
      <td><input name="quantity_2" type="text" value="30" size="15"></td>
      <select name="onSaleYN_2">
            <option value="1" selected >Yes</option>
            <option value="0">No</option>
      </select>
</tr>
<tr>
      <td><input name="regularprice_3" type="text" value="4.25" size="15"></td>
      <td><input name="saleprice_3" type="text" value="3.50" size="15"></td>
      <td><input name="quantity_3" type="text" value="25" size="15"></td>
      <select name="onSaleYN_3">
            <option value="1">Yes</option>
            <option value="0" selected >No</option>
      </select>
</tr>
<tr>
      <td><input name="regularprice_4" type="text" value="5.25" size="15"></td>
      <td><input name="saleprice_4" type="text" value="4.50" size="15"></td>
      <td><input name="quantity_4" type="text" value="20" size="15"></td>
      <select name="onSaleYN_4">
            <option value="1">Yes</option>
            <option value="0" selected >No</option>
      </select>
</tr>

</table>

<input type="hidden" name="listofids" value="1,2,3,4">

When you update the values in the form and click the submit button, the form posts to a page with the following code to handle updating the multiple rows with multiple fields regardless of whether all fields have values or not.

<cfloop index="id" list="#form.listofids#" delimiters=",">
      <cfquery datasource="somedatasource" .... >
            UPDATE products SET
            regularPrice = '#Evaluate("form.regularprice_#id#")#',
            salePrice = '#Evaluate("form.saleprice_#id#")#',
            quantity = '#Evaluate("form.quantity_#id#")#',
            onSaleYN = #Evaluate("form.onsaleYN_#id#")#
            WHERE productid=#id#
      </cfquery>
</cfloop>

The code above works by looping through each product id in the comma-delimited list of ids and then updating each row.

About the Author

Abdullah Yahya is the webmaster at Chabot College in Hayward, CA and founder of HangStop.com. He has experience building complete web systems from basic static sites to multi-tier, database-driven e-commerce sites. He holds a bachelors in engineering from UC Berkeley, a masters in computer science from San Francisco State University and is also a Macromedia Certified Advanced Coldfusion Developer. Abdullah has been coding in Coldfusion since 2001. More information on Abdullah can be found on his website at www.abdullahyahya.com.