Get list of months with posts

1 Views

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?

Read More

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.
Post Views: 1

Related posts

Leave a Reply

3 comments

  1. 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;

  2. Try wp_get_archives()

    Last Twelve Months

    Displays archive list by month, displaying only the last twelve.

    <?php wp_get_archives('type=monthly&limit=12'); ?>

    Last Fifteen Days

    Displays archive list by date, displaying only the last fifteen days.

    <?php wp_get_archives('type=daily&limit=15'); ?>

    Last Twenty Posts

    Displays archive list of the last twenty most recent posts listed by post title.

    <?php wp_get_archives('type=postbypost&limit=20&format=custom'); ?>

    For a non-formatted solution, consider using query_posts()

    <?php 
    $allPosts = query_posts('cat=6&monthnum=04&year=2011');
    print_r($allPosts); 
?>

  3. This is the query that wp_get_archive is internally using for month archives

    $query = "SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date $order $limit";

    and you are doing same thing, except that using distinct and group by together.