Re: Re: [benedict] Count News Items That Match Month & Year (and link to them)

Hi Hiroko,

If you don't want to hard-code the start year, you can use this code to find the year of the earliest record:

<?php
  $earliestRecord = mysql_get('news', null, 'TRUE ORDER BY createdDate ASC');
  $earliestYear   = date('Y', strtotime($earliestRecord['createdDate']));
?>

Then you can use this code to loop from the current year to the earliest year and display a count for each:

<?php foreach (range(date('Y'), $earliestYear) as $year): ?>
  <?php $count = mysql_count("news", "YEAR(createdDate) = $year"); ?>
  <a href="newsList.php?createdDate_keyword=<?php echo $year;?>"><?php echo $year; ?> (<?php echo $count;?> posts)</a><br/>
<?php endforeach ?>

I hope this helps! Please let me know if you have any questions.

Hi Chris,

Thank you for your help.

I would also like to show the months like this. How can I add the month and year to become a list?

For example,

January 2014 (5 posts)

December 2013 (10 posts)

November 2013 (10 posts)

October 2013 (10 posts)

... and so on

It will become a long long list ... but I prefer this way to show my archive.

Hiroko

Hi Hiroko,

Does this do what you want?

<?php
  $earliestRecord = mysql_get('news', null, 'TRUE ORDER BY createdDate ASC');
  $earliestYear   = date('Y', strtotime($earliestRecord['createdDate']));
?>
<?php
  foreach (range($earliestYear, date('Y')) as $year) {
    foreach (range(1, 12) as $month) {
      
      $monthPadded = str_pad($month, 2, 0, STR_PAD_LEFT); // zero padding for month number
      $beginningOfMonthTime = strtotime("$year-$monthPadded-01");
      
      // skip months in the future
      if ($beginningOfMonthTime > time()) {
        continue;
      }
      
      $count = mysql_count("news", "YEAR(createdDate) = $year AND MONTH(createdDate) = $month");
      $dateTitle = date('F Y', $beginningOfMonthTime);
      ?>
        <a href="newsList.php?createdDate_keyword=<?php echo $year . '-' . $monthPadded;?>"><?php echo $dateTitle ?> (<?php echo $count;?> posts)</a><br/>
      <?php
    }
  }
?>

Please let me know if you have any questions.

Hi Hiroko, 

I've found a fix for the problem, here is some updated code:

<?php
  $earliestRecord = mysql_get('news', null, 'TRUE ORDER BY createdDate ASC');
  $earliestYear   = date('Y', strtotime($earliestRecord['createdDate']));
?>
<?php
  foreach (range($earliestYear, date('Y')) as $year) {
    foreach (range(12, 1) as $month) {
      
      $monthPadded = str_pad($month, 2, 0, STR_PAD_LEFT); // zero padding for month number
      $beginningOfMonthTime = strtotime("$year-$monthPadded-01");
      
      // skip months in the future
      if ($beginningOfMonthTime > time()) {
        continue;
      }
      
      $count = mysql_count("news", "YEAR(createdDate) = $year AND MONTH(createdDate) = $month");
      $dateTitle = date('F Y', $beginningOfMonthTime);
      ?>
        <a href="newsList.php?createdDate_keyword=<?php echo $year . '-' . $monthPadded;?>"><?php echo $dateTitle ?> (<?php echo $count;?> posts)</a><br/>
      <?php
    }
  }
?>

I've highlighted my change in green above. So I've reordered the range to go from 12 to 1 instead of 1 to 12.  Range generates an array of numbers, with each number being used as the month value, with 12 being December, and 1 being January.

Let me know if this doesn't fix the issue.

Cheers,

Greg

You may also want to swap the year order:

foreach (range(date('Y'), $earliestYear) as $year) {