ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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

Updated on October 14, 2012


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:

Click to Rate This Article