Get list of months with posts

by

I am trying to add a filter on my custom theme based on post month, similar with the archives but with some differences.

What is the best way to get a list of months in witch we have posts?

Thanks,
Alex

I used the following query:

 $wpdb->get_results("SELECT DISTINCT YEAR(post_date) AS `year`, MONTH(post_date) AS   `month`, count(ID) as posts 
                     FROM $wpdb->posts 
                     WHERE post_type="post" AND post_status="publish" 
                     GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC"

Could not use the wp_get_archives function because it returns only the following formats:

html - In HTML list (<li>) tags and before and after strings. This is the default.
option - In select (<select>) or dropdown option (<option>) tags.
link - Within link (<link>) tags.
custom - Custom list using the before and after strings.

3 Answers
3

Core can’t help you here. You’ll have to do a…

Custom Query

Here’s a save query, that I use on admin UI screens to get the total amounts of month with posts to use them in the custom pagination. Pay attention that I query not only for the published posts, but take into consideration that there might be some restriction set and apply it then.

$post_status = esc_attr( $_GET['post_status'] );
$post_status = in_array( $post_status, $GLOBALS['avail_post_stati'] )
    ? " AND post_status = {$post_status}"
    : 'all'
;
'all' === $post_status AND $post_status="";

$total_page_dates = $wpdb->get_results( $wpdb->prepare( "
    SELECT
        YEAR( post_date )  AS year,
        MONTH( post_date ) AS month,
        count( ID )        AS posts
    FROM {$wpdb->posts}
    WHERE
        post_type = %s
        %s
    GROUP BY
        YEAR( post_date ),
        MONTH( post_date )
    ORDER BY post_date
    ASC
", get_current_screen()->post_type, $post_status ) );

Result

You’ll then be able to inspect your result

// Inspect the result
var_dump( $total_page_dates );

Which might look like the following:

array (size=4)
  0 => 
    object(stdClass)[1847]
      public 'year' => string '2013' (length=4)
      public 'month' => string '6' (length=1)
      public 'posts' => string '19' (length=2)
  1 => 
    object(stdClass)[1846]
      public 'year' => string '2013' (length=4)
      public 'month' => string '7' (length=1)
      public 'posts' => string '17' (length=2)
  2 => 
    object(stdClass)[1845]
      public 'year' => string '2013' (length=4)
      public 'month' => string '8' (length=1)
      public 'posts' => string '8' (length=1)
  3 => 
    object(stdClass)[1844]
      public 'year' => string '2013' (length=4)
      public 'month' => string '9' (length=1)
      public 'posts' => string '2' (length=1)

You are then able to loop through it or simply grab the first or last array item to get the range. Counting – count( $total_page_dates ) – would tell you how many month you got, etc. Keep in mind that every array value is an object, so you have to access it like this

$posts_in_first_month = total_page_dates[0]->posts;

Leave a Comment