Get list of months with posts

Question

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.

Answer 1

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); 
?>

Answer 2

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.