0

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.

3
  • 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, 2013 at 17:34
  • thank you for the comment , could you give an example ? Mar 19, 2013 at 17:56
  • see answer below.
    – Milo
    Mar 19, 2013 at 18:03

1 Answer 1

0

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 );
2
  • 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 ) ? Mar 19, 2013 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, 2013 at 21:14

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.