Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Show archive DESC from date and add link

Posted on 2012-03-31
Medium Priority
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

571 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