2 Replies Latest reply on Jan 28, 2008 10:23 AM by (kaosiso_Anaekwe)

    How do I insert multiple rows from a single form ...

      How do I insert multiple rows from a single form?

      This form is organised by a table. (just as in an excel format)

      I have 20 items on a form each row item has five field

      +++++++++++ FORM AREA+++++++++++++++++++++++++++++++++++++++++++++++++++++
      +Product| qty In | Qty Out | Balance | Date +
      +------------------------------------------------------------------------+
      +Item1 | textbox1 | textbox2 | textbox3 | date +
      + |value = $qty_in1|value= &qty_out1|value=$balance1|value=$date1 +
      +------------------------------------------------------------------------+
      +Item 2 | textbox1 | textbox2 | textbox4 | date +
      + |value = $qty_in2|value= $qty_out1|value=$balance2|value=$date2 +
      +------------------------------------------------------------------------+
      + Item3 | textbox1 | textbox2 | textbox3 | date +
      +------------------------------------------------------------------------+
      + contd | | | +
      +------------------------------------------------------------------------+
      + item20| | | | +
      +------------------------------------------------------------------------+
      + + + SUBMIT + ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
      Database Structure
      +++++++++++++++++
      + Stock_tabe +
      +---------------+
      + refid +
      +---------------+
      + item +
      +---------------+
      + Qty In +
      +---------------+
      + Qty Out +
      +---------------+
      + Balance +
      +---------------+
      + Date +
      +++++++++++++++++

      Let's say for example user have to the use the form to enter all 10 items or few like 5 on their stock form into 4 different textbox field each lines of your form, however these items go into a "Stock_table" under Single insert transaction query when submit button is pressed.

      Please anyone help me out, on how to get this concept started.
        • 1. Re: How do I insert multiple rows from a single form ...
          Level 1
          Hello,

          I have a way to do this, but it would take some hand coding on your part. If you feel comfortable hand writing php code and doing manual database calls, specificaly database INSERT calls you should be fine.

          Create a custom form using the ADDT Custom Form Wizard that has all the rows and fields you need. This may take a bit if you are adding the ability for up to 20 rows, as per your diagram of the form area. The nice thing about using ADDT to create the form is that you can setup the form validation at the same time. Leave the last step in the Custom Form Wizard blank. You can add a custom database call here, but I would leave it blank.

          Next, under ADDT's Forms Server Behaviors, select Custom Trigger. At the Basic tab, you enter your custom php code that will be executed. Here you are going to want to put your code that will check if a value has been entered in the form and then do a database INSERT operation on the Stock_table with that row. The advanced tab lets you set the order of operations and the name of the Custom Trigger. By default, it is set to AFTER. This means that the Custom Trigger will get executed AFTER the form data is processed by the Custom Form Transaction.

          I usually just enter TEST into the "Basic" tab of the Custom Trigger. Then set my order of operations in the "Advanced" tab and close the Custom Trigger. Then I go to the code view for that page in Dreamweaver and find the Custom Trigger function and edit the code manually. It's much easier this way because the Custom Trigger wizard does not show you formatting on the code, and you don't have to keep opening the Wizard to edit and test your code.

          Your going to have to have the Custom Trigger fuction do a test on the submitted form data. If data is present, then INSERT into database. Here's a basic example of what you need to do:

          In your code view, the Custom Trigger will look something like this:

          function Trigger_Custom(&$tNG) {

          if($tNG->getColumnValue("Item_1")) {

          $item1 = $tNG->getColumnValue("Item_1");
          $textbox1_1 = $tNG->getColumnValue("Textbox_1");
          $textbox1_2 = $tNG->getColumnValue("Textbox_2");
          $textbox1_3 = $tNG->getColumnValue("Textbox_3");
          $date1 = $tNG->getColumnValue("Textbox_3");
          $queryAdd = "INSERT INTO Stock_table
          (item, Qty_In, Qty_Out, Balance, Date) VALUES($item1, $textbox1_1, $textbox1_2, $textbox1_3, $date1)"
          $result = mysql_query($queryAdd) or die(mysql_error());
          }

          }

          This code checks to see if the form input field named Item_1 is set. If so, then get the rest of the values for the first item and insert them into the database. You would need to do this for each row in your form. So the if you let the customer add 20 rows, you would need to check 20 times to see if the data is there or write the code so that it stops once it encounters an empty Item field. To exit a Custom Trigger, you can return NULL; and it will jump out of the function. You can also throw custom error message out of triggers, but this post is already way to long to get into that.

          $tNG->getColumnValue("Item_1") is used to retrieve the value that was set by the form input field named Item_1. This field is named by the Custom Form Wizard when you create your form. You can see what all the input filed names are by looking in the code view for something like:

          // Add columns
          $customTransaction->addColumn("Item_1", "STRING_TYPE", "POST", "Item_1");

          There will be one for each field you created with the Custom Form Wizard.

          Unfortunately, I don't have an easy way to do what you need. Maybe there is a way, but since none of the experts have responded, I thought I would point you in a direction. You should read all you can about Custom Triggers in the ADDT documentation/help pdf to give you more detailed information about how Custom Triggers work.

          Hope this helps.

          Shane
          • 2. Re: How do I insert multiple rows from a single form ...
            Level 1
            Hello Shane,
            Thanks for your suggestion, I see that there is only a hard way the only way doing manual scripting. I tried it out without DDT but I get error were not all the form field gets submitted by the query even though a fields were filled out.
            Now I want to go back to DDT custom form wizard, so I can take advantage of the form validation sequence,

            I gonna try it out, I will keep you posted if I succeed or not

            Thanks
            kossanah