php - sql查询重叠时间

Question

我有一个类(class)预订名称book的表格。我还有一个包含房间数据的表，称为 room。

我试图提出一个条件，如果房间已经在特定时间段(例如上午 08:00 至上午 09:00)被预订，则您无法预订类(class)。但如果没有，那么你也可以。

我的sql是:

SELECT * FROM book WHERE room_id = '$room_id' and start_date = '$date' and start_time = '$starttime' BETWEEN (SELECT MIN(start_time) AS mintime
FROM book WHERE room_id = '$room_id' and start_date = '$date') and (SELECT MAX(end_time) AS maxtime
FROM book WHERE room_id = '$room_id' and start_date = '$date')

如果 SQL 返回一行，则用户无法预订房间。

这个sql有时有效，但有时会失败。我在这里做什么对吗？

Answer 1

如果您以可读的格式写出查询，通常会有所帮助:

SELECT * 
FROM book 
WHERE room_id = '$room_id' AND
      start_date = '$date' AND
      start_time = '$starttime' 
BETWEEN (SELECT MIN(start_time) AS mintime
         FROM book 
         WHERE room_id = '$room_id' AND 
               start_date = '$date') AND
        (SELECT MAX(end_time) AS maxtime
         FROM book 
         WHERE room_id = '$room_id' AND
               start_date = '$date')

现在你可以清楚地看到你只使用了一个表，即book，但有两个子查询。这似乎有点多，而且您没有以正确的方式使用 BETWEEN 。为什么不这样查询:

SELECT * 
FROM book 
WHERE room_id = '$room_id' AND
      start_date = '$date' AND
      '$starttime' BETWEEN start_time AND end_time

我当然应该warn about SQL-injection .