0

I'm using the following code to list all years from my blog, I guess we can call it an archive.

<?php
$years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts ORDER BY post_date DESC");
foreach($years as $year) : ?>
    <?php echo $year ?>
<?php endforeach; ?>

What I'd like to achieve though is to also display, in front of each year, the number of posts (count) that each year has. Example:

2015 (5 posts)
2014 (3 posts)
2011 (10 posts)

I found a hint somewhere and tried to implement it, but didn't work. Here's what I was trying:

<?php
$years = $wpdb->get_col("SELECT DISTINCT YEAR(post_date) FROM $wpdb->posts ORDER BY post_date DESC");
$count = $years->post_count;
foreach($years as $year) : ?>
    <?php echo $year ?> (<?php echo $count ?> posts)
<?php endforeach; ?>

Any ideas?

Thank you.

EDIT @David

<?php 
/** Grab the years that contain published posts, and a count of how many */
$query = $wpdb->prepare('
            SELECT YEAR(%1$s.post_date) AS `year`, count(%1$s.ID) as `posts`
            FROM %1$s
            WHERE %1$s.post_type IN ("post")
            AND %1$s.post_status IN ("publish")
            GROUP BY YEAR(%1$s.post_date)
            ORDER BY %1$s.post_date DESC',
            $wpdb->posts
        );
$results = $wpdb->get_results($query);

/** Create the '$years' array, a list of all yearly archives */
$years = array();
if(!empty($results)) : foreach($results as $result) :

        $url = get_year_link($result->year);                            // The archive link URL for the current year
        $text = $result->year;                                          // The archive link text for the current year
        $count = $result->posts;                                        // The number of posts in the current year
        $years[] = get_archives_link($url, $text, 'html', '', $count);  // Create the archive link for the current year

    endforeach;
endif;
?>
<a href="<?php echo $url; ?>"><li><?php echo $text ?> <span class="lower">(<?php echo $count ?>)</span></li></a>
1
  • I'll post an answer later if no one else does, but you are on the right track. Your issue is that you are trying to count the posts from the $years query, which wont work.
    – David Gard
    Feb 20 '15 at 16:16
3

The Easy Way

This is by far and away the easiest approach to this, but unless you are willing to be a little bit flexible with how you present your archive it's not for you.

The easy solution is achieved using the wp_get_archives() function. Unfortunately though there is no filter available to manipulate the output, so you'll be left with a list like this. The count will not be part of the link -

2015 (5)
2014 (3)
2011 (10)

And here is the code -

$args = array(
    'type'              => 'yearly',
    'show_post_count'   => true
);
wp_get_archives($args);

The Hard Way

So if you decide the easy way isn't for you that only leave the hard way. Essentially we are replicating the functionality of wp_get_archives(), but you can format the output exactly as you wish.

The code below makes use of a custom query, not dissimilar from your own (except that we grab the count of posts as well as the years), as well as the functions get_year_link() and get_archives_link().

The output will leave you with a list formatted as you posted in the update to your question. The count will be part of the link -

2015 (5)
2014 (3)
2011 (10)

And here is the code -

/** Grab the years that contain published posts, and a count of how many */
$query = $wpdb->prepare('
            SELECT YEAR(%1$s.post_date) AS `year`, count(%1$s.ID) as `posts`
            FROM %1$s
            WHERE %1$s.post_type IN ("post")
            AND %1$s.post_status IN ("publish")
            GROUP BY YEAR(%1$s.post_date)
            ORDER BY %1$s.post_date',
            $wpdb->posts
        );
$results = $wpdb->get_results($query);      

/** Create the '$years' array, a list of all yearly archives */
$years = array();
if(!empty($results)) : foreach($results as $result) :

        $count = '<span class="lower">(' . $result->posts . ')</span>';      // The number of posts in the current year
        $url = get_year_link($result->year);                                    // The archive link URL for the current year
        $text = '<li>' . $result->year . ' ' . $count . '</li>';                // The archive link text for the current year
        $years[] = get_archives_link($url, $text, 'html');                      // Create the archive link for the current year

    endforeach;
endif;

/** Output the custom archive list */
echo join("\n", $years);

Edit

Based on your comment, I've amended my answer above slightly.

The reason that you were only able to output one year is because you were only explicity echoing the last year after the foreach loop. What the code does is add each year to the $years array, and then outputs all of them using the final line - echo join("\n", $years);

9
  • Thank you! :) I tried changing it a little bit but for some reason it's showing me only one year! Basically I'm not using the echo but instead creating the tag after the code. See my edit please. Feb 22 '15 at 10:59
  • Please see my update, hopefully this helps.
    – David Gard
    Feb 23 '15 at 9:29
  • It's giving me blank li's between li's with years and the url is wrong (it's outputting /date/2011 for example) but I have a custom permalink for dates :\ that's why I was creating a custom output with php tags. Is there a way I can do it like this? Also, I need to wrap the li's with <a> that is why using get_archives_link won't work. Feb 23 '15 at 18:06
  • Try now, I'd added an extra </li> by mistake. And fine, change the output as required. The important thing to remeber is that, as you loop, $result->year is the year and $result->count is the number of posts.
    – David Gard
    Feb 23 '15 at 18:55
  • The problem is that get_archives_link will always output an <a> to the second parameter, which is the year :x Feb 23 '15 at 19:20
2

Just had simmilar problem and because I don't feel comfortable with performing DB queries in rendered php sites, I made this function, witch uses wp_get_archives() and regex. It returns array with year as the key and number of posts as the value.

The middle way:

function load_years() {
  $args = array(
    'type'              => 'yearly',
    'show_post_count'   => true
  );
  ob_start();
  wp_get_archives($args);
  $item = ob_get_contents(); 
  ob_end_clean(); 
  $pattern = '/\'>(\d\d\d\d)<.*nbsp;\((\d*)/';
  preg_match_all($pattern, $item, $matches, PREG_OFFSET_CAPTURE);
  $years_array = array();
  foreach ($matches[1] as $key => $value) {
      $year = $matches[1][$key][0];
      $posts = $matches[2][$key][0];
      $years_array[$year] = $posts;
  }
  return $years_array;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.