complex date range query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

complex date range query

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

Post by penciLneck »

Thanks. Could you illustrate how that would work in code, so I can understand it.

sorry quite slow at these things. :)
Post Reply