How to check if a booking exists within the time frame?

Question

Im working on a meeting room booking system and i am having trouble figuring out if a room is taken within a set timeframe, i am using the current format for dates and have the type set to DATETIME in the table: YYYY-MM-DD HH:II:SS

This is my current code:

//Function to check if a booking exists:
function bookingExist($DateStart, $DateEnd, $RoomNo) {
    global $DB;

    $sql = "SELECT * FROM bookings WHERE date_start = :dateStart AND date_end = :dateEnd AND room_no = :roomNo";
    $Q = $DB->prepare($sql);
    $Q->bindParam(':dateStart', $DateStart);
    $Q->bindParam(':dateEnd', $DateEnd);
    $Q->bindParam(':roomNo', $RoomNo);
    $Q->execute();

    if ($Q->rowCount()) {
        //A record exists...
        return true;
    } else {
        //no record exists
        return false;
    }
}

Is there any way to do this in the sql query ?

example: Room A is booked between 2016-03-31 15:00:00 and 2016-03-31 17:30:00 User then tries to create a new booking for room A for 2016-03-31 16:30:00 - 2016-03-31 17:00:00 and thsi should return 1 record as there is a booking

Answer 1

SELECT *
FROM bookings
WHERE
  (
    :dateStart BETWEEN date_start AND date_end
    OR :dateEnd BETWEEN date_start AND date_end
    OR date_start BETWEEN :dateStart AND :dateEnd
  )
  AND room_no = :roomNo

This will give you zero rows, if the room is not booked in you given timespan :dateStart - :dateEnd. :dateStart BETWEEN date_start AND date_end will check if it's already booked at starttime, :dateEnd BETWEEN date_start AND date_end will check if its booked at the end time and the last one date_start BETWEEN :dateStart AND :dateEnd is also important, it checks if the room is book in between (shorter period for example)

EDIT: OR times and no AND ;)

EDIT2: To exclude the start and end times, you have to expand the BETWEEN keyword and make the relation only be a <:

SELECT *
FROM bookings
WHERE
  (
    (date_start < :dateStart AND :dateStart < date_end)
    OR (date_start < :dateEnd AND :dateEnd < date_end)
    OR (:dateStart < date_start AND date_start < :dateEnd)
  )
  AND room_no = :roomNo