sql查询重叠时间

Question

I have a table for class reservation name book. I also have a table that has room data which is called room.

I'm trying to make a condition where if the room is already booked for a specific time period such as 08:00 am till 09:00 am - you cant book the class. But if not then you can .

my sql is:

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')

If the sql returns a row then the user cant book the room .

Sometimes this sql works but sometimes it fails . Is it right what am I doing here ?

Answer 1

It often helps if you write out your query in a readable format:

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')

Now you can clearly see you use only one table, namely book but two subqueries. That's seems a bit much and you're not using BETWEEN in the correct way. Why not query like this:

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

I should, of course, warn about SQL-injection.