2 Replies Latest reply on Apr 15, 2009 1:24 PM by akieru

    insert into two tables, how to insert multiple slave records

      Hi, I have a problem with insert into two tables wizard.

      The wizard works fine and I can add my records, but I need to enter multiple slave table records.

      My database:
      ---------------------------
      table: paper

      `id_paper` INTEGER(11) NOT NULL AUTO_INCREMENT,
      `make` VARCHAR(20) COLLATE utf8_general_ci NOT NULL DEFAULT '',
      `model` VARCHAR(20) COLLATE utf8_general_ci NOT NULL DEFAULT '',
      `gsm` INTEGER(11) NOT NULL,
      PRIMARY KEY (`id_paper`)

      table: paper_data

      `id_paper_data` INTEGER(11) NOT NULL AUTO_INCREMENT,
      `id_paper` INTEGER(11) NOT NULL,
      `value` DOUBLE(15,3) NOT NULL,
      `nanometer` INTEGER(11) NOT NULL,
      PRIMARY KEY (`id_paper_data`)

      I need to add multiple fields "value" and "nanometer"

      Current form looks like this:

      Make:
      Model:
      Gsm:
      Value:
      nanometer:

      I need it to look like this:

      Make:
      Model:
      Gsm:
      Value:
      nanometer:
      Value:
      nanometer:
      Value:
      nanometer:
      Value:
      nanometer:
      and so on.

      The field "id_paper" in table paper_data needs to get same id for entire transaction. Also how do I set default values for each field "nanometer" on my form the must be different (370,380,390 etc)?

      Thanks.
        • 1. Re: insert into two tables, how to insert multiple slave records
          Albert S. Level 3

          Hi Smile,

           

          Your first problem. I need to run some tests on the two tables insert wizard to see if you need to run a query to get the last record from the database before it inserts the new data with the next following number. Then you can take the last number and add 1 to it and you have the number that will be used for the next insert transaction.

           

          Second problem. If you have one database field but you want to have it filled with data from several text-input fields from your form, you will need to have the text-input fields arrayed with the same input.

           

          Standard input field:

           

          <input type="text" name="dbfield" value="1234">


          Combining multiple field values:

           

          <input type="text" name="dbfield[]" value="1234">

          <input type="text" name="dbfield[]" value="5678">

          <input type="text" name="dbfield[]" value="91011">

           

          Now dbfield is an array and should input into database field like:

           

          1234, 5678, 91011

           

          You might need to extract the array back to a string before insert. I don't fully remember but thats the overall idea.

          • 2. Re: insert into two tables, how to insert multiple slave records

            you can find an answer here: http://209.85.129.132/search?q=cache:PzQj57dsWmQJ:www.experts-exchange.com/Web_Development /Software/Macromedia_Dreamweaver/Q_23713792.html+Insert+Into+Two+Tables+Wizard&cd=3&hl=lt& ct=clnk&gl=lt

             

            This is a copy of the post:

             

            Hi experts,

            Im using ADDT to design a page that needs to insert one record into a master ALBUMS table, along with three records into a GENRES table, all linked by the primary, auto-incremented ALBUMS. ALBUM_ID.

            Ive tried many different ways of combining the Insert into Multiple Tables wizard and the insert record wizard with Link Transactions, all with no luck.  Either only the album info gets inserted, or a ALBUM_ID cannot be null error from MySQL.  Here is the structure of the tables

            ALBUMS

            ALBUM_ID, INT(11), Primary, Auto_Increment
            alb_name, varchar
            alb_release, YEAR
            USER_ID, int
            alb_image, varchar


            GENRES

            ALBUM_ID, int, NOT NULL
            GENRE_ID, int, NOT NULL
            ID, int, primary, auto-increment

            Many thanks in advance...

            ==========================================================================================

            //remove this line if you want to edit the code by hand
            function Trigger_LinkTransactions(&$tNG) {
              global $ins_genres;
              $linkObj = new tNG_LinkedTrans($tNG, $ins_genres);
              $linkObj->setLink("ALBUM_ID");
              return $linkObj->Execute();
            }
            function Trigger_LinkTransactions2(&$tNG) {
              global $ins_genres2;
              $linkObj = new tNG_LinkedTrans($tNG, $ins_genres2);
              $linkObj->setLink("ALBUM_ID");
              return $linkObj->Execute();
            }
            function Trigger_LinkTransactions3(&$tNG) {
              global $ins_genres3;
              $linkObj = new tNG_LinkedTrans($tNG, $ins_genres3);
              $linkObj->setLink("ALBUM_ID");
              return $linkObj->Execute();
            }
            //end Trigger_LinkTransactions trigger



            //-------------------------------------------------------------//
            //-----------------------Different Section---------------------//


            // Make an insert transaction instance
            //Add Record Genre 1
            $ins_genres = new tNG_insert($conn_MySQL);
            $tNGs->addTransaction($ins_genres);
            $ins_genres->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "VALUE", null);
            $ins_genres->registerTrigger("BEFORE", "Trigger_Default_FormValidation", 10, $detailValidation);
            $ins_genres->setTable("genres");
            $ins_genres->addColumn("GENRE_ID", "NUMERIC_TYPE", "POST", "GENRE_ID");
            $ins_genres->addColumn("ALBUM_ID", "NUMERIC_TYPE", "VALUE", "");
            $ins_genres->setPrimaryKey("ID", "NUMERIC_TYPE");

            // Add Record Genre 2
            $ins_genres2 = new tNG_insert($conn_MySQL);
            $tNGs->addTransaction($ins_genres2);
            $ins_genres2->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "VALUE", null);
            $ins_genres2->setTable("genres");
            $ins_genres2->addColumn("GENRE_ID", "NUMERIC_TYPE", "POST", "GENRE_ID2");
            $ins_genres2->addColumn("ALBUM_ID", "NUMERIC_TYPE", "VALUE", "");
            $ins_genres2->setPrimaryKey("ID", "NUMERIC_TYPE");

            // Add Record Genre 3
            $ins_genres3 = new tNG_insert($conn_MySQL);
            $tNGs->addTransaction($ins_genres3);
            $ins_genres3->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "VALUE", null);
            $ins_genres3->setTable("genres");
            $ins_genres3->addColumn("GENRE_ID", "NUMERIC_TYPE", "POST", "GENRE_ID3");
            $ins_genres3->addColumn("ALBUM_ID", "NUMERIC_TYPE", "VALUE", "");
            $ins_genres3->setPrimaryKey("ID", "NUMERIC_TYPE");

            =========================================================================================

            Hi Aaron,

            Nice job!!

            $ins_albums->registerTrigger("AFTER", "Trigger_LinkTransactions2", 98);
            $ins_albums->registerTrigger("AFTER", "Trigger_LinkTransactions3", 98);
            These lines, right? :-( Sorry I forgot to mention that

            Thanks a lot for the grading!