List events (filtered to next seven days and grouped by day)

PHP
1

Right, let’s see if anyone can help with this…

I have a list of events, which are either single day events or multiple day events. If they are a single day they simply have a startDate field. If they are a multiple day event they have a startDate and an endDate.

What I want is just to show the next seven days events, but split the events into each day. So, there would be a header for Monday and then a list of Monday’s events, and so on. If the event lasted all week it would show under each day.

Okay, now I have this short piece of code that works out the next seven days and their headers. How would I begin to incoporate the events into this loop? Any help would be massively appreciated!

<?php
$date = time();
for($i=0; $i<7; $i++) {
   echo date('D d', strtotime("+$i days", $date)) . "<br />";
}
?>

There are a few different fields in the events table but the important ones are:

eventName
startDate (formatted like this: 2011-06-06 21:00:00)
endDate (as above)

2

please try this query and let me know how it works

it’s a single query that returns all events in the next 7 days (including today)

note that you will be looping over the query results to print the events, rather than looping to query them

SELECT d.eventdate
     , e.eventname
  FROM ( SELECT CURRENT_DATE + INTERVAL numbers.n DAY AS eventdate
           FROM ( SELECT 0 AS n
                  UNION ALL
                  SELECT 1
                  UNION ALL
                  SELECT 2
                  UNION ALL
                  SELECT 3
                  UNION ALL
                  SELECT 4
                  UNION ALL
                  SELECT 5
                  UNION ALL
                  SELECT 6 ) AS numbers
       ) AS d
LEFT OUTER
  JOIN events AS e
    ON d.eventdate BETWEEN e.startDate 
                       AND COALESCE(e.startDate,e.endDate)
ORDER
    BY d.eventdate
3

Cheers r937!

However, that code just outputted this:

=>
=>
=>
=>
=>
=>
=>

I’ve been thinking a little more about this, and this is basically what I am trying to achieve, using this as my test data:

However, thinking about this, and using this as my test data,

eventName startDate endDate ID
Event 1 2011-06-10 21:00:00 NULL 1
Event 2 2011-06-08 21:00:00 2011-06-09 21:00:00 2
Event 3 2011-06-13 21:00:00 NULL 3
Event 4 2011-06-06 17:00:00 2011-06-15 17:00:00 4

I am intending to output this:

06/08/2011 17:00:00=> Event 4
06/08/2011 21:00:00=> Event 2
06/09/2011 17:00:00=> Event 4
06/09/2011 21:00:00=> Event 2
06/10/2011 17:00:00=> Event 4
06/10/2011 21:00:00=> Event 1
06/11/2011 17:00:00=> Event 4
06/12/2011 17:00:00=> Event 4
06/13/2011 21:00:00=> Event 3
06/13/2011 17:00:00=> Event 4

Does that make sense? This thing is definitely getting clearer in my head conceptually it’s just working out the code that’s a problem!

4

could you please try the query outside of php?

i’m pretty sure it produces more than a bunch of arrows

:cool: