WordPress.org

1. chadrew
   Member
   Posted 8 months 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 8 months 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 8 months 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 8 months 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.

Reply

You must log in to post.