List upcoming events, ordered by date in a custom field

Question

I want to display a list of events with dates in upcoming order. Ex:

• 1. 23 March, 2013,
• 1. 30 March, 2013

Below is the query I'm using to filter out posts from my database:

query_posts( 
    'post_type=myevents&meta_key=_datepicker&orderby=meta_value&order=ASC&showposts=20'
);

But this doesn't work. I get my events listed in following order:

• 07 April, 2013,
• 18 April, 2013,
• 19 June, 2013,
• 20 October, 2013,
• 23 March 2013,
• 24 March 2013

Events happening in March are listed at last. There is no order at the start too.

I'm using a custom field to store event date and the date format is 23-Mar-2013, but I show the front end dates in the format 23 March 2013.

How can I get the correct order?

Answer 1

That is proper alpha-numerical order. If your dates are stored human-like as you have them posted in your question, that it is the best you get. It appears that that is the case. That is, if your dates are stored as "07-April-2013", "18 -April-2013", ect., in the database and you sort them, you are going to get that order. That is how alpha-numeric sorting works.

What you want is human-calendar order, which is neither numerical nor alphabetical, except in in one circumstance. If you store dates as YYYY-MM-DD, and sort numerically you will get an order matching human-calendar order. Another option is to store the dates in UNIX-time. I'd recommend UNIX time because date manipulation in PHP pretty much always means converting to UNIX time first anyway.

Your problem here is that you are storing data in a format that is always going to cause you trouble.

I can't really point you to the right code edits without seeing the code you already have.

Similar question, possibly a duplicate: order post my meta value m/d/y format with year as included value

Answer 2

At first, never ever store a date in the format you prefer. Store dates only in the format your database prefer. In case of MySQL the format is YYYY-MM-DD

Let's write a short function to convert a date to the MySQL-format:

function date2mysql( $date ) {

  $timestamp = strtotime( $date );

  return ( false === $timestamp ) ?
    false : date( 'Y-m-d', $timestamp );

}

This function will return a date formated with the MySQL-formaat. If the given date is not a valid date (e.g. '16. Hugo 2013'), the function returns false.

I expect you have a function to store your data in the database. This could look like this one:

function save_event( $data ) {
  $defaults = array(
    'date'  => '01. January 1970',
    'event' => 'Sample event'
  );

  $data = wp_parse_args( $data, $defaults );

  /*
   * some code that store your data in the database
   */
}

Before you store the data in the database, you have to convert the date to the right format:

[...]
  $data = wp_parse_args( $data, $defaults );
  $date = date2mysql( $data['date'] );

  // handle cases where the date could not be converted. here setup a default date
  if ( false === $date )
    $date = '1970-01-01';

  $data['date'] = $date;
[...]

If you query your database, the post will be now sorted in the right way but will be displayed wrong. You also need a back-conversion from MySQL-format to a human readable format. WordPress have a build in function for that:

function display_data() {

  $events = query_posts( 
    'post_type=myevents&meta_key=_datepicker&orderby=meta_value&order=ASC&showposts=20'
  );

  $dateformat = get_option( 'date_format' );

  echo '<table>';
  echo '<thead><th><td>Date</td><td>Event</td></th></thead>';
  echo '<tbody>';

  foreach ( $events as $single_event ) {
    printf(
      '<th><td>%s</td><td>%s</td></th>',
      mysql2date( $dateformat, $single_event['date'],
      $single_event['event']
    );
  }

  echo '</tbody></table>';
}

mysql2date() format the date with the given format-string (in this example the date format from the WP options).

Summary: Before storing a date in the database, convert it into a MySQL-format. Convert it back into another format only on displaying. Do not convert it before sorting the data or do something else with the data.