Filter free day time slot using PHP Codeigniter And Mysql

Question

Please Help !!! I want free date time slots(data) on new result page in table format associated with the "Search" button so that it outputs the details after the search button has been clicked. Result Image

I know the length of the needed time slot (eg. 1 hour) - Image

An available time slot cannot overlap.

How can this be achieved? What would you think is the best way to approach this?

Process Steps

My DB Structure

Event_ID | dt_start          | dt_end            
+----------+---------------------+---------------------+
|        3 | 2013-09-21 16:00:00 | 2013-09-21 16:30:00 |
|       21 | 2013-09-21 09:00:00 | 2013-09-21 09:15:00 |
|        5 | 2013-09-21 09:15:00 | 2013-09-21 10:15:00 | 
|       64 | 2013-09-21 15:00:00 | 2013-09-21 15:45:00 |     
|       32 | 2013-09-21 10:15:00 | 2013-09-21 11:30:00 | 
|        6 | 2013-09-21 13:00:00 | 2013-09-21 14:45:00 |  
+----------+---------------------+---------------------+

Already thanks for the help,

There are some useful insights [here](https://dba.stackexchange.com/questions/169429/mysql-query-to-fill-in-missing-datetime-values) and [here](https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) — Claudio Busatto, Mar 19 '19 at 06:38
basically we can do with Spliting time slots between start time and end time in PHP ?? — Ritesh shirsat, Mar 19 '19 at 07:04
The basic idea is that you create an array which tracks if a time (in minutes) is free or not. After that, you need to traverse this array checking for 60 (1 hour, from your description) free spaces. I will wrote an answer using PHP and SQL, which maybe can help you — Claudio Busatto, Mar 26 '19 at 17:53

Claudio Busatto · Answer 1 · 2019-03-27T11:18:03.903

This answer was based on the ideas from here and here.

The idea of the algorithm is the following:

We create an array where the indexes are all the possible minutes for a date and the values are true.
We fill all the minutes which are included on the existing events to false.
Finally, we look for consecutive time intervals of size $length (in minutes) and once we can traverse the array without finding a false value for $length iterations, we found an empty timeslot for a new event.

<?php
require("database.php");

// Create an array with all the timeslots (in minutes) of a date
// Example: $date = '2018-03-21'
//          $timeslots['2018-03-21 00:00:00'] = true;
//          $timeslots['2018-03-21 00:01:00'] = true;
//          $timeslots['2018-03-21 00:02:00'] = true;
//                            until
//          $timeslots['2018-03-21 23:59:00'] = true;
function getAllTimeslots($date)
{
    $currentDate = strtotime($date . " 00:00:00");
    $endDate = strtotime($date . " 23:59:59");

    $timeslots = [];
    while ($currentDate <= $endDate) {
        $index = date("Y-m-d H:i:s", $currentDate);
        $timeslots[$index] = true;
        $currentDate = strtotime("+1 minute", $currentDate);
    }
    return $timeslots;
}


// Based on the events table registers, fill the intervals of the timeslots array marking them as false, i.e., not available
// Therefore, it will result on an array with trues and falses marking if the specific minute is already filled by an event
function fillUsedTimeslots($date, $timeslots)
{
    $queryDate = $date . "%";
    $conn = openConnection();
    $result = $conn->query("SELECT dt_start, dt_end
                            FROM events
                            WHERE dt_start LIKE '" . mysqli_real_escape_string($conn, $queryDate) . "'
                                OR dt_end LIKE '" . mysqli_real_escape_string($conn, $queryDate) . "'");

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $start = strtotime($row['dt_start']);
            $end = strtotime($row['dt_end']);

            // update the timeslots data structure with the information of the events' time usage
            while ($start < $end) {
                $currentDatetime = date("Y-m-d H:i:s", $start);
                $timeslots[$currentDatetime] = false;
                $start = strtotime("+1 minute", $start);
            }
        }
    }

    return $timeslots;
}

// Finally, we need to find a free an interval of time ($length) where we can place an event
// In short words, it iterates over the array of $timeslots looking for $length consecutives trues, i.e.,
// if $length=60, we iterate over the array looking for 60 times of consecutives falses
function findFreeTimeslots($date, $length, $timeslots)
{
    $currentDate = strtotime($date . " 00:00:00");
    $endDate = strtotime($date . " 23:59:00");

    $timeInterval = 0;
    while ($currentDate <= $endDate) {
        $index = date("Y-m-d H:i:s", $currentDate);

        if ($timeslots[$index]) { // Timeslot is free for use
            $timeInterval += 1;
        } else { // Reset timeInterval
            $timeInterval = 0;
        }

        // We have $length consecutives true, i.e., an interval of $length minutes available for another event
        if ($timeInterval == $length + 1) {
            echo "<br/>Timeslot found: " . date("Y-m-d H:i:s", strtotime("-" . $length . " minutes", $currentDate)) . " - " . $index;
            $timeInterval = 1;
        }
        $currentDate = strtotime("+1 minute", $currentDate);
    }
}

// Main
$timeslots = getAllTimeslots("2013-09-21");
$filledTimeslots = fillUsedTimeslots("2013-09-21", $timeslots);
findFreeTimeslots("2013-09-21", 180, $filledTimeslots);

I applied your example of 3 hours interval, and the result was:

Timeslot found: 2013-09-21 00:00:00 - 2013-09-21 03:00:00
Timeslot found: 2013-09-21 03:00:00 - 2013-09-21 06:00:00
Timeslot found: 2013-09-21 16:30:00 - 2013-09-21 19:30:00
Timeslot found: 2013-09-21 19:30:00 - 2013-09-21 22:30:00

You can restrict the timeslots data structure size to only working times, or a diferent criteria to adapt on your project. And also, refactor it, it's been a long time since I programmed vanilla PHP

Filter free day time slot using PHP Codeigniter And Mysql

1 Answers1