Show archive DESC from date and add link

Posted on 2012-03-31
Last Modified: 2012-08-14
I have an archive function working on my Blog using the following code...
mysql_select_db($database_acsinfo, $acsinfo);
 $query_archive = "SELECT YEAR(`eventdate`) AS 'year', MONTH(`eventdate`) AS 'month', COUNT(`id`) AS 'count'  FROM `latestnews` GROUP BY YEAR(`eventdate`), MONTH(`eventdate`) DESC
$result = mysql_query($query_archive, $acsinfo) or die(mysql_error());

$data = array();
while($row = mysql_fetch_assoc($result)) {
if( intval($row['count']) >0) $data[$row['year']][$row['month']] = $row['count'];


Open in new window

 <?php foreach ($data as $year => $months) {
    echo $year.'';
    foreach ($months as $month => $count) {
            echo date("F", mktime(0,0,0,$month,1,2000)).'('.$count.')<br>';
} ?>

Open in new window

How can I get the dates to show the other way round? ie 2012 first.

Also how would add a link? I need to send a link to the detail page which will show posts from that year & month.
Question by:BrighteyesDesign
  • 2

Accepted Solution

OCDan earned 500 total points
ID: 37791495
Replace your archive query with this one:
SELECT YEAR(`eventdate`) AS 'year', MONTH(`eventdate`) AS 'month', COUNT(`id`) AS 'count'  FROM `latestnews` GROUP BY YEAR(`eventdate`), MONTH(`eventdate`) ORDER BY YEAR('eventdate') DESC, MONTH('eventdate') DESC

Author Comment

ID: 37796935
That's great, thanks for that.

Where would I add a link around the results so you click on  'July (1)' for example, it takes you a page with the month in the URL to display posts from that month?

I have tried...

        <a href="searchrecent.php?month=<?php echo $month ?>" class="sidemenu"> <?php foreach ($data as $year => $months) {
    echo $year.'';
    foreach ($months as $month => $count) {
            echo date("F", mktime(0,0,0,$month,1,2000)).'('.$count.')<br>';
} ?></a>

Open in new window


Author Comment

ID: 37805692
Anyone know how to do this?

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP Healthcheck 2 85
File submission to SQL Server 1 39
Making register page log you in after registration is complete. 2 14
Problem sending file attachments 8 23
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now