Jump to content

Booking Calendar - PHP MYSQL

adzie

By adzie
in MySQL Help

 Share
Go to solution Solved by Barand,

Recommended Posts

adzie Regular Member

adzie

Posted
Posted

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

Link to comment
https://forums.phpfreaks.com/topic/320671-booking-calendar-php-mysql/
Share on other sites
  • Solution
Barand Prolific Member

Barand

Posted
  • Solution
Posted

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...

image.thumb.png.6af7d7bc83ea343b4697e8aec3a04063.png       image.thumb.png.cb7dad56875fc2b069f501a3ad45244e.png

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>

 

  • Like 1
Barand Prolific Member

Barand

Posted
Posted

Slightly simpler this way (by resource headings)

image.png.273d893b822af25e41e0f0e99cf20379.png

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>

 

  • Like 1
adzie Regular Member

adzie

Posted
  • Author
Posted

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 Prolific Member

Barand

Posted
Posted

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.

image.thumb.png.594dd48795daa2789256cf3c6e8a280b.png

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>

 

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.