adding multiple row and column data with a form

[Tonyam]

I need to add multiple row and column data to a database using an online form and am not sure what the best method of data entry and inserting would be.

The data should look like this:

COLUMN1 COLUMN2
var1 var1
var2 var2
var3 var3
etc.......

When creating the form should I have individual text fields for every var or would it be better to have a text field where they list the data. I'm just not sure where to start.

Any advice would be great.

[Dan_Bracuk]

Individual text fields for every var is the way I would do it.

[Tonyam]

I don't think I was clear in my original explanation. I need all the variables in Column 1 to go into the same column in the database and of the variables in Column 2 to go into another column in the database. If I create a unique text field for each variable won't they each need their own column in the database? I need them to be inserted into one column, and the variables in Column 1 correspond to the variables in Column 2. I know I need to do a cfloop statement, but I need a little more guideance on how to set up the form.

[dempster]

So the different columns are grouped with rows?

[dempster · Accepted solution]

You can use row numbers in your field names to associate the columns with a particular record. For example, let's say you want 5 rows to enter last names and first names:

<CFSET maxrow = 5>
<CFLOOP INDEX="rownum" FROM="1" TO="#maxrow#"><CFOUTPUT>
Last name: <INPUT TYPE="text" NAME="lname#rownum#" SIZE="25">
First name: <INPUT TYPE="text" NAME="fname#rownum#" SIZE="15"><BR>
</CFOUTPUT></CFLOOP>

This will give you a series of fields such as lname1, fname1, lname2, fname2, etc.

In your form, you can also include a hidden field to indicate the number of rows.

When you process the form, you loop again to insert each row:

<CFLOOP INDEX="onerow" FROM="1" TO="#Form.numrows#">
<CFQUERY NAME="addRec" DATASOURCE="mydsn">
INSERT INTO mytable (lastname, firstname)
VALUES ('#Form["lname" & onerow]#)', '#Form["fname" & onerow]#')
</CFQUERY>
</CFLOOP>

[Dan_Bracuk]

Regarding:
If I create a unique text field for each variable won't they each need their own column in the database?

No.
As long as you know the name of the form field and what you are supposed to do with it, write your code accordingly.