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>
<tr>
<td valign=top align=center>PRODUCT ID</td>
<td valign=top align=center>DESCRIPTION</td>
<td valign=top align=center>QTY</td>
</tr>
<tr>
<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>
<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>
<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>
<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>
<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>
<tr>
<td colspan=3 align=right><input type="submit" value="submit"></td></tr>
</table>
</cfform>

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"> 
         INSERT INTO ORDERS
		 (	productid,
		 	description,
			qty
		 	
			) 
         VALUES 
                  (    		
						<cfif isDefined("form.productid#i#") and Evaluate("form.productid#i#") NEQ ''>	
				  		#Evaluate("form.productid#i#")#,
						<cfelse>
						Null,
						</cfif>
						
						<cfif isDefined("form.description#i#") and Evaluate("form.description#i#") NEQ ''>	
				  		'#Evaluate("form.description#i#")#',
						<cfelse>
						Null,
						</cfif>
						
						<cfif isDefined("form.qty#i#") and Evaluate("form.qty#i#") NEQ ''>	
				  		'#Evaluate("form.qty#i#")#',
						<cfelse>
						Null,
						</cfif>
					 			
				) 
			  
   </cfquery> 
      
</cfloop>   

</cfif>

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

  • How to Install a Ceiling Fan
    8

    When installing a home ceiling fan you want to make sure that your follow several safety tips and warnings so that you prevent fire hazards, electrical shock, and personal injury. Read carefully when looking over the...

  • How to Build Wooden Roof Trusses
    23

    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...

  • How to Get Rid of Mice In and Around The House?
    5

    Until this year, the only time that I've had mice or rats in the house were when my daughter had pet mice and rats, and one of those times we did have one loose in the house. (Science fair project got loose.) Anyway,...


Click to Rate This Article
working