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 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.
share|improve this question
    
If you can include some more details (such as code you're using or have tried, the specifics of what you're trying to do, and something that haven't worked), I suspect you'll get a much better answer. As it stands, it's hard to give you an specific-enough answer because it's not quite clear what you want. –  mrwweb May 17 '12 at 6:17
    
I ended using the query mentioned above, not quite sure if a better way exists –  thedev May 17 '12 at 14:46
add comment

3 Answers

up vote 0 down vote accepted

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;
share|improve this answer
add comment

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); 
?>
share|improve this answer
    
wp_get_archives returns a formated result, I only need the month and year value (01.2005, 02.2005 ...) –  thedev May 17 '12 at 14:45
    
@thedev Updated my answer with an example using query_posts() –  Michael Ecklund May 17 '12 at 17:43
1  
Thanks for your update, however I am not interested in the posts :) I am interested in the months that have posts. –  thedev May 17 '12 at 17:45
    
Do NOT user query_posts(). Micheal, please revisit that answer. :) –  kaiser Jun 21 '13 at 13:21
add comment

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.

share|improve this answer
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.