Page 1 of 1

complex date range query

Posted: Fri Jun 17, 2005 7:23 am
by penciLneck
Hi all, wonder if you could point me in the right direction on this.

At the moment i have a very simple listings query from one table: events

I'm just pulling out the data and theres a loop so events that occur on the same date are listed sequentially after the date heading.

code:

$q = "SELECT events.title,events.image,events.imagetype,events.text,DATE_FORMAT(events.date,'%d %M') as date FROM events where date >= CURDATE() ORDER BY events.date ";

$prevdate = "";

// before printing this info out, check to see if it's on the same date
if ($prevdate == $date) {
// then it's the same date and this is just a different instance.
echo....

// now, remember this event as the past event.
$prevdate = $date;

}


But what I really need to do is have a date range. So instead of:

jul 23
------
event data

event data

being output.

jul 23 - aug 3
--------------
event data

event data

is displayed. I know I need to add the from and to dates to each event in the backend. Thats not a problem, but how do I form the query in the page and echo it out appropriately.

Thanks,

pn.

Posted: Fri Jun 17, 2005 10:06 am
by timvw
Instead of keeping track of the previous date:


(1) keep track of previous period
(2) keep track of events that have started but haven't end.. (because you need to mention them again in the following period

(3) every period you start... you iterate over the events in (2).. and remove those that end in this period.

Posted: Fri Jun 17, 2005 10:27 am
by penciLneck
Thanks. Could you illustrate how that would work in code, so I can understand it.

sorry quite slow at these things. :)