Skip to main content
C
cfsetNewbie
Known Participant
October 4, 2010
Question

Creating an archive from db data?

  • October 4, 2010
  • 1 reply
  • 584 views

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

This topic has been closed for replies.

1 reply

J
JR__Bob__Dobbs
Inspiring
October 4, 2010

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.
C
cfsetNewbieAuthor
Known Participant
October 4, 2010

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

J
Joshua_Cyr
Participating Frequently
October 6, 2010

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>

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices