List of days with events that occur on that day

Question

I have a table of events, each of which has a start date and an end date.

I need to display a list of days and show which events are occurring on each day.

E.g. Say I have on event that runs mon-wed and another that runs tues-thurs I'm trying to create a table like so:

Mon: Event 1
Tue: Event 1, Event 2
Wed: Event 1, Event 2
Thu: Event 2

The only way I can see to do this is either by running a query for each day or by loading all events for given date range and then duplicating them in my code if they last for more than one day. Both approaches seem hacky and I'm sure I'm missing something.

Is there a neater way to do this?

Structure:

id         INT
name       VARCHAR
start_date DATE
end_date   DATE

Current query (slightly simplified because this database structure is appalling):

SELECT *
FROM events
WHERE start_date <= $somedate
AND end_date >= $somedate

...where $somedate is a given date. (Yes, I am escaping it properly in my code!)

Please, provide some data with structure – Alma Do Aug 29 '13 at 13:47 — Alma Do, Aug 29 '13 at 13:47

score 1 · Answer 1 · edited May 23 '17 at 11:49

First you'll propably have to get all dates between start_date and end_date of each event. Put the in a temp. table like:

|Weekday| Event_name|
|Mo     | Event1    |
|Tu     | Event1    |
|We     | Event1    |
|We     | Event2    |
|Th     | Event2    |
|Fr     | Event2    |

and then group_concat the events by group by on weekday.

This link will help you to get the dates between start and end date of each event.: Get a list of dates between two dates

score 1 · Answer 2 · answered Aug 29 '13 at 13:58

1

There's probably a better way, but off the top of my head I would say to create a table of possible dates, then simply

select * from events
 inner join possibleDates on possibleDates.Date >= start_date and possibleDates.Date <= end_date

answered Aug 29 '13 at 13:58

Kevin

7,162
11
46
70

score 1 · Accepted Answer · answered Aug 29 '13 at 14:04

Select your events for a particular date range (I assume a particular month). Then loop over the days in that month and have another, inner loop that displays any events that occur on that date.

<?php
$events = getEvents(); // some function that returns events from database

echo '<ol class="calendar">';
for ($i = 1; $i <= cal_days_in_month(); $i++) {
    echo '<li>';

    $beginning = mktime(0, 0, 0, date('n'), $i, date('Y'));
    $end = mktime(23, 59, 59, date('n'), $i, date('Y'));
    $events_on_day = array();

    foreach ($events as $event) {
        if ($event->start_date <= $end && $event->end_date >= $beginning) {
            $events_on_day[] = $event;
        }
    }

    if ($events_on_day > 0) {
        echo '<ul>';
        foreach ($events_on_day as $event_on_day) {
            echo '<li>' . $event_on_day->name . '</li>';
        }
        echo '</ul>';
    }
}
echo '</ol>';
?>

I think it's probably more efficient to do this directly in SQL but I ended up using this method due to time constraints and the inflexibility of the existing system. Thanks! — Mr_Chimp, Sep 04 '13 at 18:40

score 1 · Answer 4 · answered Aug 29 '13 at 15:37

You can do it in one query if you must, though I would advise getting a weekday-table like Jan Zeiseweis also suggests. Working SQLfiddle: http://www.sqlfiddle.com/#!2/e618d9/1/0

SELECT wk.day, GROUP_CONCAT(e.name, '') events
FROM (
  SELECT 'Monday' day, 0 dayIndex
    UNION
  SELECT 'Tuesday' day, 1 dayIndex
    UNION
  SELECT 'Wednesday' day, 2 dayIndex
    UNION
  SELECT 'Thursday' day, 3 dayIndex
    UNION
  SELECT 'Friday' day, 4 dayIndex
    UNION
  SELECT 'Saturday' day, 5 dayIndex
    UNION
  SELECT 'Sunday' day, 6 dayIndex
) wk
LEFT JOIN Events e
   ON WEEKDAY(e.start) <= wk.dayIndex
  AND WEEKDAY(e.end) >= wk.dayIndex
GROUP BY wk.day ORDER BY wk.dayIndex

List of days with events that occur on that day

4 Answers4