3 Replies Latest reply on Oct 6, 2010 6:03 AM by Joshua Cyr

    Creating an archive from db data?

    cfsetNewbie Level 1

      Hello;

      I was wondering if there is way to use coldfusion (and my database) to create archives in records after a certain date? I've been looking all over the web to see if there is a way or if someone has made it.. I have only found .zip file archives.

       

      Can anyone point me in the right direction to accomplish this? Is it possible?

       

      This is what I want to do maybe a query of queries on all articles any in:in the month of september go to a september link and the same for each month, then also broken down by years.

       

      I started with a strait up query with next and previous capabilities.I can post the code if needed. Now I want to archive some of the records out to another page / query.

       

      Thank you.. any help is appreciated

        • 1. Re: Creating an archive from db data?
          JR "Bob" Dobbs Level 4

          I would avoid using "query of queries" where possible and let your database handle executing SQL statements.

           

          Perhaps you could describe your goal in more detail.  What do you mean by "archive", is an archive a separate database?  Are your "articles" already stored in a database?  Posting your existing code would also help.
          • 2. Re: Creating an archive from db data?
            cfsetNewbie Level 1

            This is what I have right now.. (working)

             

            Right now, I am calling up the records and separating them into "pages" 6 records per page.

             

            I wold like to archive older posts to a seperate "nav" on the right. Like a blog works.. with the month and all records attached to that month appear when you click on the months name. maybe keep the 6 most recent and give them time before they go to an archive status.

             

            Is this possible or just overthinking?

             

            Here is my code so far:

             

            <cflock timeout="2" scope="application" type="READONLY">
            <cfquery name="articles224" datasource="#APPLICATION.dataSource#">
                SELECT articles520.title AS ViewField2, articles520.artDate AS ViewField3, articles520.author AS ViewField4, articles520.publication AS ViewField5, articles520.htmlLink AS ViewField6, articles520.MYFile AS ViewField7, articles520.runDate AS ViewField8, articles520.ID AS ID
                FROM articles520
                ORDER BY artDate DESC
            </cfquery>
            <cfset application.articles224=articles224>
            </cflock>
            <cfset rowsPerPage = 6>
            <cfparam name="URL.startRow" default="1" type="numeric">
            <cfset totalRows = articles224.recordCount>
            <cfset endRow = min(URL.startRow + rowsPerPage - 1, totalRows)>
            <cfset startRowNext = endRow + 1>
            <cfset startRowBack = URL.startRow - rowsPerPage>

             

            <cfoutput>

            <cfif startRowBack GT 0>
                            <a href="#CGI.script_name#?startRow=#startRowBack#" class="nav">&lt; Previous</a>
                          </cfif>

             

            <cfif startRowNext lte totalRows>
                            <a href="#CGI.script_name#?startRow=#startRowNext#" class="nav">Next &gt; </a>
                          </cfif>

            </cfoutput>

             

            <cfloop query="articles224" startRow="#URL.startRow#" endrow="#endRow#">
                <cfoutput>

            <!--- all my layout for the info goes here --->

            </cfoutput>

            </cfloop>

             

             

            Thanks

            • 3. Re: Creating an archive from db data?
              Joshua Cyr Level 3

              Probably just update your query to search for current month only, then your archive on the left can just be a list of months (or years, or whatever) which tells the db to query just that span.

               

              Below x and y are whatever your dates in the span.

               

              <cfquery name="articles224" datasource="#APPLICATION.dataSource#">
                  SELECT articles520.title AS ViewField2, articles520.artDate AS ViewField3, articles520.author AS ViewField4, articles520.publication AS ViewField5, articles520.htmlLink AS ViewField6, articles520.MYFile AS ViewField7, articles520.runDate AS ViewField8, articles520.ID AS ID
                  FROM articles520

               

              Where articles520.artDate between  x and y

               

                  ORDER BY artDate DESC
              </cfquery>