Archive by Month Menu - blog style

I would like to create a menu system in the sidebar of a site that displays a link for each month and year (in Desc order) for articles from that month. If no articles are available for that month, no link will be provided.

Do you know how this is done?

Do you know of a tutorial which can show me how to create this?

Thanks

you would need a recordset to query the blog table for the date posted, then use the recordset to populate your menu.

something like:

SELECT Date_format(dateposted, '%M, %Y'),
FROM blog_entries
GROUP BY YEAR(dateposted),
MONTH(dateposted)
ORDER dateposted DESC

Hi Jason

I ended up using;

SELECT Date_format(pcms2_contents.ContentDate, '%M, %Y'), COUNT(*) AS cc FROM pcms2_contents WHERE pcms2_contents.ContentPageName='News' AND pcms2_contents.ContentPageSetParentID=12 GROUP BY YEAR(pcms2_contents.ContentDate), MONTH(pcms2_contents.ContentDate) ORDER BY COUNT(*) DESC, pcms2_contents.ContentDate DESC

When I tested this in DW it displayed the results as expected, however when I tried to add the results to a page it didnt work.

The code I was using was created by DW....

<?php echo $row_Recordset1['Date_format(pcms2_contents.ContentDate, '%M, %Y')']; ?> (<?php echo $row_Recordset1['cc']; ?>)

It appears to be

<?php echo $row_Recordset1['Date_format(pcms2_contents.ContentDate, '%M, %Y')']; ?>

that is breaking the page.

SELECT Date_format(pcms2_contents.ContentDate, '%M, %Y'),


should be:
SELECT Date_format(pcms2_contents.ContentDate, '%M, %Y') as contDate,


then:
<?php echo $row_Recordset1['Date_format(pcms2_contents.ContentDate, '%M, %Y')']; ?>

would be:
<?php echo $row_Recordset1['contDate']; ?>

Thank you Jason!

Just for the benefit of others who might be trying to do this with PowerCMSBuilder News Items where you use a page as a template;

Query

  SELECT Date_format(pcms2_contents.ContentDate, '%M, %Y') as contDate,
COUNT(*) AS cc FROM pcms2_contents
WHERE pcms2_contents.ContentPageName='News' AND pcms2_contents.ContentPageSetParentID=12
GROUP BY YEAR(pcms2_contents.ContentDate),
MONTH(pcms2_contents.ContentDate)
ORDER BY pcms2_contents.ContentDate DESC  

Displayed using;

<?php
    // RepeatSelectionCounter_1 Begin Loop
    $RepeatSelectionCounter_1_IterationsRemaining = $RepeatSelectionCounter_1_Iterations;
    while($RepeatSelectionCounter_1_IterationsRemaining--){
        if($RepeatSelectionCounterBasedLooping_1 || $row_Recordset1){
?>
<?php echo $row_Recordset1['contDate']; ?> (<?php echo $row_Recordset1['cc']; ?>)<br/>
<?php
    } // RepeatSelectionCounter_1 Begin Alternate Content
    else{
?>
<?php } // RepeatSelectionCounter_1 End Alternate Content
        if(!$RepeatSelectionCounterBasedLooping_1 && $RepeatSelectionCounter_1_IterationsRemaining != 0){
            if(!$row_Recordset1 && $RepeatSelectionCounter_1_Iterations == -1){$RepeatSelectionCounter_1_IterationsRemaining = 0;}
            $row_Recordset1 = mysql_fetch_assoc($Recordset1);
        }
        $RepeatSelectionCounter_1++;
    } // RepeatSelectionCounter_1 End Loop
?>

Hi Jason and Daryl

Thanks for this thread - have been trying to implement this but its not showing on the live page.

I have a template group in PCMS called Blog Items

I have created a recordset ( BlogRS ) for the Blog Items to be shown on the page.

I created a 2nd recordset ( BlogMenuRS ) to create the menu system mentioned in this thread.

This is the code I used for the BlogMenuRS recordset:

  SELECT Date_format(pcms2_contents.ContentDate, '%M, %Y') as contDate, COUNT(*) AS cc
FROM pcms2_contents
WHERE pcms2_contents.ContentPageName='Blog Items' AND pcms2_contents.ContentPageSetParentID=12
GROUP BY YEAR(pcms2_contents.ContentDate), MONTH(pcms2_contents.ContentDate)
ORDER BY pcms2_contents.ContentDate DESC  

and this is your code amended to reflect my recordset:

<?php
    // RepeatSelectionCounter_1 Begin Loop
    $RepeatSelectionCounter_1_IterationsRemaining = $RepeatSelectionCounter_1_Iterations;
    while($RepeatSelectionCounter_1_IterationsRemaining--){
        if($RepeatSelectionCounterBasedLooping_1 || $row_BlogMenuRS){
?>
<?php echo $row_BlogMenuRS['contDate']; ?> (<?php echo $row_BlogMenuRS['cc']; ?>)<br/>
<?php
    } // RepeatSelectionCounter_1 Begin Alternate Content
    else{
?>
<?php } // RepeatSelectionCounter_1 End Alternate Content
        if(!$RepeatSelectionCounterBasedLooping_1 && $RepeatSelectionCounter_1_IterationsRemaining != 0){
            if(!$row_BlogMenuRS && $RepeatSelectionCounter_1_Iterations == -1){$RepeatSelectionCounter_1_IterationsRemaining = 0;}
            $row_BlogMenuRS = mysql_fetch_assoc($BlogMenuRS);
        }
        $RepeatSelectionCounter_1++;
    } // RepeatSelectionCounter_1 End Loop
?>

But for some reason, its not displaying on the live page.... obviously I have either not done something or done it incorrectly and would appreciate your help please

Many thanks

Jamie

Hi Jamie

You may need to change the contentpagesetparentid = 12 to a different number based on your database.

Thanks for the quick response Daryl

Sorry to be a bit thick, but where would I find what it needs to be?

Love your random sheep site by the waY!

Cheers

Jamie

Hi Jamie

Login to your database, have a look in the pcms2_contents table and find the row which contains the 'Blog Items' in the ContentGroup column and 'Title' in the ContentRedID column. Then look in the ContentPageSetParenID column to see what the value is on the same row. This number will then need to replace the 12 in the SQL that was used as an example.

Hopefully that made sense....

thanks for that Daryl... found that (it was 216) but the menu is still not visible....

Jamie