Booking Calendar - PHP MYSQL

[adzie]

Morning All,

I'm looking to build a simple booking calendar for a couple of resources, ideally only showing one day at a time so I can see the schedule for the day.

To set the day I started by converting say 0700 (25200) to seconds and end of the day to seconds 1900 (66600).  That is the id for the rows in 30 minutes intervals and then doing the same for the bookings and a booking takes up a number of rows based on how many 30 minute blocks (1800 seconds) it covers.

The issue is not being able to relate this to the resources at the top, of conversely if I did a horizontal calendar.

I'm looking for some guidance on the best way to present the calendar and how to look the column header to the rows, so the rows are generated by active resources in the database.

Resource table

resource_id	resource
1	Car - C43 GBT
2	Van - D76 GBT

Booking Table

booking_id	resource	start	end
1	1	datetime	datetime
2	2	datetime	datetime

As Always many thanks for your tips

[Barand]

I suggest output like this below. I have used yiur two resources and ny booking data looke like this...

mysql> select * from booking;
+----+-------------+---------------------+---------------------+
| id | resource_id | book_start          | book_end            |
+----+-------------+---------------------+---------------------+
|  1 |           1 | 2024-05-13 09:00:00 | 2024-05-13 12:00:00 |
|  2 |           1 | 2024-05-15 09:00:00 | 2024-05-15 19:00:00 |
|  4 |           1 | 2024-05-16 12:00:00 | 2024-05-16 18:00:00 |
|  5 |           2 | 2024-05-13 12:00:00 | 2024-05-17 18:00:00 |
+----+-------------+---------------------+---------------------+

The output looks lke this for the 2 resources...

      

CODE

<?php
require 'db_inc.php';                // 
$pdo = mdbConnect('db1');            // USE YOUR OWN CONNECTION CODE

$wkcomm = $_GET['wkcomm'] ?? date('Y-m-d');
$resid = $_GET['resid'] ?? '';

################################################################################
##  COLUMN HEADINGS
################################################################################
    $d1 = new DateTime($wkcomm);
    $di = new DateInterval('P1D');
    $dp = new DatePeriod($d1, $di, 6);
    $theads = "<tr class='w3-dark-gray'><th>Time Slot</th>";
    foreach ($dp as $d)  {
        $theads .= "<th>" . $d->format('D M d') . "</th>";
    }
    $theads .= "</tr>\n";

################################################################################
##  QUERY BOOKINGS AND BUILD ARRAY IN REQUIRED STRUCTURE FOR OUTPUT
################################################################################
    $res = $pdo->prepare("-- 
                          -- create temporary table containing the dates of the selected week
                          -- 
                            WITH RECURSIVE dateslot (n, slotdate) AS
                            (
                                SELECT 1, ?
                                UNION ALL
                                SELECT n+1
                                     , date_add(slotdate, INTERVAL 1 DAY)
                                FROM dateslot WHERE n < 7
                            ),
                          -- 
                          -- create temporary table containing the daily 30-minute timeslots
                          -- 
                            timeslot (n, starttime, endtime) AS
                            (
                                SELECT 1, '07:00:00', '07:30:00'
                                UNION ALL
                                SELECT n+1
                                     , addtime(starttime, '00:30:00')
                                     , addtime(endtime, '00:30:00')
                                FROM timeslot WHERE n < 24
                            )
                            SELECT slotdate
                                 , time_format(starttime, '%H:%i') as time
                                 , b.id
                          -- 
                          -- cross join the two temporary tables to give rows for every timeslot for the seven days
                          -- then match these against the bookings to see which slots fall withing the booking range
                          -- for the selected resource
                          -- 
                            FROM dateslot d
                                 CROSS JOIN timeslot t
                                 LEFT JOIN booking b ON CONCAT(d.slotdate, ' ', t.starttime) < b.book_end
                                                    AND CONCAT(d.slotdate, ' ', t.endtime) > b.book_start
                                                    AND b.resource_id = ?
                            ORDER BY starttime, slotdate
                        ");
    $res->execute([ $wkcomm, $resid ]);
    $data = [];
    foreach ($res as $r)  {
        $data[$r['time']][$r['slotdate']] = $r['id'];
    }
    
################################################################################
##  LOOP THROUGH ARRAY TO CREATE HTML ROWS
################################################################################
    $tdata  = '';
    foreach ($data as $tslot => $slotdata)  {
        $tdata .= "<tr><td class='w3-gray w3-text-white'>$tslot</td>";
        foreach ($slotdata as $booked)  {
            $class = $booked ? 'w3-red' : '';
            $tdata .= "<td class='$class'>&nbsp;</td>";
        }
        $tdata .= "</tr>\n";
    }
    
################################################################################
## FUNCTIONS
################################################################################
function resourceOptions($pdo, $current)
{
    $res = $pdo->query("SELECT id, description
                        FROM resource
                        ORDER BY description
                       ");
    $opts = "<option value=''>- select resource -</option>\n";
    foreach ($res as $r)   {
        $sel = $r['id']==$current ? 'selected' : '';
        $opts .= "<option $sel value='{$r['id']}'>{$r['description']}</option>\n";
    }
    return $opts;
}
?>
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset="utf-8">
<title>Example</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style type='text/css'>
    table {
        border-collapse: collapse;
        width: 100%;
    }
    th, td {
        padding: 4px;
    }
    th {
        border: 1px solid white;
    }
</style> 
</head>
<body>

<header class='w3-indigo w3-padding'>
    <h1>Resource Bookings</h1>
</header>
<form class='w3-light-gray w3-padding w3-margin-bottom'>
    Week commencing:
    <input type='date' name='wkcomm' value='<?=$wkcomm?>'>
    &emsp;
    Resource:
    <select name='resid'>
        <?=resourceOptions($pdo, $resid)?>
    </select>
    <button class='w3-blue w3-button'>Refresh</button>
</form>

<div class='w3-content'>
    <table class='w3-small' border='1'>
        <?=$theads?>
        <?=$tdata?>
    </table>
</div>
</body>
</html>

 

[adzie]

This looks Brilliant @Barand - I shall go play with this now - appreciate the time you took to reply!

Would it be much to modify so it's a one day view, the drop down changes dates and the dates across the top are the resource?

[Barand]

1 minute ago, adzie said:

Would it be much to modify so it's a one day view, the drop down changes dates and the dates across the top are the resource?

Try it and see.

[Barand]

Slightly simpler this way (by resource headings)

CODE

<?php
require 'db_inc.php';                // 
$pdo = mdbConnect('db1');            // USER YOUR OWN CONNECTION CODE

$wkcomm = $_GET['wkcomm'] ?? date('Y-m-d');

################################################################################
##  COLUMN HEADINGS
################################################################################
    $res = $pdo->query("SELECT id, description
                        FROM resource
                        ORDER BY id
                       ");
    
    $theads = "<tr class='w3-dark-gray'><th>Time Slot</th>";
    foreach ($res as $r)  {
        $theads .= "<th>{$r['description']}</th>";
    }
    $theads .= "</tr>\n";

################################################################################
##  QUERY BOOKINGS AND BUILD ARRAY IN REQUIRED STRUCTURE FOR OUTPUT
################################################################################
    $res = $pdo->prepare("
                          -- 
                          -- create temporary table containing the daily 30-minute timeslots
                          -- 
                            WITH RECURSIVE timeslot (n, starttime, endtime, date) AS
                            (
                                SELECT 1, '07:00:00', '07:30:00', ?
                                UNION ALL
                                SELECT n+1
                                     , addtime(starttime, '00:30:00')
                                     , addtime(endtime, '00:30:00')
                                     , date
                                FROM timeslot WHERE n < 24
                            )
                            
                            SELECT r.id as resid
                                 , time_format(starttime, '%H:%i') as time
                                 , b.id
                          -- 
                          -- cross join the resource table with temp timeslot table to give rows for every timeslot for each resource
                          -- then match these against the bookings to see which slots fall withing the booking range
                          -- for the matching resource
                          -- 
                            FROM resource r
                                 CROSS JOIN timeslot t
                                 LEFT JOIN booking b ON CONCAT(t.date, ' ', t.starttime) < b.book_end
                                                    AND CONCAT(t.date, ' ', t.endtime) > b.book_start
                                                    AND r.id = b.resource_id
                            ORDER BY starttime, resid
                        ");
    $res->execute([ $wkcomm ]);
    $data = [];
    foreach ($res as $r)  {
        $data[$r['time']][$r['resid']] = $r['id'];
    }
    
################################################################################
##  LOOP THROUGH ARRAY TO CREATE HTML ROWS
################################################################################
    $tdata  = '';
    foreach ($data as $tslot => $slotdata)  {
        $tdata .= "<tr><td class='w3-gray w3-text-white'>$tslot</td>";
        foreach ($slotdata as $booked)  {
            $class = $booked ? 'w3-red' : '';
            $tdata .= "<td class='$class'>&nbsp;</td>";
        }
        $tdata .= "</tr>\n";
    }
    
?>
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset="utf-8">
<title>Example</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style type='text/css'>
    table {
        border-collapse: collapse;
        width: 100%;
    }
    th, td {
        padding: 4px;
    }
    th {
        border: 1px solid white;
    }
</style> 
</head>
<body>

<header class='w3-indigo w3-padding'>
    <h1>Resource Bookings</h1>
</header>
<form class='w3-light-gray w3-padding w3-margin-bottom'>
    Date:
    <input type='date' name='wkcomm' value='<?=$wkcomm?>'>
    &emsp;
    <button class='w3-blue w3-button'>Refresh</button>
</form>

<div class='w3-content'>
    <table class='w3-small' border='1'>
        <?=$theads?>
        <?=$tdata?>
    </table>
</div>
</body>
</html>

 

[adzie]

This framework is superb and adaptable i've managed to make some tweaks. relatively easy thanks to your hard work.

I think there is just two things that would finish it for our volunteer group:-

• Ability to span rows for a booked so details of the booking could be shown - Extend the booking Table to show who booked it
• Ability to change colour of booking either for the car/van or the user who booked the vehicle

I do appreciate these are user tweaks but the ability to expand the booking red to be on block and put the text of the booking details would make it so visual for the users.

[Barand]

Added user table and also added user_id to booking table records...

USER
+----+----------+--------------+
| id | username | fullname     |
+----+----------+--------------+
| 1  | tess     | Tess Tickell |
| 2  | hugh     | Hugh Jass    |
| 3  | tom      | Tom DiCanari |
| 4  | lucy     | Lucy Lastik  |
+----+----------+--------------+

BOOKING
+----+-------------+---------------------+---------------------+---------+
| id | resource_id | book_start          | book_end            | user_id |
+----+-------------+---------------------+---------------------+---------+
| 1  | 1           | 2024-05-13 09:00:00 | 2024-05-13 12:00:00 | 1       |
| 2  | 1           | 2024-05-13 12:00:00 | 2024-05-13 17:00:00 | 2       |
| 4  | 1           | 2024-05-16 12:00:00 | 2024-05-16 18:00:00 | 3       |
| 5  | 2           | 2024-05-13 11:30:00 | 2024-05-17 18:00:00 | 4       |
+----+-------------+---------------------+---------------------+---------+

I also added styles for each user name to define background colours.

I used a couple of SQL WINDOW functions to the query

1. the row_number() so I know which is the first cell of each booking (That's the only on we need to output)
2. the row count() so we know how many rows the cell should span.

CODE

<?php
require 'db_inc.php';                // 
$pdo = mdbConnect('db1');            // USER YOUR OWN CONNECTION CODE

$wkcomm = $_GET['wkcomm'] ?? date('Y-m-d');

################################################################################
##  COLUMN HEADINGS
################################################################################
    $res = $pdo->query("SELECT id, description
                        FROM resource
                        ORDER BY id
                       ");
    
    $theads = "<tr class='w3-dark-gray'><th>Time Slot</th>";
    foreach ($res as $r)  {
        $theads .= "<th>{$r['description']}</th>";
    }
    $theads .= "</tr>\n";

################################################################################
##  QUERY BOOKINGS AND BUILD ARRAY IN REQUIRED STRUCTURE FOR OUTPUT
################################################################################
    $res = $pdo->prepare("
                          -- 
                          -- create temporary table containing the daily 30-minute timeslots
                          -- 
                            WITH RECURSIVE timeslot (n, starttime, endtime, date) AS
                            (
                                SELECT 1, '07:00:00', '07:30:00', ?
                                UNION ALL
                                SELECT n+1
                                     , addtime(starttime, '00:30:00')
                                     , addtime(endtime, '00:30:00')
                                     , date
                                FROM timeslot WHERE n < 24
                            )
                            
                            SELECT r.id as resid
                                 , time_format(starttime, '%H:%i') as time
                                 , b.id as id
                                 , u.fullname as booked_by
                                 , u.username
                                 , t.date
                                 , t.starttime
                                 , ROW_NUMBER() OVER w1 as slotnum
                                 , COUNT(b.id) OVER w2 as slotcount
                          -- 
                          -- cross join the resource table with temp timeslot table to give rows for every timeslot for each resource
                          -- then match these against the bookings to see which slots fall withing the booking range
                          -- for the matching resource
                          -- 
                            FROM resource r
                                 CROSS JOIN timeslot t
                                 LEFT JOIN booking b ON CONCAT(t.date, ' ', t.starttime) < b.book_end
                                                    AND CONCAT(t.date, ' ', t.endtime) > b.book_start
                                                    AND r.id = b.resource_id
                                 LEFT JOIN user u ON b.user_id = u.id
                            WINDOW w1 AS (PARTITION BY id, t.date  ORDER BY t.starttime)
                                 , w2 AS (PARTITION BY id, t.date)
                            ORDER BY starttime, resid
                        ");
    $res->execute([ $wkcomm ]);
    $data = [];
    foreach ($res as $r)  {
        $data[$r['time']][$r['resid']] = ['bid' => $r['id'],
                                          'num' => $r['slotnum'],
                                          'span' => $r['slotcount'],
                                          'cust' => $r['booked_by'],
                                          'class' => $r['username']
                                         ];
    }
    
################################################################################
##  LOOP THROUGH ARRAY TO CREATE HTML ROWS
################################################################################
    $tdata  = '';
    foreach ($data as $tslot => $slotdata)  {
        $tdata .= "<tr><td class='w3-gray w3-text-white'>$tslot</td>";
        foreach ($slotdata as $booked)  {
            if ($booked['bid']) { 
                if ($booked['num']==1)  {
                    $tdata .= "<td class='booked {$booked['class']}' rowspan='{$booked['span']}'>{$booked['cust']}</td>";
                }
            }
            else {
                $tdata .= '<td>&nbsp;</td>';
            }
        }
        $tdata .= "</tr>\n";
    }
    
?>
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset="utf-8">
<title>Example</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style type='text/css'>
    table {
        border-collapse: collapse;
        width: 100%;
    }
    th, td {
        padding: 4px;
    }
    th {
        border: 1px solid white;
    }
    .booked {
        color: white;
        border: 3px solid #E0E0E0;
        text-align: center;
    }
    .tess {
        background-color: #A91723;        
    }
    .hugh {
        background-color: #54BC54;        
    }
    .tom {
        background-color: #EC9807;        
    }
    .lucy {
        background-color: #8F1FCF;        
    }
</style> 
</head>
<body>

<header class='w3-indigo w3-padding'>
    <h1>Resource Bookings</h1>
</header>
<form class='w3-light-gray w3-padding w3-margin-bottom'>
    Date:
    <input type='date' name='wkcomm' value='<?=$wkcomm?>'>
    &emsp;
    <button class='w3-blue w3-button'>Refresh</button>
</form>

<div class='w3-content'>
    <table class='w3-small' border='1'>
        <?=$theads?>
        <?=$tdata?>
    </table>
</div>
</body>
</html>