Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm looking to echo the number of posts per month since the blog began, and for months where there was no posts echo '0'.

This is the output I want:

January 1, February 3, March 8, April 3, ...

Any help would be great. Dave

share|improve this question
add comment

3 Answers

Your best bet is probably to use $wpdb directly. You can use COUNT and GROUP BY to make things easier.

A query might look something like this:

<?php
global $wpdb;
$res = $wpdb->get_results("SELECT MONTH(post_date) as post_month, count(ID) as post_count from {$wpdb->posts} WHERE post_status = 'publish' GROUP BY post_month", OBJECT_K);

That gets you most of the way there. Be sure to have a look at the generic results section of the wpdb docs.

To get you the rest of the way there, you'll likely want to loop through a range of 1-12, creating month names and checking to see if the results include that month.

Here's an example implemented as a shortcode:

<?php
add_action('init', 'wpse60859_register_shortcode');
/**
 * Registers the shortcode
 * 
 * @uses    add_shortcode
 */
function wpse60859_register_shortcode()
{
    add_shortcode(
        'posts_per_month',
        'wpse60859_shortcode_cb'
    );
}


/**
 * The shortcode callback function.
 *
 * Usage:
 *      [posts_per_month year="2012"]
 *
 * @uses    date_i18n
 * @uses    shortcode_atts
 */
function wpse60859_shortcode_cb($args)
{
    global $wpdb;

    $args = shortcode_atts(array(
        'year' => false
    ), $args);

    $year = absint($args['year']);

    // year is a no go?  bail.
    if(!$year)
        return '';

    $res = $wpdb->get_results($wpdb->prepare(
        "SELECT MONTH(post_date) AS post_month, count(ID) AS post_count from " .
        "{$wpdb->posts} WHERE post_status = 'publish' AND YEAR(post_date) = %d " .
        "GROUP BY post_month;", $year
    ), OBJECT_K);

    // We didn't get any results.  Something might be wrong?
    if(!$res)
        return '';

    // build the display
    $out = '<ul>';
    foreach(range(1, 12) as $m)
    {
        $month = date_i18n('F', mktime(0, 0, 0, $m, 1));
        $out .= sprintf(
            '<li>%s %d</li>',
            $month,
            isset($res[$m]) ? $res[$m]->post_count : 0
        );
    }
    $out .= '</ul>';

    return $out;
}

The foreach loop at the end is the one to pay attention to. Loop through a 1-12 range, create a proper month name for each, and see if the post count exists. If it does use that number, or else print 0.

That shortcode as a plugin.

EDIT Display counts for the last 12 months.

This one requires a bit more complex query, but the concept is the same: get post counts, group by month. This time, order by post date ascending. From there we just need to make an array of month numbers based on the current date.

Example (again as a shortcode)

<?php
add_action('init', 'wpse60859_register_shortcode');
/**
 * Registers the shortcode
 * 
 * @uses    add_shortcode
 */
function wpse60859_register_shortcode()
{
    add_shortcode(
        'posts_per_month_last',
        'wpse60859_shortcode_alt_cb'
    );
}

/**
 * Callback for displaying the last twelve months of posts
 *
 * @uses $wpdb
 */
function wpse60859_shortcode_alt_cb()
{
    global $wpdb;
    $res = $wpdb->get_results(
        "SELECT MONTH(post_date) as post_month, COUNT(ID) as post_count " .
        "FROM {$wpdb->posts} " .
        "WHERE post_date BETWEEN DATE_SUB(NOW(), INTERVAL 12 MONTH) AND NOW() " .
        "AND post_status = 'publish' " .
        "GROUP BY post_month ORDER BY post_date ASC", OBJECT_K
    );

    $cur = absint(date('n'));
    if($cur > 1)
    {
        $looper = array_merge(range($cur, 12), range(1, $cur-1));
    }
    else
    {
        $looper = range(1, 12);
    }

    $out = '<ul>';
    foreach($looper as $m)
    {
        $month = date_i18n('F', mktime(0, 0, 0, $m, 1));
        $out .= sprintf(
            '<li>%s %d</li>',
            $month,
            isset($res[$m]) ? $res[$m]->post_count : 0
        );
    }
    $out .= '</ul>';

    return $out;
}
share|improve this answer
    
This is getting closer I think, Chris. Thanks. Currently your code starts from Jan, and runs til Dec. Can it be modified to get the current month and go 12 (or X) months back? So July to June for example? –  davebowker Aug 5 '12 at 3:39
    
See my edit. I did for the last 12 months from the current month. It's just a matter of adding an additional where clause that includes the post date. –  chrisguitarguy Aug 5 '12 at 8:26
    
This is really close, thanks Chris. Just one bug I can see is that it ignores the counts of the latest month and sets them to 0. Here's the sample code, currently there are 2 posts in Feb, and 1 in January and it shows Feb as being 0, like its ignoring the latest month that has results. August 0 September 0 October 0 November 0 December 0 January 1 February 0 March 0 April 0 May 0 June 0 July 0 –  davebowker Aug 5 '12 at 14:35
1  
Also, I had to mod your wpdb query as your code was showing strange tallys in different months where there was no posts. I removed Object_k as turning that on and off changed the values. Here's what I ended up with: "SELECT MONTH(post_date) as post_month, COUNT( id ) as post_count ". "FROM $wpdb->posts WHERE post_status = 'publish' AND post_date BETWEEN DATE_SUB(NOW(), INTERVAL 12 MONTH) AND NOW() AND post_type = 'post' ". "GROUP BY post_month ORDER BY post_date DESC"); –  davebowker Aug 5 '12 at 14:38
    
Good call, forgot about the post_type thing. –  chrisguitarguy Aug 5 '12 at 16:34
show 1 more comment

So basically what you need to do is paste the following code in your theme’s sidebar.php file or any other file where you want to display custom WordPress archives.

<?php
global $wpdb;
$limit = 0;
$year_prev = null;
$months = $wpdb->get_results("SELECT DISTINCT MONTH( post_date ) AS month,YEAR( post_date ) AS year, COUNT( id ) as post_count FROM $wpdb->posts WHERE post_status = 'publish' and post_date <= now( ) and post_type = 'post' GROUP BY month , year ORDER BY post_date DESC");
foreach($months as $month) :
$year_current = $month->year;
if ($year_current != $year_prev){
if ($year_prev != null){
?>
<?php } ?>

<li class="archive-year"><a href="<?php bloginfo('url') ?>/<?php echo $month->year; ?>/"><?php echo $month->year; ?></a></li>


<?php } ?>
<li><a href="<?php bloginfo('url') ?>/<?php echo $month->year; ?>/<?php echo date("m", mktime(0, 0, 0, $month->month, 1, $month->year)) ?>"><span class="archive-month"><?php echo date("F", mktime(0, 0, 0, $month->month, 1, $month->year)) ?></span></a></li>
<?php $year_prev = $year_current;

if(++$limit >= 18) { break; }

endforeach; ?>

Note: If you want to change the number of months displayed, then you need to change line 19 where the current $limit value is set to 18.

Our CSS looked a bit like this:

.widget-archive{padding: 0 0 40px 0; float: left; width: 235px;}
.widget-archive ul {margin: 0;}
.widget-archive li {margin: 0; padding: 0;}
.widget-archive li a{ border-left: 1px solid #d6d7d7; padding: 5px 0 3px 10px; margin: 0 0 0 55px; display: block;}
li.archive-year{float: left; font-family: Helvetica, Arial, san-serif; padding: 5px 0 3px 10px; color:#ed1a1c;}
li.archive-year a{color:#ed1a1c; margin: 0; border: 0px; padding: 0;}

Now if you want to show the count of posts in each month, then you would need to add this bit of code anywhere in between line 12 – 16 of the above code:

<?php echo $month->post_count; ?>
share|improve this answer
    
That's great Varun. But on the example I'm using the last post was in February, I'd still like to have it print out 0 for each month up until now. Is that possible? –  davebowker Aug 5 '12 at 2:29
    
can you explain me clearly me with your code and a sample screen show pls so that i can assist you better –  S.Varun Aug 5 '12 at 3:58
    
Sure, I'll explain: Say my posts start 1 year ago. That's 12 months I want to show. There's 5 posts total, split over 3 months of Jan, Feb, May. 2 in Jan, 2 in Feb, 1 in May = 5. Your code spits out Jan 2, Feb 2, May 1. What I want is Jan 2, Feb 2, Mar 0, Apr 0, May 1, Jun 0, etc Hope that helps. Basically just your code that you have, printing out 0 for months with no posts. –  davebowker Aug 5 '12 at 4:08
    
hmm sorry i think that it cannot be done but any way i will try to help you –  S.Varun Aug 5 '12 at 4:30
    
Chris's solution above is similar, but still not 100% yet. Perhaps your code and Chris's could be combined someway to do what you have done, but count the 0 for months with no posts? –  davebowker Aug 5 '12 at 16:48
add comment

Thanks for your help, Chris and Varun. I seem to have done it by mostly using Chris's example, and taking a little code from Varuns too.

Here's what I ended up with. I'm not sure if it's the most efficient way of doing this, its more a proof of concept for me but if anyone has a way to do it any cleaner then please let me know.

Thanks all.

global $wpdb;
$res = $wpdb->get_results(
    "SELECT MONTH(post_date) as post_month, COUNT(ID) as post_count " .
    "FROM {$wpdb->posts} " .
    "WHERE post_date BETWEEN DATE_SUB(NOW(), INTERVAL 12 MONTH) AND NOW() AND post_type = 'post' " .
    "AND post_status = 'publish' " .
    "GROUP BY post_month ORDER BY post_date DESC", OBJECT_K
);

$postCount= 0;
$len = count($looper);

$cur = absint(date('n'));
if($cur > 1)
{
    $looper = array_merge(range($cur+1, 12), range(1, $cur));
}
else
{
    $looper = range(1, 12);
}

$out = '0,';
$postCount= '0';
$len = count($looper);
foreach($looper as $m)
{


    $month = date_i18n('F', mktime(0, 0, 0, $m, 1));

    $out .= sprintf(
        '%s %d',
        $month,
        //'',
        isset($res[$m]) ? $res[$m]->post_count : 0
    );
    if ($postCount!= $len-1) {
        $out .= ',';
    }

    $postCount++;

}
//$out .= '</ul>';

echo $out;
share|improve this answer
    
sorry for late that's good man i was trying to make for you but sorry i am unable to do so anyway you did smart work –  S.Varun Aug 6 '12 at 2:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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