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 would like to change the calendar so it will display some posts based on a custom date field. For now i am interested to modify this query:

$wpdb->get_results("SELECT ID, post_title, DAYOFMONTH(post_date) as dom "
    ."FROM $wpdb->posts "
    ."WHERE post_date >= '{$thisyear}-{$thismonth}-01 00:00:00' "
    ."AND post_date <= '{$thisyear}-{$thismonth}-{$last_day} 23:59:59' "
    ."AND post_type = 'post' AND post_status = 'publish'"
);

to something like this:

$wpdb->get_results("SELECT a.ID, a.post_title, DAYOFMONTH(b.meta_value) as dom "
    ."FROM $wpdb->posts a, $wpdb->postmeta b "
    ."WHERE b.meta_value >= '{$thisyear}-{$thismonth}-01 00:00:00' "
    ."AND b.meta_value <= '{$thisyear}-{$thismonth}-{$last_day} 23:59:59' "
    ."AND a.post_type = 'post' AND a.post_status = 'publish' AND a.ID = b.postid AND b.meta_key='Event Date'"
);

I did try the above query but it does not work, it returns an empty array. How can I change that the calendar will look after the meta value instead of post_date ? Thank you.

share|improve this question
    
You have to join the post meta table, but it's much simpler to do this via WP_Query's meta_query and a BETWEEN comparison, no need for a raw SQL query. –  Milo Mar 19 '13 at 17:34
    
thank you for the comment , could you give an example ? –  Gigi Becali Mar 19 '13 at 17:56
    
see answer below. –  Milo Mar 19 '13 at 18:03

1 Answer 1

Here's an example using WP_Query's meta_query to query posts between two meta key values:

$meta_query = array(
    array(
        'key' => 'event_date',
        'value' => array( $date_start, $date_end ),
        'compare' => 'BETWEEN'
    )
);

$args = array(
    'posts_per_page' => -1,
    'meta_key' => 'event_date',
    'orderby' => 'meta_value',
    'meta_query' => $meta_query
);

$results = new WP_Query( $args );
share|improve this answer
    
yeah .. it will not help me at all because as you can see i do not use the WP_Query object and how am i supposed to integrate all those conditions (compares ) ? –  Gigi Becali Mar 19 '13 at 18:32
    
perhaps you should read up on WP_Query in documentation and try it for yourself. You will get back an object containing your results, same as wpdb (in fact this ultimately uses wpdb under the hood). In this example $date_start is your '{$thisyear}-{$thismonth}-01 00:00:00' value and $date_end is '{$thisyear}-{$thismonth}-{$last_day} 23:59:59', the SQL is generated for you, including the comparison. status and post_type are set to those values by default. –  Milo Mar 19 '13 at 21:14

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.