I`ve been trying to make a dayplanner for a meeting room. I have two tables:
1) dayplanner: day split in 5 minute intervals Values=00:00:00,00:05:00,00:10:00 ... 23:55:00
2) reservations: name, start and finish Values=[john,12:00:00,12:30:00],[bob,14:00:00,14:45:00],etc
My goal is to use one query with two IF statements so i can check the starttime and what lies in between
Like this:
[Time] [reserved] [inbetween]
12:00:00 true true
12:05:00 true true
............................
12:35:00 false false
$query = "SELECT *,
IF(d.dayplanner_time = a.reservations_starttime,'true','false') AS reserved,
IF(d.dayplanner_time > a.reservations_start && d.dayplanner_time < a.reservations_finish,'true','false') AS inbetween
FROM dayplanner d
LEFT JOIN reservations a
ON d.day_time = a.reservations_start OR d.dayplanner_time = a.reservations_finish
WHERE d.dayplanner_time BETWEEN '12:00:00' AND '18:00:00';";
The query gives only one true in the reserverd column but nothing works. I hope you can help me.
You're matching your time intervals against reservations that either start or end exactly on that interval. By definition, if times are equal they can't be greater nor less than themselves, which means you're explicitly leaving out the reservations that don't match exactly that time interval.
The comparison should be
SELECT d.dayplanner_time,d.day_time,
IF(d.dayplanner_time = a.reservations_starttime,'true','false') AS reserved,
IF(a.reservations_start is null, false, true) AS inbetween
FROM dayplanner d
LEFT JOIN reservations a
ON (d.day_time >= a.reservations_start AND d.dayplanner_time <= a.reservations_finish)
WHERE d.dayplanner_time BETWEEN '12:00:00' AND '18:00:00';
which means "if there's at least one reservation starting before me, and ending before me, then there's something in between".
remember that when doing a LEFT JOIN, everything that is not null means they match your WHERE clause. That's why I only say in_between is false when reservations start is null.
It works fine Now to figure out how i can fill te spaces between