Sorting a query by custom field date

Question

EDIT: Reworded the question for more clarity

I'm trying to sort by custom meta box data, which I am converting to a date using the strtotime function, but I'm having some difficulty.

My chief problem seems to be a catch-22 with the strtotime function. If I use strtotime inside the loop (see below) I cannot use the UNIX time to sort by date. However, if I process the strtotime upon save (in functions.php), I run into a bunch of problems with the meta box updating / not parsing if the user edits the post or re-saves.

Is there a simple solution to this? Here is the code... I just want to order these posts in ascending order by the custom date field (not the posted date).

EDIT: Fixed the array by adding a meta-key - solution still pending

<!-- The args -->
<?php
$args = array(
    "post_type" => "podcasts",
    "meta-key" => "_date",
    "orderby" => "meta_value",
    "order" => "ASC"
    );
?>

<!-- The Query -->
<?php $podcasts = new WP_Query( $args ); ?>

<!-- The Loop -->
<?php if ( $podcasts->have_posts() ) : while ( $podcasts->have_posts() ) : $podcasts->the_post(); ?>

<!-- Convert Date to Date Stamp -->
<?php $podcast_date = get_post_meta($post->ID, '_date', true);?>
<?php $fixed_date = strtotime($podcast_date); ?>

<!-- Content -->

EDIT: Added relevant meta-box code (below) from functions.php Note -- This code does not include the strtotime function anywhere because I'm currently using it on the page template. I could include it here, but I am not sure what to do to allow the user to edit posts / resave. Once the strtotime function runs upon save, wouldn't it change the meta-box date to something like 1327248252? If so, that won't parse the next time that post is saved, creating issues.

// Create podcast Meta boxes

function add_podcast_metaboxes() {
add_meta_box('podcast_info', 'podcast Information', 'podcast_info', 'podcasts',  'normal', 'high');
}

// podcast Meta Box

function podcast_info() {
global $post;

// Noncename needed to verify where the data originated
echo '<input id="podcastmeta_noncename" name="podcastmeta_noncename" type="hidden" value="' .     wp_create_nonce( plugin_basename(__FILE__) ) . '" />';

// Get the data if its already been entered
$week = get_post_meta($post->ID, '_week', true);
$date = get_post_meta($post->ID, '_date', true);
$description = get_post_meta($post->ID, '_description', true);

// Echo out the field
echo '<strong>Week</strong><br /><em>What week of the series is this podcast?</em>';
echo '<input class="widefat" name="_week" type="text" value="' . $week  . '" />';
echo '<strong>Date</strong><br /><em>Enter the Date the podcast was recorded</em>';
echo '<input class="widefat" name="_date" type="text" value="' . $date  . '" />';
echo '<strong>Description</strong><br /><em>Enter the text you would like to display as a description on the media archives list.</em>';
echo '<input class="widefat" name="_description" type="text" value="' . $description  . '" />';

}

// Save the podcast Meta box Data

function save_podcast_meta($post_id, $post) {

// verify this came from the our screen and with proper authorization,
// because save_post can be triggered at other times
if ( !wp_verify_nonce( $_POST['podcastmeta_noncename'], plugin_basename(__FILE__) )) {
return $post->ID;
}

// Is the user allowed to edit the post or page?
if ( !current_user_can( 'edit_post', $post->ID ))
    return $post->ID;

// OK, we're authenticated: we need to find and save the data
// We'll put it into an array to make it easier to loop though.
$podcast_meta['_week'] = $_POST['_week']; 
$podcast_meta['_date'] = $_POST['_date'];
$podcast_meta['_thumbnail'] = $_POST['_thumbnail'];
$podcast_meta['_seriesimg'] = $_POST['_seriesimg'];
$podcast_meta['_description'] = $_POST['_description'];

// Add values of $podcast_meta as custom fields

foreach ($podcast_meta as $key => $value) { // Cycle through the $podcast_meta array!
    if( $post->post_type == 'revision' ) return; // Don't store custom data twice
    $value = implode(',', (array)$value); // If $value is an array, make it a CSV    (unlikely)
    if(get_post_meta($post->ID, $key, FALSE)) { // If the custom field already has a value
        update_post_meta($post->ID, $key, $value);
    } else { // If the custom field doesn't have a value
        add_post_meta($post->ID, $key, $value);
    }
    if(!$value) delete_post_meta($post->ID, $key); // Delete if blank
}

}
add_action('save_post', 'save_podcast_meta', 1, 2); // save the custom fields

Answer 1

EDIT: Your problem is this:

When you save your meta data, you want the date to be saved as a strtotime() date, but you want it to display the date back in the old Y-m-d format. What you need to do is save it as strtotime() and then when displaying the text back in the input, you need to reverse that strtotime()so it can be displayed properly. You can do it like so:

In your add_podcasts_metaboxes() function, change the line that gets the date to this (assuming the date would always be a UNIX timestamp if there is one set):

$date = get_post_meta( $post->ID, '_date', true );
if( $date != '' )
    $date = date( 'Y-m-d', $date );

And when saving the meta in save_podcast_meta(), do it like you want with strtotime():

$podcast_meta['_date'] = strtotime( $_POST['_date'] );

This would turn the date into a UNIX timestamp in the database, but with the date() function, you can turn that UNIX timestamp back into a date you can use.

---

You need to specify a meta key, then orderby meta_value like so:

<!-- The args -->
<?php
$args = array(
    "post_type" => "podcasts",
    "meta_key" => "some_meta_key", // Change to the meta key you want to sort by
    "orderby" => "meta_value_num", // This stays as 'meta_value' or 'meta_value_num' (str sorting or numeric sorting)
    "order" => "ASC"
    );
?>

<!-- The Query -->
<?php $podcasts = new WP_Query( $args ); ?>

<!-- The Loop -->
<?php if ( $podcasts->have_posts() ) : while ( $podcasts->have_posts() ) : $podcasts->the_post(); ?>

<!-- Convert Date to Date Stamp -->
<?php $podcast_date = get_post_meta($post->ID, '_date', true);?>
<?php $fixed_date = strtotime($podcast_date); ?>

<!-- Content -->

See this page of the codex for reference: http://codex.wordpress.org/Class_Reference/WP_Query#Order_.26_Orderby_Parameters

EDIT: I see one (possible) problem with your meta box code that you just added. Not 100% sure though!

Try replacing:

echo '<input id="podcastmeta_noncename" name="podcastmeta_noncename" type="hidden" value="' .     wp_create_nonce( plugin_basename(__FILE__) ) . '" />';

with:

wp_nonce_field( plugin_basename( __FILE__ ), 'podcastmeta_noncename' );

I am not sure if this will solve your problem or makes much of a difference, but that's how I do use nonce in my meta boxes (although I believe there is another way to do it to where it will get rid of that Notice if you have WP_DEBUG set to true).

Answer 2

The answers above are great. Another useful tactic to keep in mind is that if you already have an array of data you can sort server-side (vs. database-side) using PHP's usort, uasort and uksort.

To do that, you pass the array as the first argument and as it's second give the method a name of a function that is used as a callback later on. You just need to have that function return 1, 0 or -1 to sort by any custom recipe.

Answer 3

You are facing a similar problem I have, and I was trying to work through / manipulate your offered solutions with no results.

I ended up using http://tablesorter.com/docs and querying my meta-box data directly into the table columns. This worked perfectly and also automatically sorts dates appropriately.

Hope this helps...

Answer 4

I realize I should put this in my original post - but it's getting very crowded, and this lends itself toward an answer.

I'm looking for something like this:

http://www.designhammer.com/blog/sorting-events-date-using-wordpress-custom-post-types

However, I'm not quite at the php level where I can take what he's doing and apply it to my situation. It seems like he is adding some logic in functions.php where the strtotime function will only fire if the time is in a good format. That would solve my problem and allow me to sort on the front end.

Thoughts?