Combining timeslots for schedule into array - PHP

Question

I have a table with appointments:

$barber | $open    | $close   | $appointment_id  | $start    | $finish  |
1       |10:00:00  |17:00:00  | 1                | 11:30:00  | 12:30:00 |
1       |10:00:00  |17:00:00  | 2                | 14:30:00  | 15:30:00 |
2       |8:00:00   |16:00:00  | 3                | 12:00:00  | 13:00:00 |
2       |8:00:00   |16:00:00  | 4                | 15:00:00  | 16:00:00 |

Where open and close - working time, start and finish - timeslot for appointment. Assume there 2 workers in barbershop. I would like to define overlap in schedules as 'booked', else - 'empty'.

I would like to have this kind of array:

'open' => '08:00:00'  //defined by min in $open column
'close' => '17:00:00' //defined by max in $close column
'schedule' => Array ( 
    [08.00] => 'empty'
    [09.00] => 'empty'
    [10.00] => 'empty'
    [11.00] => 'empty'
    [12.00] => 'booked' //as appointments 1 and 3 overlap
    [13.00] => 'empty'
    [14.00] => 'empty'
    [15.00] => 'booked' //as appointments 2 and 4 overlap
    [16.00] => 'empty'  
)

My question - php loop, which builds required array from given data table.

Simplified version (just to show algo) what I'm trying to do:

for ($t = date('H',$open); $t < date('H',$close); $t++) {
   if (date('H',$start) < $t + 1 AND date('H',$finish) > $t AND $schedule_arr['schedule'][number_format($t,2)] != 'empty') {
    $schedule_arr['schedule'][number_format($t,2)] = 'booked';} 
   else {$schedule_arr['schedule'][number_format($t,2)] = 'empty';}
}

In the end most of the time I have array with 'empty' slots.

Answer 1

Here's how I might do this. First some things to note, you should probably be using strtotime() rather than date(). Consider the following, for me I get the second message displayed indicating the first date is not less than the second.

if(date("9:00:00") < date("10:00:00")) {
    echo "9:00:00 < 10:00:00 <br/>";
} else {
    echo "9:00:00 >= 10:00:00 ? <br/>";
}

You said you are using MySQL. This example uses SQLite because it is a fully working example. You will of course have to change how you connect to your database and what query you run.

Most importantly, consider how you check for overlap. As humans we think of an appointment from 12:00 to 13:00 does not overlap with an appointment from 13:00 to 14:00. But the time "13:00" appears in both ranges, so you might consider subtracting a second from your appointment end time or otherwise adjusting the functions used to determine overlap.

Anyway see if the following helps give you any ideas. This seems to work for me.

<?php

    class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db');
      }
    }
    $db = new MyDB();

    $results = $db->query(
        'SELECT barber, open, close, appointment_id, start, finish ' .
        'FROM   appointments' );

    //
    // Create structure with some defaults populated.
    //
    $output = array( 
                'open' => strtotime('23:59:59'),
                'close' => strtotime('0:00:00'),
                'schedule' => array(
                    strtotime('8:00:00')     => 'empty',
                    strtotime('9:00:00')     => 'empty',
                    strtotime('10:00:00')    => 'empty',
                    strtotime('11:00:00')    => 'empty',
                    strtotime('12:00:00')    => 'empty',
                    strtotime('13:00:00')    => 'empty',
                    strtotime('14:00:00')    => 'empty',
                    strtotime('15:00:00')    => 'empty',
                    strtotime('16:00:00')    => 'empty'
                ) );

    $barbers = array();

    echo 'INPUT' . '<br/>';

    while ($row = $results->fetchArray()) {

        //
        // Display input
        //
        echo    $row[0] . ' ' .
                $row[1] . ' ' .
                $row[2] . ' ' .
                $row[3] . ' ' .
                $row[4] . ' ' .
                $row[5] . ' ' .
                ' <br/>';

        $barber = $row[0];
        $open   = strtotime($row[1]);
        $close  = strtotime($row[2]);
        $start  = strtotime($row[4]);
        $finish = strtotime($row[5]);

        //
        // Handle setting open and close times.
        //
        if($open < $output['open']) {
            $output['open'] = $open;
        }
        if($close > $output['close']) {
            $output['close'] = $close;
        }

        //
        // Want to also ensure close > open???
        //

        if(!array_key_exists($barber, $barbers)) {
            $barbers[$barber] = array();
        }

        $range = array( 'start'     =>  $start,
                        'finish'    =>  $finish );

        //
        // For this range, check all existing ranges we have added so far.
        //
        checkOverlap($barbers, $barber, $range, $output);

        array_push($barbers[$barber], $range);
    }

    echo '<br/><br/><br/>';

    //
    // Display results
    //

    $open = date("H:i:s", $output['open']);
    $close = date("H:i:s", $output['close']);

    echo 'OUTPUT' . '<br/>';

    echo 'open: '   . $open   . '<br/>';
    echo 'close: '  . $close  . '<br/>';
    echo 'schedule: ' . '<br/>';
    foreach ($output['schedule'] as $key => $val) {
        $time = date("H:i:s", $key);
        echo '&nbsp;&nbsp;&nbsp;&nbsp;';
        echo $time . ' => ' . $val . '<br/>';
    }

    //
    // Helper function
    //
    function checkOverlap($barbers, $current_barber, $range, & $output) {
        $start  = $range['start'];
        $finish = $range['finish'];

        foreach($barbers as $barber => $appointments) {
            if($barber == $current_barber) {
                continue;   // Do not consider conflicts with self???
            }
            for($i = 0; $i < count($appointments); $i++) {
                $appointment = $appointments[$i];

                $aStart  = $appointment['start'];
                $aFinish = $appointment['finish'];

                if($aStart < $finish && $aFinish > $start) {

                    //
                    // Which one starts later?
                    //
                    $conflict_time = $start;
                    if($aStart > $start) {
                        $conflict_time = $aStart;
                    }

                    //
                    // Now find which hour to mark as conflict
                    //
                    foreach($output['schedule'] as $hour => $status) {

                        if( $conflict_time >= $hour &&
                            $conflict_time < ($hour + ((60 * 60) - 1)) ) {

                            $output['schedule'][$hour] = 'booked';
                        }
                    }
                }
            }
        }
    }

?>

I used the following to populate my sqlite3 db:

DROP TABLE IF EXISTS appointments;

CREATE TABLE appointments (
  barber integer,
  open  text,
  close text,
  appointment_id integer,
  start text,
  finish text
);

INSERT INTO appointments VALUES (
'1','10:00:00','17:00:00', '1','11:30:00','12:30:00' );
INSERT INTO appointments VALUES (
'1','10:00:00','17:00:00', '2','14:30:00','15:30:00' );
INSERT INTO appointments VALUES (
'2','8:00:00', '16:00:00', '3','12:00:00','13:00:00' );
INSERT INTO appointments VALUES (
'2','8:00:00', '16:00:00', '4','15:00:00','16:00:00' );

This is run from a linux command line with:

sqlite3 test.db <appointments.sql

EDIT further explanation of checkOverlap()

First this creates a structure called $barbers. This will use as key a barber ID, and for each barber ID, it will contain an array of ranges. We will add these ranges as we iterate over the table rows from our query. So this ends up looking something like:

$barbers => {
  1 => [ 
         { start: 1:00:00, finish: 1:30:00 },
         { start: 13:00:00, finish: 14:00:00 },
         { start: 15:00:00, finish: 16:45:00 } ],
  2 => [
         { start: 3:00:00, finish: 4:30:00 } ],
  3 => [ ... ],
  4 => [ ... ]
  ...
}

But prior to inserting a range into this structure first this calls checkOverlap() passing the structure itself and the range we are going to add. checkOverlap() walks through the structure, for each barber ID it gets the list of existing appointments, i.e. ranges, for that barber. It then checks if that existing appointment range $aStart and $aFinish overlaps with the new appointment range we want to add $start and $finish.

If there is an overlap, we take the later start time (since that will be the time the actual conflict begins) and then searches through the keys in $output['schedule'] to find which hour of the day that time falls under, and sets the value for that key to the value 'booked'. Note the $output object is passed by reference using & so that changes to this object will be reflected outside of the function. I.e. the caller's object will be updated.

Also note in the explanation of the structure above I am using human readable times like 1:00:00 but the actual structure will have UTC times since this is using strtotime().