Create an archive listing based on meta array

Question

I've got an "event" custom post type, with a meta field is used for the event date. I've successfully created archive pages (year and month) based on the answer on a previous question (here). Now, I'm trying to create a listing of the archives, sort of like wp_get_archives would work. Example:

2012

• January
• February
• March
• etc

2011

• January
• February
• March
• etc

The query I'm running to gather up all the dates from the meta_key is this:

$query = $wpdb->get_col( "SELECT DISTINCT meta_value FROM $wpdb->postmeta WHERE meta_key = 'event_date' ORDER BY meta_value DESC" );

But I'm not sure how I can take that data and list it out like the above example. I know I can output the unique dates as is with a foreach loop. That, my output is as follows:

2012-07-15 2011-06-07 2011-01-10

Trying to get it all sorted in the list format, I'm completely unsure of. Any help with this one is greatly appreciated!

Answer 1

Have you looked at PHP's date function?

A quick example :

 $dates = $wpdb->get_col( /*your sql*/ );

 if( $dates )
 {
      $archive_year = "";

      foreach( $dates as $date )
      {
           $the_date = strtotime( $date );

           $year = date( 'Y', $the_date ); //4 digit year, ex 1999
           $month = date( 'F', $the_date ); //Full textual month, ex January

           if( $year != $archive_year  )
           {
                if( !empty( $archive_year ) ) echo "</ul>";

                echo "
                     <h2>$year</h2>
                     <ul>
                          <li>$month</li>
                ";
                
           }
           else
           {
                echo "<li>$month</li>";

           }

      }
 }

Second example

 $dates = $wpdb->get_col( /*your sql*/ );

 if( $dates )
 {
      $archive_year = date( 'Y' ); //Get current year

      echo "<h2>$archive_year</h2>";

      echo "<ul>";

      foreach( $dates as $date )
      {
           $the_date = strtotime( $date );

           $year = date( 'Y', $the_date ); //4 digit year, ex 1999
           $month = date( 'F', $the_date ); //Full textual month, ex January

           if( $year == $archive_year  )
           {
                

                echo "<li>$month</li>";
                
           }

      }
      
      echo "</ul>";
      
 }

Answer 2

$query = '
    SELECT
        YEAR(meta_value) as `year`,
        MONTH(meta_value) as `month`,
        COUNT(DISTINCT post_id) as `posts`
    FROM '.$wpdb->postmeta.'
    WHERE
        meta_key = "event_date"
    ORDER BY    meta_value DESC';
$results = $wpdb->get_results( $query );

That should be a pretty solid start, you may have to tweak it, and you might want to consider doing a JOIN to fetch the post_status, etc so that you're not putting up drafts and what not (though you could do that in PHP if you felt like it, either way works, I think SQL would probably be more efficient though). My only concern is that MySQL may not like returning a YEAR from a non-datetime type...and if that doesn't work you'll have to go with a PHP solution like @Shane's.