creating an archive

[jjsand28]

I am trying to create an archive menu that would look like this

November 2004 (10)

where the (10) is the number of posts for that month and year.

This is what i have so far but everything else I tried hasn't worked. i tried
loops and month, year functions and I'm running out of hair to pull out. Aso
this menu is a going to be a set of links so how would I quer the db to get
only those records? when i do a set

<cfset thismonth = datepart('m',#passedVariable1#)>

All I get is an error.

I don't necessarily need it done for me as i wouldn't learn that way but a
good tutorial or some suggestions would be very helpful.

<!--- get the news items --->
<cfquery name="getnewsArc" datasource="#Application.dsn#">
select * from sy_news
order by newsDate desc
</cfquery>

<ul>
<cfoutput query="getNewsArc" group="newsDate">
<li>#DateFormat(getNewsArc.newsDate, 'mmmm yyyy')#</li>
</cfoutput>
</ul>

[mxstu]

I'll let you work out the syntax details for your database type, but here are
some thoughts that might help you get started.

To get the total post counts by month/year, try using database date functions,
instead of CF date functions. The functions for your database may vary, but
something like:

-- not tested
SELECT datePart(m, newsDate) As MonthNumber,
dateName(mm, newsDate) As MonthName,
datePart(yyyy, newsDate) AS YearNumber,
count(*) AS TotalPosts
FROM sy_news
GROUP BY datePart(m, newsDate), datePart(yyyy, newsDate)
ORDER BY YearNumber, MonthNumber


If your database does not have a dateName() function, you could just extract
the month number and use CF's MonthAsString(monthNumber) function.

There are a few different methods for extracting data for an entire month.
The easiest method is probably to just pass the month and year numbers as url
parameters and and use your database's datePart() function in your cfquery:


-- not tested
SELECT newsDate, otherColumns...
FROM sy_news
WHERE datePart(m, newsDate) = #url.monthNumber# AND
datePart(yyyy, newsDate) = #url.yearNumber# AND



However, using functions can sometimes cause database engines to ignore
indexes and reduce query performance. An alternative might be to use the url
parameters to create start and end date variables and use those in your cfquery:


<cfset firstOfThisMonth = createDate(url.yearNumber, url.monthNumber, 1)>
<cfset firstOfNextMonth = dateAdd("m", 1, firstOfThisMonth)>
<cfquery ....>
SELECT newsDate, otherColumns...
FROM sy_news
WHERE newsDate >= #createODBCDate(firstOfThisMonth)# AND
newsDate < #createODBCDate(firstOfNextMonth)# AND
</cfquery>