Recently updating wordpress and now I am trying to do accomplish something that is giving me a real headache. I was using an event calendar plugin, but now i am trying to remove that plugin and create my own events system. I'm getting hung up querying the posts by a date range.
I registered a custom post type and then used ACF (Advanced Custom Fields) to add two Date Picker fields. One field is the Event Start Date and the other is the Event End Date. Occasionally, an event will span for 3 months and it is these types of events that I am having the most problems with.
I have an area on my webpage that shows the events filtered by month and year. Let's call this my Event List Widget (it's not a wordpress widget). This is where I think things are getting tripped up because if I have an event that starts in May and ends in July and I am on the June 2014 section, that event won't show up.
I'm looking for a function/query - combo that will let me first filter by the month I am in, but be flexible enough to grab those events that span multiple months.
I've tried a few things, which I will list below. I've tested all of these queries and none of them seem to be able to treat my dates like a true date range.
It's suggested that maybe i need an additional field that I can update when the post is submitted to tell wordpress which months to show the event in. I would query that field instead of my date fields. I'm trying to avoid doing that because I'd like to make this system more legit.
Let me know if you all have some suggestions for me on what i can do.
Here is what i have already tried (if needed i can provide a link to the website via PM):
$all_events = array ( 'post_type' => 'events', 'posts_per_page' => 50, 'meta_query' => array( array( 'key' => 'event_start_date', 'compare' => '>=', 'value' => $startday, ), array( 'key' => 'event_end_date', 'compare' => '<=', 'value' => $endday, ) ), );
The above query almost works. It will display the events if the start and end date are within the same month, but I don't see the events that end in another month or two months from now.
$querystr = " SELECT * FROM $wpdb->posts wposts, $wpdb->postmeta metastart, $wpdb->postmeta metaend WHERE (wposts.ID = metastart.post_id AND wposts.ID = metaend.post_id) AND (metastart.meta_key = 'event_start_date' AND metastart.meta_value > $startday ) AND (metaend.meta_key = 'event_end_date' AND metaend.meta_value <= $endday ) AND wposts.post_type = 'events' AND wposts.post_status = 'publish' ORDER BY metastart.meta_value ASC ";
The above query does the same thing as the previous one. What do you think I am missing here?
NOTE: This was related to this post I made a few months ago. Now getting around to replacing the functionality.