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:
- Adobe Coldfusion Tip For Finding Duplicate Records In SQL Database
- Adobe Coldfusion Help: Query To Spreadsheet
- Upload a pipe delimited file and insert into SQL table with ColdFusion
- Monitor Hard Drive Space And Email Alert With Adobe Coldfusion
- Coldfusion SQL Tips: Creating Tables, Backup, Fields
- Coldfusion Programming: Accessing a shared network drive
- Coldfusion: allowing max file size when uploading
- Coldfusion: Encrypting and Decrypting Data
- How To Upload Multiple Files With ColdFusion
- How to pause a web page process with ColdFusion
Related
Debunking the Internet Myth of the A.T. Field
Magnetic Fields and their Theoretical Collimation
The Basis of Electromagnetic Interference (EMI): Electromagnetism
Dynamically Display Images in Image Slider using PHP and MySql
Torsion Field Hyperdimensional Physics Unlocks the Matrix of Consciousness and Reality
