32 Replies Latest reply on Nov 6, 2008 4:48 PM by (Steve_BT)

    Update 2 tables with one form...

      Hi guys,

      I've been racking my brain trying to figure this out. Anyone know how to update 2 tables in mySQL db using one form?
      Any help would be greatly appreciated.

      Thanks,

      ST
        • 1. Re: Update 2 tables with one form...
          Günter Schenk Level 4
          Hi Steve,

          you can update a secondary table by adding a Custom Trigger which executes an UPDATE query and uses the main transaction values (aka the form values).

          Please read the helfile chapter "Custom transactions and triggers" -> "Execute SQL queries" / "Use transaction fields" for some helpful pointers.

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: Update 2 tables with one form...
            Level 1
            Thanks Gunter, I will post my results.

            ST
            • 3. Re: Update 2 tables with one form...
              Level 1
              Hi Gunter,

              I attempted to follow the information in the help chapter Custom trans and triggers(user transaction fields) with no success. Can you please supply me more information on how to make this work. I would greatly appreciate any further guidance you can provide.

              ST
              • 4. Re: Update 2 tables with one form...
                Level 1
                Just wondering if I could use and manipulate "update record transaction" to achieve the same results?

                ST
                • 5. Re: Update 2 tables with one form...
                  Günter Schenk Level 4
                  Hi Steve,

                  can you please post the Custom Trigger code you probably made by now ?

                  Cheers,
                  Günter Schenk
                  Adobe Community Expert, Dreamweaver
                  • 6. Re: Update 2 tables with one form...
                    Level 1
                    Gunter,

                    Here is my code:

                    $update_request = "UPDATE id = 5 WHERE open_request_id = ".$_URL ["open_request_id"];
                    $update_result = $tNG->connection->execute($update_id);
                    if(!$update_result) {
                    $updateError = new tNG_error("Error setting the field to tendered", array(), array());
                    return $updateError;
                    } else {
                    return NULL;
                    }

                    Here is my tng execution trace:

                    Error:
                    Error setting the field to tendered
                    Developer Details:
                    Error setting the field to tendered (Error setting the field to tendered)
                    tNG Execution Trace - VIEW

                    * tNG_custom.executeTransaction
                    o STARTER.Trigger_Default_Starter
                    * tNG_update.executeTransaction
                    o STARTER.Trigger_Default_Starter
                    o tNG_update.doTransaction
                    + BEFORE.Trigger_Default_saveData
                    # tNG_update.saveData
                    + BEFORE.Trigger_Default_FormValidation
                    + tNG_update.prepareSQL
                    + tNG_update.executeTransaction - execute sql

                    + AFTER.upd_request* <<<<<< ERROR OCCURS HERE!

                    * tNG_custom.getRecordset
                    * tNG_custom.getLocalRecordset
                    * tNG_custom.getFakeRecordset
                    * tNG_update.getRecordset
                    * tNG_update.getFakeRsArr
                    * tNG_update.getLocalRecordset
                    * tNG_update.getFakeRecordset

                    Thanks for your help Gunter. This is one of the final pieces to my 180 page website I've been working on for the past year. If you can supply me with information, I will indeed be indebted to you forever.

                    (I'll send you a case of your favorite suds!)

                    Thanks again,

                    Steve BT
                    • 7. Re: Update 2 tables with one form...
                      Günter Schenk Level 4
                      Hi Steve,

                      the native PHP equivalent of an URL variable is $_GET['variablename'], so your query should read: "UPDATE id = 5 WHERE open_request_id = ".$_GET["open_request_id"];

                      Works better now ?

                      Cheers,
                      Günter Schenk
                      Adobe Community Expert, Dreamweaver
                      • 8. Re: Update 2 tables with one form...
                        Günter Schenk Level 4
                        Hi Steve,

                        there´s something else I nearly overlooked: the whole UPDATE syntax seems wrong, and I can´t see a particular column getting set to "tendered" - a valid example would be:

                        "UPDATE tablename SET columnname = 'tendered' WHERE open_request_id = '".$_GET['open_request_id']."';

                        Cheers,
                        Günter Schenk
                        Adobe Community Expert, Dreamweaver
                        • 9. Re: Update 2 tables with one form...
                          Level 1
                          Hi Gunter,
                          Crazy how it seems simple but it still didnt work. I feel like we are almost there!

                          I outlined my tables below.

                          Update Form

                          1st table -request_tender

                          Id <<<<<<<<<<<<<<<< primary key

                          open_request_id <<<<<<<<<< foreign key

                          request_status (hidden field) <<<< column to be updated from (value) 4 to 5

                          Trigger

                          2nd table request

                          Id <<<<<<<<<<<<<<<<<< primary key (also table 1 foreign key)

                          request_status <<<<<<<< column to be updated from (value) 4 to 5

                          As you know, Im trying to accomplish changing request_status in both tables from 4 5 using one update form that would be built around table 1. Table 2 primary key (id) is table 1 foreign key (open_request_id). Both "ID" and "open_request_id" vales are being passed via url from the prior page.

                          Thanks Gunter, youre a lifesaver,

                          Steve BT

                          PS. If you need more information, let me know.
                          • 10. Re: Update 2 tables with one form...
                            Günter Schenk Level 4
                            Hi Steve,

                            this query:

                            "UPDATE request SET request_status = '".$tNG->getColumnValue("request_status")."' WHERE ID = '".$_GET['open_request_id']."';

                            ..would update the "request" table and set the value of the column "request_status" according to the contents of the main form´s hidden field "request_status" -- which in turn apparently is part of the main UPDATE transaction, that´s why I´m using the $tNG->getColumnValue("request_status") variable here.

                            Cheers,
                            Günter Schenk
                            Adobe Community Expert, Dreamweaver
                            • 11. Re: Update 2 tables with one form...
                              Level 1
                              I attempted to implement your solution, It compromised the integrity of the page. The css completely disappeared.

                              Is the entire code written properly?

                              This is what I inserted as the custom trigger.

                              $update_request = "UPDATE request SET request_status = '".$tNG>getColumnValue("request_status")."' WHERE ID = '".$_GET['open_request_id']."';
                              $update_result = $tNG->connection->execute($update_id);
                              if(!$update_result) {
                              $updateError = new tNG_error("Error setting the field to 5", array(), array());
                              return $updateError;
                              } else {
                              return NULL;
                              }


                              Looking forward to your response.

                              Steve BT
                              • 12. Re: Update 2 tables with one form...
                                Günter Schenk Level 4
                                ---------
                                $tNG>getColumnValue
                                ---------

                                if that´s what you literally wrote, there´s a hyphen missing -- it´s $tNG->getColumnValue.

                                Cheers,
                                Günter Schenk
                                Adobe Community Expert, Dreamweaver
                                • 13. Re: Update 2 tables with one form...
                                  Level 1
                                  Made the change, still the same result. I rebuilt the "update form" just to be sure it wasn't the problem. The integrity of the page is still compromised.

                                  Steve BT
                                  • 14. Re: Update 2 tables with one form...
                                    Günter Schenk Level 4
                                    Hi Steve,

                                    as one can never exactly know why such stuff happens, please try with adding a query with static dummy values first, e.g. "UPDATE request SET request_status = '5' WHERE ID = '1'"; -- if this works and the correct DB entry is getting updated, it´s time to replace those static values with their dynamic equivalents.

                                    ---------
                                    The css completely disappeared
                                    ---------

                                    so the CSS styles vanished, but you didn´t get a totally blank page ?

                                    Cheers,
                                    Günter Schenk
                                    Adobe Community Expert, Dreamweaver
                                    • 15. Re: Update 2 tables with one form...
                                      Level 1
                                      Not a totaly blank page, just no styles.

                                      Static dummy didn't affect the page but upon clicking submit on the form I still receive the same error.

                                      Error setting the field to 5 (Error setting the field to 5)

                                      * tNG_custom.executeTransaction
                                      o STARTER.Trigger_Default_Starter
                                      * tNG_update.executeTransaction
                                      o STARTER.Trigger_Default_Starter
                                      o tNG_update.doTransaction
                                      + BEFORE.Trigger_Default_saveData
                                      # tNG_update.saveData
                                      + BEFORE.Trigger_Default_FormValidation
                                      + tNG_update.prepareSQL
                                      + tNG_update.executeTransaction - execute sql
                                      + AFTER.Trigger_Custom*
                                      * tNG_custom.getRecordset
                                      * tNG_custom.getLocalRecordset
                                      * tNG_custom.getFakeRecordset
                                      * tNG_update.getRecordset
                                      * tNG_update.getFakeRsArr
                                      * tNG_update.getLocalRecordset
                                      * tNG_update.getFakeRecordset

                                      This is giving me more trouble than I originally anticipated. I apologize for being a pain in the neck but It's imperative I figure this out. I don't know where this is going wrong and I'm beginning to get extremely frustrated seeing that I've been trying to figure this out for the past 3 days. :(

                                      I appreciate you help Gunter

                                      Steve BT
                                      • 16. Re: Update 2 tables with one form...
                                        Günter Schenk Level 4
                                        --------
                                        Not a totaly blank page, just no styles.
                                        --------

                                        *very* weird, never heard of this happening -- however it´s most assumingly no PHP syntax error, because you´d then get a totally blank page or an error message.

                                        --------
                                        "UPDATE request SET request_status = '5' WHERE ID = '1'";
                                        --------

                                        maybe this is where the error comes from :: is "ID" the table´s Primary Key column ?

                                        Cheers,
                                        Günter Schenk
                                        Adobe Community Expert, Dreamweaver
                                        • 17. Re: Update 2 tables with one form...
                                          Level 1
                                          ID is the "request" table's primary key. This would be the foreign key in the "request_tender" table.

                                          The foreign key is named "open_request_id" in the "request_tender" table.

                                          Steve BT
                                          • 18. Re: Update 2 tables with one form...
                                            Günter Schenk Level 4
                                            what really puzzles me is the fact that even the "static" UPDATE query seems to fail, although the syntax is just fine -- don´t think I´m going nuts now, but can you see any syntax difference to e.g. a sample UPDATE query posted @w3schools.com ? http://www.w3schools.com/php/php_mysql_update.asp

                                            BTW, does your "request" table have an existing record with an ID 1 ? This may sound stupid, but your static query would of course fail if it can´t find a record having this Primary Key value

                                            Cheers,
                                            Günter Schenk
                                            Adobe Community Expert, Dreamweaver
                                            • 19. Re: Update 2 tables with one form...
                                              Level 1
                                              As a matter of fact there was no record with a ID value of 1 in my database. I changed ID 1 to a current value resulting in the same thing. To answer your question, no I don not see any syntax difference to the diagrammed example.

                                              Tomorrow will be day 4 trying to get this to work. I hope you will still have the fortitude to help me work through this.

                                              Is there a workaround that can give us the same result?

                                              Thanks,

                                              Steve BT
                                              • 20. Re: Update 2 tables with one form...
                                                Level 1
                                                I believe I narrowed the problem down to this bit of code in the beginning of the trigger:

                                                "UPDATE request SET request_status =

                                                This piece of code is what makes my css quirky, I figured it out through process of elimination.

                                                Is this what can be causing the trigger to fail upon execution? If you analyze the dynamic and static trigger code, you will notice that the line I posted above is shared in both.

                                                Steve BT
                                                • 21. Re: Update 2 tables with one form...
                                                  Günter Schenk Level 4
                                                  Hi Steve,

                                                  just out of interest and without having any real clue why this happens: can you temporarily rename the table "request" to, say, "request_tbl", change the Custom Trigger accordingly and see if this would make a difference ?

                                                  This whole thing somehow reminds me of DW not being able to handle a table named "user" -- and who knows if something odd like this might come in your way now too.

                                                  Cheers,
                                                  Günter Schenk
                                                  Adobe Community Expert, Dreamweaver
                                                  • 22. Re: Update 2 tables with one form...
                                                    Level 1
                                                    It is not possible to rename the table so I created another "request_test" in mysql db. The end result is the same, CSS was compromised. Funny though, I notice upon saving any created trigger that not only does the CSS disappear but there are also 2 sever behavior fields that have red error exclamation points next to them ("update transaction" the other "log out").

                                                    Thanks,
                                                    Steve BT
                                                    • 23. Re: Update 2 tables with one form...
                                                      Level 1
                                                      Gunter,

                                                      I was thinking that my form's code may help. I attempted to post it with no success. For some reason the Adobe message box wouldn't allow it.

                                                      Is there another approach to this. Is there an extension or something else that can be implemented that would provide the same result as the "Custom Trigger"?

                                                      Briefing over the code, is there something that you may have missed, here is the code again:

                                                      $update_request = "UPDATE request SET request_status = '".$tNG->getColumnValue("request_status")."' WHERE ID = '".$_GET['open_request_id']."';
                                                      $update_result = $tNG->connection->execute($update_id);
                                                      if(!$update_result) {
                                                      $updateError = new tNG_error("Error setting the field to 5", array(), array());
                                                      return $updateError;
                                                      } else {
                                                      return NULL;
                                                      }

                                                      Thanks Gunter,

                                                      Steve BT
                                                      • 24. Re: Update 2 tables with one form...
                                                        Günter Schenk Level 4
                                                        Hi Steve,

                                                        ------
                                                        but there are also 2 sever behavior fields that have red error exclamation points next to them
                                                        ------

                                                        this sometimes happens with DW -- do they still display as "broken" when closing and reopening the page ?

                                                        However, I´m - like you - at my wit´s end now, and I think it´s probably best to contact the Adobe tech support and see if they know what´s up. If the UPDATE query would be technically wrong, I´d understand that, but it certainly isn´t wrong, and the possible reasons for the compromised CSS are beyond my knowledge -- I *never* heard of a Custom Trigger affecting the page´s CSS !

                                                        Cheers,
                                                        Günter Schenk
                                                        Adobe Community Expert, Dreamweaver
                                                        • 25. Re: Update 2 tables with one form...
                                                          Günter Schenk Level 4
                                                          Hi Steve,

                                                          -------
                                                          $update_request = "UPDATE request SET request_status = '".$tNG->getColumnValue("request_status")."' WHERE ID = '".$_GET['open_request_id']."';
                                                          $update_result = $tNG->connection->execute($update_id);
                                                          if(!$update_result) {
                                                          $updateError = new tNG_error("Error setting the field to 5", array(), array());
                                                          return $updateError;
                                                          } else {
                                                          return NULL;
                                                          }
                                                          -------

                                                          the line "$update_result = $tNG->connection->execute($update_id);" is definitely wrong, because you´ll have to execute the "$update_request" query variable and not the non existing variable "$update_id".

                                                          Please change this to:

                                                          $tNG->connection->execute($update_request)

                                                          Cheers,
                                                          Günter Schenk
                                                          Adobe Community Expert, Dreamweaver
                                                          • 26. Re: Update 2 tables with one form...
                                                            Level 1
                                                            Gunter,

                                                            Here is the deal...

                                                            I have DW running in 4 computers in my home, 2 with different software builds.
                                                            I started a blank php page on my second desktop computer (different software build) in order to make certain that there wasn't a quirk in my laptop copy of DW or my CSS which was clear of any errors.

                                                            I rebuilt the update form, this time keeping it very basic, just the id field and request_status field.

                                                            The result... The identical situation. The form css was stripped, exclamation marks popped up again, and the form failed to display upon saving and previewing.

                                                            I hate to say it but there is no technical issue Adobe can help me with, it's something in the trigger code that is causing my CSS to disappear and the value not to be changed in the "request" table.

                                                            Thanks

                                                            Steve BT
                                                            • 27. Re: Update 2 tables with one form...
                                                              Level 1
                                                              Thanks Gunter,

                                                              Tried your new solution with the same results.

                                                              I don't know what else to do, my entire project hinges on figuring this out.

                                                              Thanks,

                                                              Steve
                                                              • 28. Re: Update 2 tables with one form...
                                                                Level 1
                                                                Gunter,

                                                                Whooooooooooooooo!!

                                                                Great news my man, I got the static option to work (no problem with css and the appropriate fields changed in both tables!)

                                                                Here is the code I used with your changes applied:

                                                                $update_request = "UPDATE request SET request_status = '5' WHERE ID = '269'";
                                                                $update_result = $tNG->connection->execute($update_request);
                                                                if(!$update_result) {
                                                                $updateError = new tNG_error("Error setting the field to 5", array(), array());
                                                                return $updateError;
                                                                } else {
                                                                return NULL;
                                                                }

                                                                I attempted to place the dynamic options in, it again failed. Any thoughts?

                                                                Thanks

                                                                Steve BT

                                                                PS. Almost there, I can taste it. I owe you big!
                                                                • 29. Re: Update 2 tables with one form...
                                                                  Level 1
                                                                  Gunter,

                                                                  Upon adjusting the code I got the update going to the proper ID "dynamically". It was a matter of changing a few characters in the "WHERE" portion of the code. Obviously the miss written code also caused the CSS issue (btw, it has happened to me in the past where adding a character caused chaios in my page style).

                                                                  I knew it was something with the code that was causing the error! Now the problem is 80% solved the only issue remains in the first part of the trigger statement.

                                                                  Rather than using the static option which works fine:

                                                                  $update_request = "UPDATE request SET request_status = '5'

                                                                  I would like to implement the dynamic option:

                                                                  $update_request = "UPDATE request SET request_status = '".$tNG->getColumnValue("request_status")."'

                                                                  Which again when I inject causes chaos on my page.

                                                                  Just to reiterate, everything is working fine when the form is submitted to the database with the dynamic "ID" option and static "request_status" option.

                                                                  Now the only thing that's not working is the dynamic read of the hidden field "request_status". I still must achieve proper form operation by placing in the static option of "5".

                                                                  Something is wrong with this code:

                                                                  $update_request = "UPDATE request SET request_status = '".$tNG->getColumnValue("request_status")."'

                                                                  Steve BT
                                                                  • 30. Re: Update 2 tables with one form...
                                                                    Level 1
                                                                    Anyone have any though why this piece of trigger code isn't working?

                                                                    $update_request = "UPDATE request SET request_status = '".$tNG->getColumnValue("request_status")."'

                                                                    Thanks

                                                                    Steve
                                                                    • 31. Re: Update 2 tables with one form...
                                                                      Günter Schenk Level 4
                                                                      Hi Steve,

                                                                      may I contact you in private and "virtually" look over you shoulder to see what´s going on and try to fix this issue together ? If yes, there´s no need to tell me your email address

                                                                      Cheers,
                                                                      Günter Schenk
                                                                      Adobe Community Expert, Dreamweaver
                                                                      • 32. Re: Update 2 tables with one form...
                                                                        Level 1
                                                                        Hi Gunter,

                                                                        Absolutely! I would really appreciate your help.

                                                                        Thanks a million,

                                                                        Steve BT