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've created a site made just with custom fields about famous people in sport.

In the post page I've a born date and a death date field using the data picker.

What I'm trying to achieve is to show posts of people who have died today in the past, if someone has died the 14th of february 1982 I want to show in a page/widget every 14th of february of this year and the next to come.

I'd also would like to retrieve from the born and death date only the year.

Let's take Jesse Owens. He was born on September 12, 1913 and died March 31, 1980.

I would like to have a post like this: Jesse Owens (1913 – 1980)

  • Jesse Owens is the title of the post but how to show 1913 and 1980? Thanks!
share|improve this question

1 Answer 1

up vote 0 down vote accepted

It's a little complicated, but we can do this by directly modifying the query with a filter on posts_where.

We'll start with a query for posts with a meta value of today's date, this is assuming a date format of yyyy-mm-dd:

$date_today = date('Y-m-d');
$args = array(
    'posts_per_page' => -1,
    'meta_query' => array(
        array(
            'key' => 'died_date',
            'value' => $date_today,
            'compare' => '=',
            'type' => 'DATE'
        )
    )
);

$results = new WP_Query( $args );

If we look at the WHERE clause of this query, we'll see the relevant bit:

AND ( (wp_postmeta.meta_key = 'died_date'
AND CAST(wp_postmeta.meta_value AS DATE) = '2013-02-14') )

To get this to work, we want to modify the last line to use MySQL's DAYOFYEAR in the comparison.

So here's our filter:

function wpa86916_filter_where( $where ){
    global $wpdb;
    $date_today = date('Y-m-d');
    $parts = explode( 'AND CAST', $where );
    $new_where = " AND DAYOFYEAR( CAST( $wpdb->postmeta.meta_value AS DATE ) ) = DAYOFYEAR( '$date_today' ) ) )";
    $where = $parts[0] . $new_where;
    remove_filter( 'posts_where', __FUNCTION__ );
    return $where;
}

If we back up to our original query, we can now add this filter before the query, and we should get all posts with matching day of year, regardless of year:

add_filter( 'posts_where', 'wpa86916_filter_where' );
$results = new WP_Query( $args );

As for your question regarding extraction of the year from a date, we can use PHP's date and strtotime to do any date extraction and conversion:

$date = '2013-02-14';
echo date( 'Y', strtotime( $date ) );
// prints '2013'
share|improve this answer
    
Milo, thanks for your time... I hoped it could be a little easier and I confess it's too hard for my skills. Fromn what I've understood (my english is pretty bad) I should need to work on mySQL but I don't feel like messing with it after all the efforts made to create it. I'm much more like adding a code in the functions and a php code in the page where I want to show the people who have died today in the past. If I'm not wrong your code requires to modify the database as I said, in case I misunderstood your instructions let me know... –  user27309 Feb 15 '13 at 16:59
    
the filter function would go in your theme's functions.php, it uses WordPress hooks to modify the query WordPress sends to the database, it doesn't involve touching anything in the database. the add_filter and query go in your template file where you want to output the posts. it should all work as-is as long as the format of your dates in the database are yyyy-mm-dd, and you change died_date to whatever you've named the died date field. –  Milo Feb 15 '13 at 17:12

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.