get_posts sorting by event date using meta_compare

  • Here’s the situation – my blog is about upcoming, shall we say, events, and the date of the event is stored in a custom field in each post in the M d, Y format.

    What I am trying to do is make a custom page where these posts would be sorted by their custom field “event date” instead of post date. Also, I want to include only upcoming “events”. I am using get_posts for this purpose, like this:

    <?php
 $todaysDate = date('M d, Y');
 $postslist = get_posts('numberposts=-1&order=ASC&meta_key=final&meta_compare=>=&meta_value=' . $todaysDate . '&orderby=meta_value');
 foreach ($postslist as $post) :
 setup_postdata($post);
 ?>
 <li><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a><br />
Event date: <?php echo get_post_meta($post->ID, 'final', true); ?></li>
 <?php endforeach; ?>

    Everything’s fine, except it only gives me posts with dates from the current month.

    …As I was writing this post I realized that’s probably because of the date format I’m using (M d, Y, as in Sep 3, 2011).

    Is there a way to solve this besides manually changing all the custom fields to a new date format?

    And what date format should I use it would be able to compare event dates… Do I have to use Unix timestamp (like, 1315075580) or can it compare YYYY-MM-DD or whatever?

    Edit: another problem, meta_compare seems to think that day 9 is more than day 30, so the sorting is all messed up.

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter chadrew

    (@chadrew)

    It seems I managed to solve all these issues (whoa) so I’d like to leave this information for future generations.

    First of all, I needed to convert all “M j, Y” dates in the custom fields into timestamps. I ran the following code through WordPress:

    <?php
 $postslist = get_posts('numberposts=-1&order=ASC&meta_key=final');
 foreach ($postslist as $post) :
 $naujas = strtotime(get_post_meta($post->ID, 'final', true));
 update_post_meta($post->ID, 'final', $naujas);
endforeach; ?>

    …Because I don’t know MySQL ??

    Second, and I haven’t managed to find this in Codex anywhere, is that I’m supposed to use meta_value_num to sort instead of meta_value. This way WordPress won’t think that 9 is more than 30!

    Modified code:

    <?php
 $postslist = get_posts('numberposts=-1&order=ASC&meta_key=final&meta_compare=>=&meta_value=' . time() . '&orderby=meta_value_num');
 foreach ($postslist as $post) :
 setup_postdata($post);
 ?>
 <li><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a><br />
Event date: <?php echo date("F d, Y",get_post_meta($post->ID, 'final', true)); ?></li>
 <?php endforeach; ?>

    You’re welcome, future generations.

    Thanks for this. I’ve been looking for this functionality for over a week.

    Question:
    In what file do you place your code for converting the custom_field data to timestamps?

    Thread Starter chadrew

    (@chadrew)

    Hmm… Anywhere I guess, but make sure to only run it once. I think I put it on a custom page template, so site visitors wouldn’t end up running it.

    And from then on you actually have to enter the date as timestamp in your custom field, or I suppose you could code some smart function to automatically convert date to timestamp as your post is published.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘get_posts sorting by event date using meta_compare’ is closed to new replies.