php mysql how to check if 2 times overlap

Question

I have a booking system.

In the MySQL database, I have a bookings table, that contains fields for start time and end time for any given booking in the form of hh:mm:ss. The seconds are always 00.

From my booking system, I send the times through when a new booking is being made, also in the form of start time and end time in the form of hh:mm:ss.

I need to check if the NEW booking I am trying to make does not overlap a previous booking that is already in the database.

So to start with, I will have 2 variables that contain the start and end time that was sent through POST.

$startNewBooking and $endNewBooking

so my SQL would probably start like this

SELECT * FROM bookings WHERE starttime>=$startNewBooking AND .........

Then echo error if an overlapping time was found... i.o.w if the row count > 0.

Please help me with some syntax or logic for this.

Thank You

What about the day/s of booking ? You want to check only the time scales ? — mallix, Jan 24 '13 at 14:23
Are the starttime/endtime columns in the database a proper `DATETIME` or `TIME` type, or are they varchar types? — Michael Berkowski, Jan 24 '13 at 14:27
I think what you have asked is related to a existing one. [http://stackoverflow.com/questions/11098541/check-if-two-times-overlap][1] [1]: http://stackoverflow.com/questions/11098541/check-if-two-times-overlap — nTony, Jan 24 '13 at 14:27
[my own question](http://stackoverflow.com/questions/13765026/mysql-inserting-rows-into-a-database-with-datetime-sanity-checks) — Steve P, Jan 24 '13 at 14:29
The only way in which bookings can overlap is if b_start <= a_end AND b_end >= a_start — Strawberry, Jan 24 '13 at 14:31

score 2 · Accepted Answer · edited May 23 '17 at 12:12

This is my query, just edit the values as you need it

  $sql = "SELECT COUNT(*) as num FROM meeting_room_bookings
            WHERE
            (
            (startdate < start_at AND startdate > start_at)
            OR
            (startdate > start_at AND enddate < end_at)
            )
            AND
            meeting_room_id = whatever_id i was on about at the time :P";

if you are going to sanitise the data then you will need to replace the startdate and enddate values with "?" and do something like this->

 $sql = "SELECT COUNT(*) as num FROM meeting_room_bookings
            WHERE
            (
            (? < start_at AND ? > start_at)
            OR
            (? > start_at AND ? < end_at)
            )
            AND
            meeting_room_id = ?";

    $result = p4a_db::singleton()->fetchRow($sql, array($date, $date, $date, $dateend, $merono));

to go one further:

$sql = "SELECT COUNT(*) as num FROM meeting_room_bookings
                WHERE
                (
                ( start_at >= ? AND start_at <= ? )
                OR
                ( start_at <= ? AND end_at >= ? )
                OR
                ( end_at >= ? AND end_at <= ? )
                OR
                ( ? >= ? )
                )
                AND
                meeting_room_id = ?";

        $result = p4a_db::singleton()->fetchRow( $sql, array( $date, $dateend, $date, $dateend, $date, $dateend, $date, $dateend, $merono ) );

this method allows no dates to come within the fields outside the fields around the fields or any other possibility from that area (see diagram):

||       |-----------|      ||   first booking 
|-----|                          ok
        |---|                    fails
                  |----|         fails
             |----|               fails

if you need a full explanation go to This Topic

when you do the error messages you will need to also do if else so this syntax should do the job:

if ( 0 == $result["num"] )
        {
            p4a_db::singleton()->query( "INSERT INTO meeting_room_bookings ( start_at, end_at, meeting_room_id, tennant_id, centre_id )
                    VALUES
                    ( ?, ?, ?, ?, ? )", array( $date, $dateend, $merono, $row['tennant_id'], $centreRow['id'] ) );
            return true;
        }
        else
        {
            P4A::singleton()->messageError("The selected times conflict with another booking. Please select another time or meeting room!");
        }

please ignore the fields with the centre id and tennant id (unless you use these) they are there to ensure the booking is set to the right tennant and the right meeting room. use this as required and substitute what you need to.

btw i am using p4a application framework so you will need to replace P4A::singleton()->messageError with the php error message mobober

datetime and this isnt overly complecated this takes into account every possible value that can come between the dates or outside the dates... i will elaborate now — Steve P, Jan 24 '13 at 14:36
there fully updated and if you draw a diagram against both you will see why it needs so much care and attention as they are meeting rooms they need to take into account every possibily even those from within the fields and outside -> inside the booking, i am working on a project with the same system so i think i know what needs to be done. — Steve P, Jan 24 '13 at 14:40
OK, I'll repeat myself: b_start <= a_end AND b_end >= a_start — Strawberry, Jan 24 '13 at 14:46
oh and also if the a_end < a_start yours will fail plus if there are two different bookings one with b_start at 11:00 and b_start 10:00 and also b_end 11:30 and b_end 10:30 and you are trying to book at 09:00 to 12:00 that would succeed as the start date is before one start date and above a — Steve P, Jan 24 '13 at 15:19

php mysql how to check if 2 times overlap

1 Answers1