4 Replies Latest reply on Sep 3, 2009 3:34 PM by Günter Schenk

    Automatically delete records after specified time

    mrcharis2003@yahoo.c Level 1

      Hi there everyone

       

      Long time no post....... been busy

       

      My latest mission in DW is to create a list of upcoming events for a site I am working on......

       

      I can create all the things I need....... the databese..... the upload form and the list upcoming events form .........

       

      I would like to be able to automatically delete all the events that have already taken place......

       

      I have all the fields to describe the event in my database ....... I added an extra field to store the       event expiry date  .....

       

       


      Is there anyway I can achieve this with ADDT?...... with little .... or preferably NO .... codin..... or some easy stuff is ok...

       

      Anyway

       

      Hope someone can help a bit or steer me in the right direction

       

      Have a great day  

       

       

      Cheers

        • 1. Re: Automatically delete records after specified time
          zzipper7

          To remove them from view on your public list, change your Mysql query to:

               SELECT* yourtable WHERE date_feild is > CURDATE()-1

          ..then only dates greater than yesterday will show.

          -

          To remove them from your DB:

          -

          Set up a page for the admin that queries old events. Create a master list in ADDT. Delete from there. The Master list function allows for multiple record deletion by single checkbox or check all function.

          -

          Good Luck

          • 2. Re: Automatically delete records after specified time
            Günter Schenk Level 4

            If your eventexpirydate_column has the standard MySQL YYYY-MM-DD format, you could use the following query to automatically delete records which are older than "now":

             

            DELETE FROM tablename WHERE eventexpirydate_column < CURDATE()

             

            Cheers,

            Günter

            • 3. Re: Automatically delete records after specified time
              mrcharis2003@yahoo.c Level 1

              Hi there Gunter

               

              Thanks for your reply

               

              I understand the query you wrote for me

               

              but I don't know how to get it to work .... where to insert it?

               

              I not sure how to 'Use' the query

               

              Could you please explain how and exactly where to put the query?.......

               

                I not sure if I have to build a query using QUB .... or inset the query into the      add sql     in phpmyadmin

               

              Any help would be great

               

              Have a great day

               

              Cheers

              • 4. Re: Automatically delete records after specified time
                Günter Schenk Level 4

                mrcharis2003@yahoo.c wrote:

                 

                I understand the query you wrote for me

                 

                but I don't know how to get it to work .... where to insert it?

                 

                I not sure how to 'Use' the query

                well, Dreamweavers recordset builder (and ADDT´s QUB too, for that matter) is - honestly said - not the ideal tool to have you establish such a "bulk" recordset deletion, as the standard "delete record" server behaviours are meant to work with single records only. Right out of the box you´d always need to supply the Primary Key of the to-be-deleted record as URL or Form parameter, but that´s of course not what you need in such a case.

                 

                In fact it would be possible to tweak the generated code of a page which contains a standard DW "delete record" behaviour by replacing the initial query with such a "bulk" one -- but as such a tweak requires some additional steps, I´d rather suggest to use the in-built date filtering features of your ADDT Dynamic List.

                 

                I think the following should work:

                 

                1. type the following into the "filter" input field which corresponds to the date column in question:

                 

                <2009-09-09 (aka "today")

                 

                In case you´re using a different "display date format", you´d have to type this one instead of the indicated native MYSQL date format. However, the leading "<" stands for "lower than", and ADDT´s Dynamic Lists allow for filtering dates using such comparison parameters

                 

                2. try filtering the list according to the previously mentioned "rule"

                 

                3. assuming the filtered list now indeed only displays records which are older than "today", check them all and...

                 

                4. click the "delete all..." link to have ADDT´s corresponding Dynamic Form delete the selected records.

                 

                Does this work for you ?

                 

                Cheers,

                Günter