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 have a custom post type of 'events' which has a meta key 'event_date'

Im using the following to retrieve those posts and arrange them by 'event_date'

        global $wpdb;
        $today = date('Y/m/d', strtotime("now"));
        $events = $wpdb->get_results("SELECT 
                                            posts.id, posts.post_title, meta.meta_value 
                                        FROM 
                                            {$wpdb->prefix}postmeta as meta
                                        LEFT JOIN 
                                            {$wpdb->prefix}posts as posts 
                                        ON 
                                            (meta.post_id = posts.id) 
                                        WHERE
                                            (meta.meta_key = 'event_date' 
                                        AND
                                            meta.meta_value >= '{$today}'
                                        AND
                                            posts.post_status='publish' and posts.post_date < NOW())
                                        ORDER BY 
                                            meta.meta_value
                                    ");

The above works fine however i would like to show 'events' from only certain categories (say category id '5'). How can i modify the query to achive this?

Any help would be appreciated :)

share|improve this question
1  
Is there a reason why you don't use wp query it have the paramaters your asking for. –  Wyck Aug 10 '11 at 15:14
add comment

1 Answer

up vote 2 down vote accepted

First, you shouldn't use NOW() in select queries since NOW() returns the current date and time to the split second. In other words: This query will never land in the cache.

And you can use the WP_Query with filter of posts_where eg:

function filter_event_where( $where = '' ) {
    $date = date('Y-m-d'); 
    $where .= " AND posts.post_date < '".$date."'";
    return $where;
}

add_filter( 'posts_where', 'filter_event_where' );
$today = date('Y/m/d', strtotime("now"));
$query = new WP_Query( array(
    'meta_key' => 'event_date', //used for order
    'orderby' => 'meta_value', //used for order
    'post_type' => 'event',
    'meta_query' => array(
        array(
            'key' => 'event_date',
            'value' => $today,
            'compare' => '>='
        )
    )
    ));
remove_filter( 'posts_where', 'filter_event_where' );
share|improve this answer
    
This is brilliant, helped loads. Thankyou :) –  Rocket Ronnie Aug 11 '11 at 7:54
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.