PHP / MySQL - 检查双人会议室预订

Question

I want to find rows for anytime during the new period I'm requesting.

Here is my table data which has existing rows:

https://www.dropbox.com/s/1s4y8tl6s09emim/Screenshot%202014-04-04%2009.52.24.png

Here is the new period I'm requesting:

Date: 2014-04-03 
Start Time: 12:30:00
End Time: 13:30:00

What is the correct SQL to find any existing rows for anytime during the new period I'm requesting?

I have this so far but not sure about the start_time and end_time fields.

$sql = 'SELECT * FROM int_low_events WHERE start_date = "2014-04-03"';

Answer 1

SELECT *, 
       ADDTIME(start_date, start_time) AS start,
       ADDTIME(end_date, end_time) AS end,
       '2014-04-03 14:00:00' AS proposed_start,
       '2014-04-03 16:05:00' AS proposed_end
  FROM int_low_events
HAVING (start BETWEEN proposed_start AND proposed_end OR end BETWEEN proposed_start AND proposed_end)
    OR (proposed_start BETWEEN start AND end OR proposed_end BETWEEN start AND end)

This is exactly the same as my previous (working) answer, but it is (in my opinion) a lot easier to understand. Also you now don't have to fill in the proposed start- and end-dates everywhere, but instead only once (... AS proposed_start and ... AS proposed_end).

This does work according to this SQLFiddle with your database design.

---

It needs to be so complicated, as it needs to fetch any event, that's running at the time of the new proposed event.

id | start_date | start_time | end_date   | end_time
---+------------+------------+------------+-----------
62 | 2014-04-03 | 12:00:00   | 2014-04-05 | 13:00:00
63 | 2014-04-03 | NULL       | 2014-04-04 | NULL
64 | 2014-04-03 | 16:00:00   | 2014-04-03 | 17:00:00
65 | 2014-04-03 | 16:30:00   | 2014-04-03 | 17:30:00
66 | 2014-04-03 | 21:30:00   | 2014-04-03 | 22:30:00

This is the part of your database that matters. Assume we have a new event, that has the following dates: 2014-04-03 16:00 - 2014-04-03 18:00. With my previous query, it only selected the IDs 64 and 65 (as both of these have either the start_datetime OR end_datetime within our new event).

However ID 62 does also span over this timeframe, but starts earlier AND ends later. With the new query, this will also be selected (if this makes any sense to you).