Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Any code and template file to think about? Also why do you need exactly query_posts not WP_query? –  Max Yudin Mar 16 '13 at 14:00
    
@MaxYudin please download the file from here filedropper.com/events –  adam Mar 16 '13 at 14:07
    
here's my page on test site bit.ly/159ZvmM events are not in date order –  adam Mar 16 '13 at 14:13
    
Please show your code here as it can be useful for future users. Please also paste here only essential parts of the code. We know nothing about filedropper.com and why we need to register there. –  Max Yudin Mar 16 '13 at 14:28
add comment

2 Answers

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

share|improve this answer
    
download my code from here filedropper.com/events –  adam Mar 16 '13 at 14:37
1  
I am not prone to downloading strange code, and that is not really how this site works. Questions and answers are supposed to be self-contained. –  s_ha_dum Mar 16 '13 at 14:42
    
Also Wordpress has built in functions to convert dates from Unix or MySql format to something more readable to people –  Manny Fleurmond Mar 17 '13 at 14:17
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.