MySQL Forums
Forum List  »  PHP

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.

Options: ReplyQuote

Written By
Temporal query with potential sub-query question.
July 26, 2005 05:47PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.