I'm trying to build an event management system that uses post custom fields to hold event start dates and event end dates. I want to extract all events that take place during the current week. (where the start and/or end date fall during this week).
Here's the DB query I have to do this:
$query = "select post_id, meta_id, 'start' as meta_key, meta_value from wp_5_postmeta where meta_key='event_start_date' and '$weekstart' <= meta_value and meta_value <= '$weekend' union select post_id, meta_id, 'end' as meta_key, meta_value from wp_5_postmeta where meta_key='event_end_date' and '$weekstart' <= meta_value and meta_value <= '$weekend'";
Basically, I'd like the union of:
all posts where the value of "event_start_date" are between the first day of the week ($weekstart) and the end ($weekend)
all posts where the value of "event_end_date" are between the first day of the week ($weekstart) and the end ($weekend).
This query gives me the correct post_id's, but now I'd like to display these posts, and I'm not sure I want to get them one by one from the DB.
I'm wondering if it's possible to do a "between" query using query_posts (in which case I could just query twice to get the full list of posts to display, and then de-dupe that list before showing it), or if I can query on two different custom fields...
Sorry if this post is a little rambling, I'm just trying to think my way through this.