10 Replies Latest reply on Apr 18, 2008 2:29 AM by (Johannes_Gürteler)

    Capture key for use in next insert

      Ok, I have a form that would probably be beyond what most people use ADDT for, but it works!

      Here's a really long form, and it does these steps.
      1. Register's user.
      2. Runs custom transaction (or trigger, forgot) that contacts authorize.net, charges a credit card and returns pass or fail
      3. Inserts a line in a table that shows they have credit in our store. Line has a uid_usr field that is connected to user registration via link-transactions.
      4. Inserts a line into same table that gives them a starting balance, again link-transaction for uid_usr... BUT there's a second field credit_used_cre that should be a foreign key to step 3's id_cre...

      What I would like to do is capture the ID returned from Last_Insert_ID() in step 3 and pass it as something like {GLOBALS.id_cre} But I'm not sure how to do that. THIS is where I need help.

      5. I store the credit card info into payment_pay. Again, linked to users_usr.

      6. Insert a starting balance into sbal_sbl, linked again.

      7. Do a custom transaction that goes back and updates a few dates in the above directly with SQL.

      SOOOOO, how do I capture an ID to a variable so I can reuse it in a future transaction??
        • 1. Re: Capture key for use in next insert
          Level 1
          btw, currently this creates an error, but it shows what I think would be most obvious:

          $Insert_Initial_Credit->addColumn("credit_used_acc", "NUMERIC_TYPE", "VALUE", "{ins_payment_acc.id_acc}");

          Fatal error: Call to undefined method tNG_insert::Fields() in /www/html/ox/includes/common/KT_functions.inc.php on line 786
          • 2. Re: Capture key for use in next insert
            Forget using developer toolbox to do that. You have to use the ordinary insert record, then hand code it.
            • 3. Re: Capture key for use in next insert
              Level 1
              Benny,

              Not sure if this will help, but here's how you capture the ID of the inserted record.

              Create a custom trigger set to execute after the insert transaction.

              Use the following code:

              $insertKey = $tNG->getPrimaryKeyValue();

              This will set $insertKey to the Primary Key of the inserted record.

              You would then need to pass this variable via URL or $_SESSION variable.

              Shane
              • 4. Re: Capture key for use in next insert
                Hi,

                I have tried that for a custom transaction. I added
                $insertKey = $tNG->getPrimaryKeyValue();
                and I also tried
                $insertKey = $tNG->getPrimaryKeyValue($customTransaction);

                In both cases Iget a run-time error:
                Fatal error: Call to a member function getPrimaryKeyValue() on a non-object

                Is the sytntax to get the last inserted key different for custom transactions?

                Any ideas?
                • 5. Re: Capture key for use in next insert
                  Günter Schenk Level 4
                  Hi Hanno,

                  the correct syntax is: $tNG->getPrimaryKeyValue() -- and it should work both on insert and update.

                  However, maybe there´s something else which triggers this error, so it would be helpful to see the complete Custom Transaction

                  Cheers,
                  Günter Schenk
                  Adobe Community Expert, Dreamweaver
                  • 6. Re: Capture key for use in next insert
                    Level 1
                    Hi Guenter,
                    as I was under time pressure and thus dropped the ADDT custom transaction and replaced it with a 'recordset' style code. I kept the INSERT code in a comment (see below for your reference). That code (with the {} placeholders replaced with $variables and the INSERT works just fine. I therefore doubt the INSERT is the issue. The rest of the code is generated by ADDT anyway and should therefore not be the source of the error, as you rightly said: It should work.

                    But then again it does not. So maybe $tNG->getPrimaryKeyValue() just does not work for custom transactions?

                    Your thoughts?

                    Hanno

                    "INSERT INTO customers (customers_vat_id, customers_vat_id_status, customers_status, customers_gender, customers_firstname, customers_lastname, customers_email_address, customers_telephone, customers_fax, customers_password, account_type,customers_newsletter, customers_newsletter_mode, member_flag, delete_user, password_request_key, payment_unallowed, shipping_unallowed, refferers_id, customers_date_added, customers_last_modified) values ('',0,2,'','{account.name}', '{account.name2}', '{account.email}', '{account.phone}', '{account.fax}', '{account.pass}',0,0,0,1,0,'','','',0, now(), now())"
                    • 7. Re: Capture key for use in next insert
                      Günter Schenk Level 4
                      Hi Hanno,

                      ---
                      Your thoughts?
                      ---

                      so far I´ve been successfully using the $tNG->getPrimaryKeyValue() within Custom Triggers, but never did any queries with Custom Transactions -- so maybe you´ll have to try that instead.

                      In this respect the helpfile says: "Note that the primary key value can only be used in an after trigger, as it gets initialized only after the transaction has completed"

                      Cheers,
                      Günter Schenk
                      Adobe Community Expert, Dreamweaver
                      • 8. Re: Capture key for use in next insert
                        Level 1
                        It sounds to me like Hanno's attempt is not in his code using $insertKey = $tNG->getPrimaryKeyValue(); but in his calling of it. When I get that error it's because the function doesn't understand what $tNG is.

                        In my trigger, I have:

                        function Get_Credit_line_ID(&$tNG) {
                        global $credit_line_id, $connShop, $uid_usr;

                        $uid_usr = $tNG->getPrimaryKeyValue();

                        }
                        //end Get_Credit_line_ID trigger
                        (I would get scope errors if I didn't call global $connShop.)

                        And let me know if you've ever done this many triggers at once before:
                        // Register triggers
                        $userRegistration->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "KT_Insert1");
                        $userRegistration->registerTrigger("BEFORE", "Trigger_Default_FormValidation", 10, $formValidation);
                        $userRegistration->registerConditionalTrigger("{POST.password_usr} != {POST.re_password_usr}", "BEFORE", "Trigger_CheckPasswords", 30);
                        $userRegistration->registerTrigger("AFTER", "Link_Insert_Initial_Credit_To_User_Registration", 40);
                        $userRegistration->registerTrigger("ERROR", "Link_Insert_Initial_Credit_To_User_Registration", 40);
                        $userRegistration->registerTrigger("AFTER", "Get_Credit_line_ID", 44);
                        $userRegistration->registerTrigger("AFTER", "Link_Insert_Payment", 60);
                        $userRegistration->registerTrigger("ERROR", "Link_Insert_Payment", 60);
                        $userRegistration->registerConditionalTrigger("{POST.pay_up_front} != 'y'", "AFTER", "Link_Starting_Balance_to_Credit", 70);
                        $userRegistration->registerConditionalTrigger("{POST.pay_up_front} != 'y'", "ERROR", "Link_Starting_Balance_to_Credit", 70);
                        $userRegistration->registerTrigger("AFTER", "Link_Insert_Sbal_into_Sbal", 80);
                        $userRegistration->registerTrigger("ERROR", "Link_Insert_Sbal_into_Sbal", 80);
                        $userRegistration->registerTrigger("AFTER", "Trigger_WelcomeEmail", 90);
                        $userRegistration->registerTrigger("AFTER", "Process_card", 50);
                        $userRegistration->registerTrigger("END", "Trigger_Default_Redirect", 99, "../../login/customlogin.php?kt_login1=1&kt_login_user={POST.username_usr}&kt_login_passw ord={POST.password_usr}&kt_login_rememberme=1&redir=".urlencode('/credit/apply/step2.php') );

                        Oh and btw, I NEVER COULD get the log in user after registration tutorials to work, so I hacked together a login pay that logs them in and redirects them where I need them.
                        • 9. Re: Capture key for use in next insert
                          Level 1
                          Thanks Guenter and Benny,

                          yes, that code had to be in an AFTER trigger, not just placed 'after' the the custom transaction definition. That was the bit of information that was missing for me. Within a trigger it works like a charm.

                          Thanks

                          Hanno
                          • 10. Re: Capture key for use in next insert
                            Hi Benny,

                            I had the same problem as you in a custom trigger with

                            $tNG->getPrimaryKeyValue()

                            The syntax ist OK and I did't get no syntax error, but an SQL error
                            Reason: The primary key value was emty.

                            Now I found the Solution:

                            If you use Developer Toolbox - Forms - Advanced - Insert Transaction, the dialog also inserts the primary value as addColumn. This overwrites the Primary value.

                            $ins_tablename->addColumn("id", "NUMERIC_TYPE", "POST", "id"); <-- Delete this line!!!

                            ...

                            $ins_tablename->setPrimaryKey("id", "NUMERIC_TYPE");

                            Check this, its possibly the reason for your problem.

                            Johannes