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'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!

share|improve this question
    
The query that wp_get_archives() uses is 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 DESC $limit, have you tried something along those lines? –  m0r7if3r Jan 31 '12 at 13:48
    
That query would return publish date. The dates for the events is in a meta field and has no bearing on the publish date. The query I'm running returns the data perfectly. What to do with it after is my problem. –  Ryan Palmer Jan 31 '12 at 13:58
    
OK, so then you modify it...the core devs are pretty good at what they do, I find trying to mimic their style wherever possible, if only for consistency's sake will yield solid results most of the time. Something along the lines of SELECT YEAR(meta_value) as 'year', MONTH(meta_value) AS 'month'...etc, and apply your own WHERE seems to me like a solid way of approaching it. If you have not tried this method, I'll write it up as a solution for you. –  m0r7if3r Jan 31 '12 at 14:05
    
Oh that would be perfect! Many thanks m0r7if3r! –  Ryan Palmer Jan 31 '12 at 14:11

2 Answers 2

up vote 2 down vote accepted

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>";

 }
share|improve this answer
    
I have, but again, I'm not sure how to take the data and loop it into the listing like the example I posted. –  Ryan Palmer Jan 31 '12 at 13:56
1  
My revised example would loop your data and print out the dates in unordered lists separated by months. You prolly need to tweak to make it do exactly what you want. –  Shane Jan 31 '12 at 14:16
    
This works great Shane. It outputs the year for each month entry. How could I get it to show only one year and the months associated with that year? –  Ryan Palmer Jan 31 '12 at 22:45
    
Remove the 'else' clause. In the 'if', check to see if $year == the year you want. –  Shane Feb 1 '12 at 13:56
    
See my second example –  Shane Feb 1 '12 at 14:00
$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.

share|improve this answer
    
Thanks for the input here. With some tweaking, this query came out nicely. Still wasn't able to sort it into the example I listed above, which was what the client was looking for. However, did somewhat manage to get it into a "Month Year (count)" type of listing. –  Ryan Palmer Feb 1 '12 at 10:04
    
Was it because it wasn't a datetime object? I was afraid that might be the issue... –  m0r7if3r Feb 1 '12 at 18:45

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.