Adding Multiple Records to a Table.

Alright, this one is long at first, but once you get the hang of it, it can be really useful. Basically, we are going to create a 3 page application that will allow us to choose the number of people to add to a database, enter these people?s info onto a second page, and add all the records at one time to a database on page three.

Here?s the breakdown.

The first page will allow a user to enter the number of things they want to add (we?ll use people and their age). This page will be a form we?ll call userchoose.cfm.

The second page will be the form used to enter the data. This page and all it?s fields will be dynamically created based on the number the user entered on the first page. This page is known as enterdata.cfm.

The third page will evaluate the fields and then insert those fields into a database.

Here we go:

Page One: Userchoose.cfm

First, create a form with one text input box and a submit button. Name the input box pcount. Make sure the form is set to Post to a page called enterdata.cfm. Once you get the hang of this, you can spice up your input page. For now, just use the code below:

<form name="form1" method="post" action="enterdata.cfm">
How many people do you want to enter? 
<input type="text" name="pcount">
<input type=
"submit" name="Submit" value="Submit">
</form>

Save the Page. Onto page two.

Page Two: Enterdata.cfm

Alright, create a new page and insert a form. Add a table with three rows and three columns. In the first row, label the columns Name and Age. In the second row, put two input boxes. Call the first one name_ and put it in the first column. Call the second one age_ and put it in the second column. In the third row, add a submit button. This form should post to insertdata.cfm. After the submit button, create a hidden field called howmany. Set the value to "<cfoutput>#Form.pcount#</cfoutput>".

Ok, here the tricky part. What we are going to do is use a cfloop to create the number of input rows in our table. Above the <tr> used to start the second table row, we are going to create a variable called Peoplecount and we?ll set it to zero with this: 

<cfset Peoplecount = 0>

Now for the loop. We need a simple loop to start at zero and end at the number the user submitted on the first page. The loop will increase by one until it reaches the number the user submitted on the first page.

<cfloop index="Add" from="1" to="#form.pcount#" step="1">

Move the</cfloop> tag to right before the start of row three.

Now, after the loops starts, we need to increase the Peoplecount variable we created. So simply use this: 

<cfset Peoplecount = PeopleCount + 1>

So we have our loop, but we still have a problem. The name and age fields all need to be named according to the Peoplecount variable. These names will be used on the next page to insert the records into the database. So, in the code for the input box, change the name to read:

"Name_<cfoutput>#Peoplecount#</cfoutput>" for the Name field and 
"Age_<cfoutput>#Peoplecount#</cfoutput>" for the Age field. 

This will dynamically name those boxes Name_1, Age_1. The loop will run and they will be name Name_2, Age_2 and so on until the number the user submitted on the first page. Your form should look similar to the one below. Here?s the code.

<form name="form1" method="post" action="insertdata.cfm">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr>
        <td width=
"50%">Name</td>
        <td width=
"50%">Age</td>
    </tr>

    <cfset Peoplecount = 0>
    <cfloop index="Add" from="1" to="#form.pcount#" step="1">
        <tr>
            <cfset Peoplecount = PeopleCount + 1>
            <td><input name="Name_<cfoutput>#Peoplecount#</cfoutput>" type="text" id="name_"></td>
            <td>
<input name="Age_<cfoutput>#Peoplecount#</cfoutput>" type="text" id="age_"></td>
        </tr>

    </cfloop>
    <tr>
        <td>
&nbsp;</td>
        <td> 

            <input type="submit" name="Submit" value="Submit">
            <input name=
"HowMany" type="hidden" id="HowMany" value="<cfoutput>#Form.pcount#</cfoutput>">
        </td>
    </tr>
</table>

</form>


Page Three: Insertdata.cfm
Now to add those people to a database.

This part is pretty easy. Create a blank page and start at line one. We need a variable like on the last page, so first we

<cfset Pcount = 0>

Now we?ll start our loop to insert the data. Using the same technique we used to create the multiple rows, start a <cfloop> from 1 to the original number the user entered. Again, we need to go one record at a time, so set the step to 1. Also, we add one to our Pcount.

<cfloop index="Add" from="1" to="#form.howmany#" step="1">
<cfset Pcount = Pcount + 1>



Now the cool part. We create a variable called Name and set it equal to 'Form.Name_#Pcount#'. So, right now Name actually equal Form.Name_1. We use the Evaluate to get the value of Form.Name_1.

<cfset Name = "Form.Name_#Pcount#">
<cfset Name = Evaluate(Name)>

Now, Name is set to the value of whatever the first name typed on the last page was. Do the same thing with Age.

<cfset Age = "Form.Age_#Pcount#">
<cfset Age = Evaluate(Age)>

A simple insert and an </cfloop> and you?re done.

<cfquery datasource="YourSource" name="InsertData">
    Insert into YourTable (Name, Age)
    values (#Name#, #Age#)
</cfquery>

Here?s the code all at once:

<cfset Pcount = 0>
<!-- Start Loop -->
<cfloop index="Add" from="1" to="#form.howmany#" step="1">
    <cfset Pcount = Pcount
+ 1>
    <cfset Name =
"Form.Name_#Pcount#">
    <cfset Name = Evaluate(Name)>
    <cfset Age =
"Form.Age_#Pcount#">
    <cfset Age = Evaluate(Age)>
    <cfquery datasource=
"YourSource" name="InsertData">
        Insert into YourTable (Name, Age)
   
     values ('#Name#', '#Age#')
    </cfquery>
</cfloop>


This is a very powerful tutorial once you master it. You can edit multiple records at once, delete them, add multiple data sets to multiple tables. If you like it, PLEASE VOTE for me. I could use the cash!!!

About This Tutorial
Author: Mike Daugherty
Skill Level: Intermediate 
 
 
 
Platforms Tested: CF5,CFMX,CFMX7,BlueDragon
Total Views: 123,679
Submission Date: February 01, 2006
Last Update Date: June 05, 2009
All Tutorials By This Autor: 1
Discuss This Tutorial
  • I found this to be very useful. Thank you for posting it!

Advertisement


Website Designed and Developed by Pablo Varando.