WordPress.org

Ready to get started?Download WordPress

Forums

get_posts sorting by event date using meta_compare (4 posts)

  1. chadrew
    Member
    Posted 2 years ago #

    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.

  2. chadrew
    Member
    Posted 2 years ago #

    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.

  3. psnepsts
    Member
    Posted 2 years ago #

    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?

  4. chadrew
    Member
    Posted 2 years ago #

    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.

Topic Closed

This topic has been closed to new replies.

About this Topic