9 Replies Latest reply on Mar 27, 2008 4:32 PM by KDS-Djembe

    Custom Transaction or Trigger to update multiple records?

    KDS-Djembe Level 1
      I'm creating a backend for a website where my clients use a dynamic list and form to insert, edit and delete Special Notices that will be displayed on their home page. Only one Special Notice entry is to be visible on the home page at a time. Accordingly, I've included a 'Visibility' field in the table and the form has a corresponding drop-down menu with 'Yes' and 'No' values. I'd like to ensure that when the visibility of one record is set to 'Yes', the value of the others is updated to 'No'.

      Here's the poor half solution I've come up with...
      I added a Custom Transaction, from the first check drop-down I selected "Entered Value", gave it a value of 1, left the Fields tab empty, and in the Advanced tab I entered the following SQL:
      UPDATE special SET visible_special = 'No'
      This of course updates all of the visible_special fields to 'No' and I can then implicitly update a record to set it to 'Yes' as needed. However, this executes immediately on page loads, including cancelled Inserts so it's really clumsy.

      Can anyone here point me towards a better solution?
      Thanks for absolutely any suggestions whatsoever.

      Table: special
      ----------------------------------------------------
      id_special tinyint(3) auto_increment
      date_special date
      entry_special text
      visible_special varchar(4) (Yes or No)
        • 1. Re: Custom Transaction or Trigger to update multiple records?
          KDS-Djembe Level 1
          Ok, after digging through many old posts on the Interakt forum and blinking like an idiot at the documentation regarding triggers, I was able to piece together a solution...

          I removed the Custom Transaction altogether and used a Custom BEFORE Trigger instead. (Registered to Insert and Update transactions.)

          Trigger Code:
          $query = "UPDATE special SET visible_special = 'No'";
          $tNG->connection->Execute($query) or die($tNG->connection->ErrorMsg());

          This appears to do exactly what I need. :-)
          • 2. Re: Custom Transaction or Trigger to update multiple records?
            Sounds too complex. Instead, you should normalize out the 'visible' column into another table. Then this other table has only a single row which has the page identifier/name as the primary key and a foreign key column pointing to the currently displayed row. Then whenever you update the row it automatically prevents other sets of content from being pointed to as one row will only ever refer to a single record in the content table.
            • 3. Re: Custom Transaction or Trigger to update multiple records?
              KDS-Djembe Level 1
              Thanks for the reply Sean.

              You're probably right. I really struggle with this stuff and am in over my head every step of the way. Accordingly, I'm not very clear on how to do what you've described. (Specifically I'm not clear how to set up a table which, "has the page identifier/name as the primary key and a foreign key column pointing to the currently displayed row".)

              If you have any time to elaborate, I'd be really grateful.
              • 4. Re: Custom Transaction or Trigger to update multiple records?
                KDS-Djembe Level 1
                I'm really operating in the dark here...

                I've created an additional table as follows:

                Table: specialact
                -------------------------------------------------
                id_specialact tinyint(3) (Primary)
                id_spec_specialact tinyint(3)
                visible_specialact varchar(4) (Default value 'No')

                I then created the following recordset on the pages that are to contain the Dynamic List and Dynamic Form:

                rsSpecial
                -------------------------------------------------
                SELECT special.*, specialact.*
                FROM (special
                LEFT JOIN specialact ON specialact.id_spec_specialact=special.id_special)

                I'm guessing that this is the structure of the table you were suggesting? Big emphasis on the "guessing". When I added a Dynamic List to the page, I could then choose to 'Get data from Recordset' and selected the rsSpecial recordset I'd created. I selected id_special as the Primary key column (from the first table) and specify the detail page containing the form. Additionally, I specified the columns that I wanted to display including visible_specialact.

                I then moved on to the page that was to contain the Dynamic Form. I presumed, incorrectly, that its wizard would have a similar option to that of the Dynamic List to allow me to choose between a Table and a Recordset, however that wasn't the case. It only allows one to select a table to edit. So clearly I'm going about this approach all wrong.

                In other words, I just barely get my brain around your suggestion enough to understand that it's more streamlined, but I don't know how to go about implementing it.
                • 5. Re: Custom Transaction or Trigger to update multiple records?
                  Level 1
                  Hi J,

                  You got the basic idea of using two tables. This allows you to force the database to keep things straight (this is about database design and that is a subject unto itself).

                  But you _do_ _not_ want to have anything but two columns in it. The whole visible thing is unneeded as the one row in that table tracks the currently visible notice. If that row is deleted or points to no notice (zero or a negative value that will cause the join to return an empty set). This row in the new table defines the current special notice.

                  You also want to simply your JOIN to:

                  SELECT special.*, specialact.*
                  FROM special, specialact
                  WHERE specialact.id_spec_specialact=special.id_special

                  so that if there is no currently defined special notice, then the join causes the select to return zero rows. This is essentially using shorthand syntax for an INNER JOIN.

                  Being a coder rather than a designer, I am still wrapping my mind around these ADDT methods (I would traditionally just write plain old SQL and execute it). That said, I would guess that your page will need 2 update transactions (one from the wizard and a second one added via Server Behaviors->Developer Toolbox->Forms->Advanced->Update Record Transaction) to update the new table.

                  Hopefully this helps. This response is (yet again), quick and sloppy.

                  enjoy,

                  Sean
                  • 6. Re: Custom Transaction or Trigger to update multiple records?
                    KDS-Djembe Level 1
                    Hi Sean,

                    Thanks for attempting to help me but I'm afraid that I'm extraordinarily dense. Coding (and apparently database design) is a huge struggle for me. I think I'm getting the concept behind your suggestion; that there should be a second table that keeps track of which record is visible. In other words, rather than keeping track of the visibility of all of the special notice records individually and ensuring that only one is currently enabled at a time, you are suggesting that I simply keep a single separate record, via a foreign key, indicating which special notice record is currently enabled. It's all of the specifics that stump me. :-)

                    I want my clients to be able to look through past special notice entries and be able to edit and re-enable them as needed (The special notices will contain re-occurring sales offers, annual event notices, etc.) I'm developing my project locally, but I've posted online examples of more or less what I'm trying to achieve here: http://www.tueller.net/testform/specialnotice.php This is using the Custom BEFORE Trigger I described in my second post. While generally functioning as intended, it's an illogical approach since it will require updating increasingly more records as time goes on. Also, I removed the option to set visibility on Insert because I couldn't figure out how to ensure that one record always remained enabled (the user could select 'No' on Insert, making all records disabled.)

                    I stayed up through the night trying a variety of different approaches with the two tables without luck. When I attempt to add the Update Record Transaction to the page containing the Dynamic Form, I'm lost about what options are appropriate. (Basic Tab: Primary key equals, First check variable - Fields Tab: Get value from, Variable name). Further, since this doesn't add any actual form elements to the existing form, I don't see how it can do what I'm after. Perhaps I'd have to manually add a checkbox element into the form and do some hand coding?

                    Perhaps after some sleep something will click. :-/
                    Thanks again for your effort.
                    • 7. Re: Custom Transaction or Trigger to update multiple records?
                      KDS-Djembe Level 1
                      Ugh. After another 14+ hours of frustrating dead-ends, I hang my head in defeat.

                      I really miss the helpful support that Interakt crew used to provide.
                      • 8. Re: Custom Transaction or Trigger to update multiple records?
                        Level 1
                        Instead of letting the user mark this with this submission, why not just show a list of notices on another page with radio buttons or use a <select> which sets the active notice. It is less convenient for the user, but simpler for the programmer (i.e., make it work). Then you can use two simple updates.

                        Otherwise you would have to handle issues relating to using an after trigger when creating a new notice and making that notice active and acquiring the auto_increment value which is the primary key for the notice on SQL INSERTs.

                        Below is some suggested basic table structure showing the relationships between the tables. Note how the active notice table has its entry pre-populated.

                        CREATE TABLE `someapp_notices` (
                        `id` int(10) unsigned NOT NULL auto_increment,
                        `notice` text NOT NULL,
                        PRIMARY KEY (`id`)
                        ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
                        CREATE TABLE `someapp_active_notice` (
                        `id` int(10) unsigned NOT NULL auto_increment,
                        `active` int(10) unsigned NOT NULL,
                        PRIMARY KEY (`id`)
                        ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
                        INSERT INTO `someapp_active_notice` (`id`,`active`) VALUES
                        (1,0);

                        This allows you to use an INNER JOIN to get an empty result when the active notice pointer is set to zero. i.e.,

                        SELECT *
                        FROM `someapp_active_notice` AS `an`, `someapp_notices` AS `n`
                        WHERE `an`.`active`=`n`.`id`

                        And then use simple updates on the two tables in ADDT on separate pages.

                        - Update: Escaped HTML tag.
                        • 9. Re: Custom Transaction or Trigger to update multiple records?
                          KDS-Djembe Level 1
                          First, my apologies for my previous post. I realize in hindsight that I may have sounded like I was complaining about not getting a ready-made solution when you've so kindly gone out of your way to assist me.

                          Your suggestion about having a second list/form to allow the user to indicate the currently visible special notice record makes sense. As you point out, it may not be ideal but I think that I'm more likely to be able to figure it out. The sample table and recordset are instructive. I'll give a go at this approach and see what happens.

                          Again, thanks for all of your help.