Problem on MySQL Query on Between Operator with php

Question

I have MySQL table "room_booking" and this table contain two columns 'start_time' and 'end_time'. I want to check the time of the conference room booking. Suppose that if room is booked from 2:10 PM to 3:15 PM. Then another entry should not insert at between this time. My query is

$queryCheck = "SELECT start_time,end_time FROM room_booking AND (SELECT * FROM (room_booking) WHERE (start_time NOT BETWEEN '".$new_start_time."' AND '".$end_start_time."') AND (end_time NOT BETWEEN '".$new_start_time."' AND '".$end_start_time."'))";

Answer 1

This sounds to me to just be the overlapping range problem, and in this case you want to return records which do not overlap with the input start and end times. Try this query:

SELECT *
FROM room_booking
WHERE existing_end < ? OR existing_start > ?;

To the two ? placeholders above, you would bind, in order, $new_start_time and $end_start_time. So, you would end up with the following query:

SELECT *
FROM room_booking
WHERE existing_end > $new_start_time OR existing_start > $end_start_time;

Answer 2

You can use this one:-

INSERT INTO `room_booking` (`room`,`start_time`,`end_time`) 
SELECT 'A','2019-02-25 07:55:00','2019-02-25 08:00:00' FROM `room_booking`
WHERE NOT EXISTS
    (SELECT * FROM `room_booking` WHERE '2019-02-25 07:55:00' BETWEEN `start_time` AND `end_time`)

Let me know if you have doubt?

I am attaching screen shoot for an explanation.