0

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 :)

1
  • 1
    Is there a reason why you don't use wp query it have the paramaters your asking for.
    – Wyck
    Aug 10, 2011 at 15:14

1 Answer 1

2

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' );
1
  • This is brilliant, helped loads. Thankyou :) Aug 11, 2011 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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