Temporal query with potential sub-query question.
Posted by:
Web Poser
Date: July 26, 2005 05:47PM
I have an events table which lists events by date. Within that table are a "name, event_date, event_end, recurring" fields pertinent to this question.
I need to be able to select events where event_date >= NOW().
NEXT, the query must look at the 'event_end' date and make sure it is only pulling information on or before the event_end date. event_end <= NOW().
Here's the trick, their is a recurring boolean field set to 1 for recurring or 0 for non-recurring. Recurring events occur every (specified day) up until the 'event_end' date.
The problem is I only want to show a certain amount of days, and a certain amount of events, and that pesky recurring field is throwing everything off because it won't display the recurring events due to the 'event_date' not being close enough to the temporal query.
Here are two queries that I have tried:
$query = "SELECT name, event_date, event_end FROM events WHERE event_date >= NOW() AND event_end <= DATE_ADD(NOW(), INTERVAL $numDaysEvents DAY) ORDER BY event_date LIMIT $numEvents";
-- this query works fine, but does not deal with the recurring events problem therefore does not display rows with recurring = 1.
Another try:
$last_day = strtotime("+7 day");
$last_date = date("Y-d-m", $last_day);
$query = mysql_query("SELECT * FROM events WHERE IF(recurring='1', (event_end >= NOW() AND event_date <= $last_date), (event_date >= NOW() AND event_end <= $last_date))");
--this query returns nothing.
If anyone can get this one right, I would really be impressed.
Thanks for any help.