5 Replies Latest reply on Apr 25, 2006 11:37 AM by dempster

    adding multiple row and column data with a form

    muellertj
      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.
        • 1. Re: adding multiple row and column data with a form
          Dan Bracuk Level 5
          Individual text fields for every var is the way I would do it.
          • 2. Re: adding multiple row and column data with a form
            muellertj Level 1
            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.
            • 3. Re: adding multiple row and column data with a form
              Dan Bracuk Level 5
              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.
              • 4. adding multiple row and column data with a form
                dempster Level 1
                So the different columns are grouped with rows?
                • 5. Re: adding multiple row and column data with a form
                  dempster Level 1
                  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>