Coldfusion programming: How to submit muliple records at the same time that have the same field names.


Have you ever needed to have a web page, dispaly in a spreadsheet layout, with the same fields listed on multiple rows, with only one submit button.

Maybe, you have wanted to develop a survey questionnaire, which would have multiple questions with the same database field names, and only one submit button. Basically, your database would have fields, such as; QUESTION, ANSWER (just a simple example). The web page may have 10 questions on it, which would be 10 rows. You want the user to answer as many questions as applicable, and hit a single submit button.

You needed a way to loop through and insert each as it's own record, but with the same field names in the database. Also, you may have up to 10 rows of fields for the user to complete, but they may only answer 6 rows.

HTML example of form fields

In this example, we have a display of 4 rows with 3 columns, or 3 fields in the database and only one submit button. Because we are using the same field names, we will have to loop through the insert of the data into the database. We will need to make sure we are not just inserting blank records for the 4 rows of fields. I will use the EVALUATE function for checking each field when submitted.

The form property for the field and id will have basically a row number at the end of each, to work with the looping of the inserting of data. Let's say the 3 field names are: PRODUCTID, DESCRIPTION, QTY

Typically, you would see:
<cfinput type="text" name="productid" size="10" maxlength="10">

In this process, we will modify it, just a little by adding a row number to the name and id properties:
<cfinput type="text" name="productid1" id="productid1" size="10" maxlength="10">

So, you would end up with:
<cfinput type="text" name="productid1" id="productid1" size="10" maxlength="10">
<cfinput type="text" name="productid2" id="productid2" size="10" maxlength="10">
<cfinput type="text" name="productid3" id="productid3" size="10" maxlength="10">
<cfinput type="text" name="productid4" id="productid4" size="10" maxlength="10">

Use this same syntax with the other 3 fields.

<cfform action="?submitdata=yes" method="post">
<table border=0 cellpadding=0 cellspacing=2>
<td valign=top align=center>PRODUCT ID</td>
<td valign=top align=center>DESCRIPTION</td>
<td valign=top align=center>QTY</td>
<td valign=top><cfinput type="text" name="productid1" id="productid1" size="10" maxlength="10"></td>
<td valign=top><cfinput type="text" name="description1" id="description1" size="40" maxlength="60"></td>
<td valign=top><cfinput type="text" name="qty1" id="qty1" size="2" maxlength="4"></td></tr>
<td valign=top><cfinput type="text" name="productid2" id="productid2" size="10" maxlength="10"></td>
<td valign=top><cfinput type="text" name="description2" id="description2" size="40" maxlength="60"></td>
<td valign=top><cfinput type="text" name="qty2" id="qty2" size="2" maxlength="4"></td></tr>
<td valign=top><cfinput type="text" name="productid3" id="productid3" size="10" maxlength="10"></td>
<td valign=top><cfinput type="text" name="description3" id="description3" size="40" maxlength="60"></td>
<td valign=top><cfinput type="text" name="qty3" id="qty3" size="2" maxlength="4"></td></tr>
<td valign=top><cfinput type="text" name="productid4" id="productid4" size="10" maxlength="10"></td>
<td valign=top><cfinput type="text" name="description4" id="description4" size="40" maxlength="60"></td>
<td valign=top><cfinput type="text" name="qty4" id="qty4" size="2" maxlength="4"></td></tr>
<td valign=top><cfinput type="text" name="productid5" id="productid5" size="10" maxlength="10"></td>
<td valign=top><cfinput type="text" name="description5" id="description5" size="40" maxlength="60"></td>
<td valign=top><cfinput type="text" name="qty5" id="qty5" size="2" maxlength="4"></td></tr>
<td colspan=3 align=right><input type="submit" value="submit"></td></tr>

QUERY section

Here is the sample code for the actual insertion of the data into the database. Notice the line, <cfloopfrom="1"to ="4"index="i">
"4" is for the number of rows in the form.

<cfif isDefined('submitdata')>

<cfloop from="1" to ="4" index="i">

<cfquery name="insertrecs" datasource="yours"> 
		 (	productid,
						<cfif isDefined("form.productid#i#") and Evaluate("form.productid#i#") NEQ ''>	
						<cfif isDefined("form.description#i#") and Evaluate("form.description#i#") NEQ ''>	
						<cfif isDefined("form.qty#i#") and Evaluate("form.qty#i#") NEQ ''>	


I hope the above example helps those looking for a way to insert multiple answers into the same field names in the database.

Check out some of the other Coldfusion articles below:

More by this Author

  • Gun Laws in Georgia

    Firearms can be a very dangerous tool in the hands of the wrong person. Every state has their own gun laws and regulations. For the state of Georgia, the laws are pretty straight forward and easy to abide by. Before you...

  • Tips For How to Stain Concrete Floors

    You don't always have to tile, carpet, or hardwood your floors. The new thing is to just stain the concrete that's already there. It's a lot of work, but if done properly, your home will look gorgeous. If you're...

  • How to Build Wooden Roof Trusses

    You may refer to a truss as the rafter, but it's basically the skeleton of the roof, carrying the weight of the frame and supporting the walls of the building. Trusses are very important to preventing the walls from...

Click to Rate This Article