14 Replies Latest reply on Oct 30, 2008 6:55 AM by (Juergen_Helmers)

    designing a custom trigger

      Hi!

      I have searched the web including the forum and have spent the last 2 hours trying to create a custom trigger.

      This is what I am trying to accomplish:

      1. I have an insert record page that will put an order into an ordering system.

      2. The record contains a quantity and untiprice field.

      3. Before the record is inserted, the custom trigger is supposed to calculate the total of that order and check a special table in the same database that has a field storing the available budget.

      This is my code so far for the trigger:

      //start Trigger_Custom1 trigger
      function Trigger_Custom1(&$tNG) {
      $TOTAL_ORDER = $_POST['unitprice'] * $_POST['quantity'];
      //get the budget amount from database

      $LAB = $_SESSION['kt_lab'];
      mysql_select_db($database_era, $era);
      $query_amount = "SELECT amount FROM budget WHERE id_Lab =$LAB";
      $amount = mysql_query($query_amount, $era) or die(mysql_error());
      $row_amount = mysql_fetch_assoc($amount);
      $totalRows_amount = mysql_num_rows($amount);

      $BUDGET = $row_amount['amount'];
      $TOTAL = $BUDGET - $TOTAL_ORDER;

      $update_logged_in_status = "UPDATE budget SET amount='$TOTAL' WHERE id_Lab=".$_SESSION['kt_lab'];
      $update_result = $tNG->connection->execute($update_logged_in_status);
      if(!$update_result) {
      $updateError = new tNG_error("Error Updating the budget 1", array(), array());
      return $updateError;
      } else {
      return NULL;
      }
      }

      The UPDATE staement at the bottom does work but I have difficulties getting the availabel budget from the database. So the line

      $query_amount = "SELECT amount FROM budget WHERE id_Lab =$LAB";

      gets me the following error:

      PHP Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource

      So I tried to modify it in all ways possible but to no avail.

      Anybody being able to help?

      Thanks a lot in advance!

      Cheers Juergen
        • 1. Re: designing a custom trigger
          Günter Schenk Level 4
          Hi Jürgen,

          there´s a common misconception with Custom Triggers :: they don´t and can´t evaluate...

          $_POST['columnname']

          ...variables, because the syntax for grabbing ADDT´s transaction values is different:

          $tNG->getColumnValue("column_name")

          Your calculation formula would have to be:

          $TOTAL_ORDER = $tNG->getColumnValue("unit_price") * $tNG->getColumnValue("quantity");

          ---------
          So the line

          $query_amount = "SELECT amount FROM budget WHERE id_Lab =$LAB";

          gets me the following error:

          PHP Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource

          So I tried to modify it in all ways possible but to no avail
          ----------

          guess it has to be this way:

          $query_amount = "SELECT amount FROM budget WHERE id_Lab = ".$LAB."";

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: designing a custom trigger
            Level 1
            Dear Günter,

            thanks for your help, but it still doesn't work:

            When I include the two changes as indicated, I get the following error:

            tNG_fields.getColumnValue:
            Column unit_price is not part of the current transaction.

            If I use my construct. the $_POST[] field DO get evaluated, since the $TOTAL of the whole custom trigger is always -$TOTAL_ORDER, so whatever I do, the record field amount from budget never gets evaluated...

            If I keep my $TOTAL_ORDER construction and include the chnages for the sql query, I get the same erro as before...

            Cheers Juergen
            • 3. Re: designing a custom trigger
              Günter Schenk Level 4
              Hi Jürgen,

              --------
              When I include the two changes as indicated, I get the following error:

              tNG_fields.getColumnValue:
              Column unit_price is not part of the current transaction.
              --------

              don´t know why exactly you´re getting this error, but all I can say is that ADDT´s standard method to introduce transaction values to Custom Triggers is $tNG->getColumnValue("columnname")

              Can you upload the code of your page on your server as .txt file and give a link to that file ?

              Cheers,
              Günter Schenk
              Adobe Community Expert, Dreamweaver
              • 4. Re: designing a custom trigger
                Level 1
                Dear Guenter,

                thanks a lot for your support. I have uploaded the file which you can find here:

                http://home.arcor.de/helmerj/insert_new.txt

                I have done some tinkering, so it might be in a worse state than before. To give you some background:

                I would like to check if the budget is high enough for the order to be placed. Therefore the custom trigger. The order should be inserted no matter what but:

                1. Compute the total of the current oder in the form (unitprice * quantity
                2. Condition for the custom trigger 1 the budget amount is smaller than the total of the order :if the budget (pulled from the database table budget[amount], is not hight enough, substract the total for the order from the budget and update the amount field in the budget table.
                3. At the same time, set $APR=3 and $REDIRECT=overspend.php

                Number 3 will result the user to be taken to a different redirect page letting him or her know that he or she just overspent the budget. It will mark the oder as rejected by setting APR=3 which will be used to set the approved field in the table that receives the inserted record and therefore marking the order in the system as rejected...

                In case the budget is not overspend I would like to update the budget field in a second custom trigger that I have executed with a priority one higher than the other.

                I don't even know if this can be done like this. If not I will have to hand code it and that would take me ages...

                Cheers Juergen
                • 5. Re: designing a custom trigger
                  Günter Schenk Level 4
                  Hi Jürgen,

                  I wonder what the following condition in your Trigger_Custom1 is supposed to check:

                  --------
                  if ($query == '') {

                  }
                  --------

                  AFAIK, this condition would check if the previously declared variable...

                  $query = "SELECT amount FROM budget WHERE id_lab=1";

                  ...will equal an empty string, but not if the number of rows returned is 0.

                  As the $query - variable has been declared before and isn´t an ampty string at all, I wonder if you´re currently getting the "the budget record is empty" error message every time ??

                  Cheers,
                  Günter Schenk
                  Adobe Community Expert, Dreamweaver
                  • 6. Re: designing a custom trigger
                    Level 1
                    Hi Guenter,

                    the idea of the if staement was to introduce a new check because the query always was empty. Part of the tinkering that I did mention. But the code does not runso far so I dont get the message as of now. But once the check is in place I suppose I will as the query seems to be empty all the time since I do get the negative of the calculated $TOTAL_ORDER all the time and not the value of the budget minus the total_order...

                    Cheers Juergen
                    • 7. Re: designing a custom trigger
                      Level 1
                      Hi!

                      With the help of this page:

                      http://www.interaktonline.com/support/forums/php-general-forum/Details/14700/BEFORE%252FST ARTER+trigger+redirect.html

                      I have rewritten my custom trigger and now it works as expected. I still have to check on the redirect and the approved bit but at least the evaluation of the database field and the session vars works as expected.

                      Thanks for your support Guenter!

                      Cheers Juergen

                      //start Trigger_Custom1 trigger
                      function Trigger_Custom1(&$tNG) {
                      global $HTTP_SESSION_VARS;//make session variables available in trigger
                      mysql_select_db($database_era, $era);
                      $sql = "SELECT amount FROM budget WHERE id_lab=".$HTTP_SESSION_VARS['kt_lab'];
                      $rs = mysql_query($sql) or die(mysql_error());
                      $TOTAL_ORDER = $_POST['unitprice'] * $_POST['quantity']; //calculate the total of the current order
                      if (mysql_num_rows($rs)>0){
                      $row = mysql_fetch_assoc($rs);
                      if ($row['amount'] <= $TOTAL_ORDER) {
                      $BUDGET = $row['amount'];
                      $TOTAL = $BUDGET - $TOTAL_ORDER;
                      $update_amount_in_budget = "UPDATE budget SET amount='$TOTAL' WHERE id_lab=".$HTTP_SESSION_VARS['kt_lab'];
                      $update_result = $tNG->connection->execute($update_amount_in_budget);
                      $APR = '3';
                      $REDIRECT = "overspent.php";
                      } else {
                      $BUDGET = $row['amount'];
                      $TOTAL = $BUDGET - $TOTAL_ORDER;
                      $update_amount_in_budget = "UPDATE budget SET amount='$TOTAL' WHERE id_lab=".$HTTP_SESSION_VARS['kt_lab'];
                      $update_result = $tNG->connection->execute($update_amount_in_budget);
                      }
                      }
                      }
                      //end Trigger_Custom1 trigger
                      • 8. Re: designing a custom trigger
                        Level 1
                        Hi!

                        I still have one last problem. I have two variables in the main page ($APR and $REDIRECT) that have to be set depending on the outcome of the insert transaction. I have rewritten the trigger and declared thos variables as global variables so I can change their value in the trigger depending of trigger code result and then use those variables in the main page. BUT although the global declaration, the values never get assigned to those variables inside the trigger. Is there a special way to make that happen when using custom triggers?

                        Cheers Juergen

                        this is the trigger by now....

                        //start Trigger_Custom1 trigger
                        function Trigger_Custom1(&$tNG) {
                        global $HTTP_SESSION_VARS;
                        global $APR;
                        global $REDIRECT;
                        mysql_select_db($database_era, $era);
                        $sql = "SELECT amount FROM budget WHERE id_lab=".$HTTP_SESSION_VARS['kt_lab'];
                        $rs = mysql_query($sql) or die(mysql_error());
                        $TOTAL_ORDER = $_POST['unitprice'] * $_POST['quantity'];
                        if (mysql_num_rows($rs)>0){
                        $row = mysql_fetch_assoc($rs);
                        if ($row['amount'] <= $TOTAL_ORDER) {
                        $BUDGET = $row['amount'];
                        $TOTAL = $BUDGET - $TOTAL_ORDER;
                        $update_amount_in_budget = "UPDATE budget SET amount='$TOTAL' WHERE id_lab=".$HTTP_SESSION_VARS['kt_lab'];
                        $update_result = $tNG->connection->execute($update_amount_in_budget);
                        $APR = '3';
                        $REDIRECT = "overspent.php";
                        } else if ($row['amount'] > $TOTAL_ORDER){
                        $BUDGET = $row['amount'];
                        $TOTAL = $BUDGET - $TOTAL_ORDER;
                        $update_amount_in_budget = "UPDATE budget SET amount='$TOTAL' WHERE id_lab=".$HTTP_SESSION_VARS['kt_lab'];
                        $update_result = $tNG->connection->execute($update_amount_in_budget);
                        if ( $HTTP_SESSION_VARS['kt_login_level'] == 'student' ){ // if logged in user IS student
                        $APR= '0';
                        $REDIRECT = "approve.php";
                        } else {
                        $REDIRECT = "pending.php";
                        $APR= '1';
                        }

                        } else {
                        $myErrorObjectName = new tNG_error("Something went wrong", array(), array());
                        return $myErrorObjectName;
                        }
                        }
                        }
                        //end Trigger_Custom1 trigger
                        • 9. Re: designing a custom trigger
                          Günter Schenk Level 4
                          Hi Jürgen,

                          glad you got it sorted -- so I don´t have to, LOL :-)

                          Some comments though:

                          1. $HTTP_SESSION_VARS is deprecated by now, means that PHP version 4.1.0 and higher uses the $_SESSION superglobal instead.

                          "Superglobal" in fact means that a $_SESSION['whatever'] variable - unlike a $HTTP_SESSION_VARS['whatever'] variable - doesn´t need to be "imported" into a function using the GLOBAL keyword

                          "$HTTP_SESSION_VARS" might still work for you now, but who knows which future PHP version will finally deactivate this outdated stuff once and for all ;-)

                          Recommended PHP manual chapter: "Predefined Variables": http://www.php.net/manual/nl/reserved.variables.php

                          2.

                          -------
                          I have rewritten the trigger and declared thos variables as global variables so I can change their value in the trigger depending of trigger code result and then use those variables in the main page
                          -------

                          you only need to declare a variable GLOBAL inside a function, if it has been introduced and set outside the function -- in your case the variable $APR is declared inside the function, means that declaring it GLOBAL is not required.

                          Cheers,
                          Günter Schenk
                          Adobe Community Expert, Dreamweaver
                          • 10. Re: designing a custom trigger
                            Level 1
                            Hi Guenter,

                            well it is not all over yet... ;-)

                            If I am not mistaken I don't need to declare variables in a function global if I only want to use them inside that function. But I want to declare the variable inside the function and then use it outside that function. I set $APR and $REDIRECT. APR will be a value to be inserted depending on how does it and if the budget is overspent (0=student, 1=all other users, 3=rejected). $REDIRECTED will specify a redirection page. The default redirect page in the redirection trigger I have replaced with $REDIRECT.

                            So I do need the variables outside the function but whether I declare them global or not, neither the insert record trigger has access to $APR nor does the redirect trigger have access to $REDIRECT.

                            This is the start trigger part of my page:

                            // Register triggers
                            $ins_pending->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "KT_Insert1");
                            $ins_pending->registerTrigger("BEFORE", "Trigger_Default_FormValidation", 10, $formValidation);
                            $ins_pending->registerTrigger("END", "Trigger_Default_Redirect", 99, "$REDIRECT");
                            $ins_pending->registerTrigger("BEFORE", "Trigger_Custom1", 50);

                            Do I have to change the before/after parts?

                            Thanks for the pointers!

                            Cheers Juergen
                            • 11. Re: designing a custom trigger
                              Günter Schenk Level 4
                              Hi Jürgen,

                              -------
                              If I am not mistaken I don't need to declare variables in a function global if I only want to use them inside that function. But I want to declare the variable inside the function and then use it outside that function
                              -------

                              first declaring a variable GLOBAL and later assigning some value to it should make it available outside a function as well, example:

                              function bar() {
                              global $b;
                              $b = "b";
                              }

                              echo $b; //echoes "b";

                              -------
                              $ins_pending->registerTrigger("END", "Trigger_Default_Redirect", 99, "$REDIRECT")
                              -------

                              as it´s now, $REDIRECT will be interpreted literally similar to a hard-coded file path -- if you want to embed PHP variables in strings, you´ll have to do it this way:

                              "".$REDIRECT.""

                              Cheers,
                              Günter Schenk
                              Adobe Community Expert, Dreamweaver
                              • 12. Re: designing a custom trigger
                                Level 1
                                Woohoo!

                                Session variables to the rescue. I don't know why, but the trigger constrcut using variables didn't work. Makeing them global or not didn't matter... So I figuered, since i can use them session variables alright inside the trigger I might as well chnage to them for the APR and REDIRECT as well and now it work like I want it. In the main page I save the session vars after the trigger again in regular variables.

                                Happy camper here!!

                                Thanks Guenter for support on this one! I really appreciate it.

                                Cheers Juergen

                                PS: for the record, this is the final trigger

                                //start Trigger_Custom1 trigger
                                function Trigger_Custom1(&$tNG) {
                                mysql_select_db($database_era, $era);
                                $sql = "SELECT amount FROM budget WHERE id_lab=".$_SESSION['kt_lab'];
                                $rs = mysql_query($sql) or die(mysql_error());
                                $TOTAL_ORDER = $_POST['unitprice'] * $_POST['quantity']; //calculate the total of the current order
                                if (mysql_num_rows($rs)>0){ //depending on available budget redirect user and file order...
                                $row = mysql_fetch_assoc($rs);
                                if ($row['amount'] < $TOTAL_ORDER) {
                                $BUDGET = $row['amount'];
                                $TOTAL = $BUDGET - $TOTAL_ORDER;
                                $update_amount_in_budget = "UPDATE budget SET amount='$TOTAL' WHERE id_lab=".$_SESSION['kt_lab'];
                                $update_result = $tNG->connection->execute($update_amount_in_budget);
                                //set session var for redirect and reject order
                                $_SESSION['APR'] = 3;
                                $_SESSION['REDIRECT'] = "overspent.php";
                                }
                                else if ($row['amount'] > $TOTAL_ORDER){
                                $BUDGET = $row['amount'];
                                $TOTAL = $BUDGET - $TOTAL_ORDER;
                                $update_amount_in_budget = "UPDATE budget SET amount='$TOTAL' WHERE id_lab=".$_SESSION['kt_lab'];
                                $update_result = $tNG->connection->execute($update_amount_in_budget);
                                if ( $_SESSION['kt_login_level'] == 'student' ){ // if logged in user IS student
                                $_SESSION['APR'] = 0;
                                $_SESSION['REDIRECT'] = "approve.php";
                                } else {
                                $_SESSION['APR'] = 1;
                                $_SESSION['REDIRECT'] = "pending.php";
                                }
                                }
                                else {
                                $myErrorObjectName = new tNG_error("Something went wrong", array(), array());
                                return $myErrorObjectName;
                                }
                                }
                                }
                                //end Trigger_Custom1 trigger
                                • 13. Re: designing a custom trigger
                                  Günter Schenk Level 4
                                  Hey Jürgen,

                                  big congrats for developing such a cool "mini application" -- and thanks a lot for sharing one of the most advanced Custom Triggers I´ve ever seen :-)

                                  In fact this is an excellent showcase which demonstrates what experienced (and adventurous :-)) PHP developers like you can achieve using ADDT´s Custom Triggers feature.

                                  Well done !!

                                  Cheers,
                                  Günter Schenk
                                  Adobe Community Expert, Dreamweaver
                                  • 14. Re: designing a custom trigger
                                    Level 1
                                    Thanks Guenter,

                                    I really appreciate your support on this!

                                    Cheers Juergen