SQL Archive

hutch posted this at 13:08 — 18th September 2005.

Joined: Jul 2005

I am trying to find out how to set up a month by month archive on a SQL database. The enclosed page is the code, and if you want to see the site it is
cfc-a.centcom.mil
We have a multitude of articals and what I would like to do is set up a month by month archive. Any guidence would be much appreciated.

<?php
Option Explicit
Dim StoryID
Dim dcnDB
Dim strSQL
Dim rsfeature
Dim rsmedia
Dim rsLeadership
Dim rsMilitary
Dim rsExternal
Dim rsCommunications
Dim rsRight
Dim rsHeadline
Dim rsTop
Dim rsFooter 
Dim rsPhoto
Dim rsStory
Dim ID
Dim rsNewcomers


IF Session ("Banner") = "" Then
Response.Redirect("security.asp")
End IF

StoryID = Request.QueryString("StoryID")

Set dcnDB = Server.CreateObject("ADODB.Connection")
dcnDB.open "PROVIDER=SQLOLEDB;DATA SOURCE=cfc-webserver2;DATABASE=cfcPublicSite;uid=webuser;password=webuser "

strSQL = "SELECT * FROM Links " _
    & "WHERE Feature = 1 " _
    & "ORDER BY LinkPriority"
Set rsfeature = dcnDB.Execute(strSQL)

strSQL = "SELECT * FROM Links " _
    & "WHERE media = 1 " _
    & "ORDER BY LinkPriority"
Set rsmedia = dcnDB.Execute(strSQL)

strSQL = "SELECT * FROM Links " _
    & "WHERE Newcomers = 1 " _
    & "ORDER BY LinkPriority"
Set rsNewcomers = dcnDB.Execute(strSQL)

strSQL = "SELECT * FROM Links " _
    & "WHERE Leadership = 1 " _
    & "ORDER BY LinkPriority"
Set rsLeadership = dcnDB.Execute(strSQL)

strSQL = "SELECT * FROM Links " _
    & "WHERE Military = 1 " _
    & "ORDER BY LinkPriority"
Set rsMilitary = dcnDB.Execute(strSQL)

strSQL = "SELECT * FROM Links " _
    & "WHERE External = 1 " _
    & "ORDER BY LinkPriority"
Set rsExternal = dcnDB.Execute(strSQL)

strSQL = "SELECT * FROM Links " _
    & "WHERE Communications = 1 " _
    & "ORDER BY LinkPriority"
Set rsCommunications = dcnDB.Execute(strSQL)

strSQL = "SELECT TOP 10 * FROM Stories " _
    & "WHERE Headline = 0 " _
    & "ORDER BY StoryID DESC"
Set rsRight = dcnDB.Execute(strSQL)

strSQL = "SELECT * FROM Links " _
    & "WHERE Footer = 1 " _
    & "ORDER BY LinkPriority"
Set rsFooter = dcnDB.Execute(strSQL)

strSQL = "SELECT * FROM Links " _
    & "WHERE Header = 1 " _
    & "ORDER BY LinkPriority"
Set rsTop = dcnDB.Execute(strSQL)

If StoryID = "" Then
strSQL = "SELECT * FROM Stories " _
     & "WHERE Headline = 1 " _
     & "ORDER BY StoryID DESC"
Set rsHeadline = dcnDB.Execute(strSQL)

ID = rsHeadline("StoryID")

strSQL = "SELECT Top 2 * FROM Photos " _
        & "WHERE StoryID = " & ID
Set rsPhoto = dcnDB.Execute(strSQL)

Else

strSQL = "SELECT * FROM Stories " _
     & "WHERE StoryID = " _
     & StoryID _
     & "ORDER BY StoryID DESC"
Set rsStory = dcnDB.Execute(strSQL)

strSQL = "SELECT Top 2 * FROM Photos " _
        & "WHERE StoryID = " & StoryID
Set rsPhoto = dcnDB.Execute(strSQL)
End If
?>

<?php
   Do While Not rsFooter.EOF
       Response.Write "<span class='rsTop'>" _ 
           & "<a href=" _
           & rsFooter("LinkAddress") _
           & ">" _
           & rsFooter("LinkName") _
           & "</a>" _
           & "</span>"
           If not rsFooter("LinkName") = "Privacy and Security" then
           Response.Write " " _
           & "|" _
           & " " 
           End If
        rsFooter.MoveNext
    Loop
?>

<?php
   rsFooter.Close
   rsFeature.Close
   rsmedia.Close
   rsNewcomers.Close
   rsLeadership.Close
   rsMilitary.Close
   rsRight.Close
   rsTop.Close
   rsExternal.Close
   If StoryID ="" Then
   rsHeadline.Close
   Else
   rsStory.Close
   End If
   rsPhoto.Close
   dcnDB.Close
   Set dcnDB = Nothing
?>

DigitalOmaha posted this at 19:55 — 23rd October 2005.

They have: 2 posts

Joined: Nov 2002

Just add a Date column/field to any table containing data you wish to archive by month. Insert the current date into that column on creation and/or update (or add a new column for updates). Then add this to the select statement "WHERE DATE < 02/01/2005 AND DATE >= 01/01/2005" (you need to check the database date format on your system this is more as an example).

Now all you need to do is setup some way of setting the month your looking at from within the page. A dual nested combo box with year and then month would work the best.

Cheers!