Using PHP to remove a time slot after it was booked

[jib0723]

Hello,

I am working on a team for a class project where we are building a website (with PHP and MySQL) that will allow a user to book a meeting room (inside of a community center). The center houses four meeting rooms. A user has to create an account to be able to login and then reserve a room. They need to be able to delete and modify a reservation. Additionally, administrators have their own account on the website and can see and control all reservations.

We have created three database tables (using MySQL): reservation, rooms, users. These are the columns for all of the tables

Reservation: resid (Primary Key), roomid (Foreign Key), userid (Foreign Key), date, start_time, end_time, participants (number of people)

Rooms: roomid (Primary Key), mincapacity, maxcapacity

Users: userid (Primary Key), firstname, lastname, email, password, role (tracks if they are a user or an administrator)

We are now trying to figure out how to display reservations that have already been booked (using PHP). Our team has decided that a user can only reserve a room between the hours of 8:00 AM and 5:00 PM. Each reservation will be for 30 minutes at a time. How can we remove a time as an option (example: 9:00 AM- 9:30 AM) if it has already been booked?

Thank you in advance for any suggestions. I would greatly appreciate it.

[requinix]

"Removing" a time is another way of saying you don't want to show the time.

How are you showing available reservation times to the user?

[Barand]

Storing reservations only, as you have done, is the efficient way to do it. However, as you have discovered, finding those not booked is like asking "Hands up up everyone who isn't here?".

Create a fourth table to define time periods (a record for 30 minute slot between 8am and 5pm)

Booking_periods : period_id (PK), start_time, end_time

Now you have something you can match against to find available slots. Whether you use it in a query or use it to define a PHP array is up to you.

You may find this useful

[jib0723]

Hi requinix and Barand,

Thank you very much for your help and fast replies.

 

requinix: Our group is still deciding how to display the reservation times. A table would probably be the best way (?).

 

Barand:  I greatly appreciate the link that you sent. It looks to be very helpful. If we create the "booking_periods" table, would it store all possible times that the room could be booked (8-8:30, 9-9:30, 10-10:30 etc)? Also, should we move the "date" column into that "booking_periods" table?

[Barand]

I'd put dates that are bookable in another table, perhaps a temporary table

Let's say you want to check next weeks booking. Put Mon to Fri dates for next week into booking_dates. With your four rooms and 18 timeslots, this query will check all 360 available timeslots for each room for each day next week and output those still available

SELECT r.roomid
     , d.date
     , start_time
     , p.end_time
FROM room r
     CROSS JOIN
     booking_dates d
     CROSS JOIN
     booking_periods p
     LEFT JOIN
     reservation res USING (roomid, date, start_time)
WHERE res.roomid IS NULL
ORDER BY rddate, r.room, p.start_time;

 

[jib0723]

After talking with my group members, I realized that we are handling the time slots differently than I realized.

The users can book a meeting room between the hours of 8 AM and 5 PM. They have 4 choices: they can book a room for a length of 30 minutes, 1 hour, 1 hour & 30 minutes, or 2 hours.

How would I show available time slots in that scenario? I'm sorry for giving the wrong information in my last posts.

Thank you again and I appreciate your time and help.

[Barand]

The tutorial I linked you to shows how.

[jib0723]

Ok. Thank you again for your time and help.

[jib0723]

Hi Barand, I'm sorry for sending my last post too soon. After reading through that link, I'm still having trouble understanding how to show available timeslots when the duration of a booking is chosen by the user (where it could be 30 minutes, 1 hour, 1.5 hours, or 2 hours long). Is it even possible to do?

[Barand]

From the tutorial I linked you to ...

 

This is working in day slots and you are working in 30 minute slots but the principle is exactly the same

[Barand]

Here's an example showing bookings/available times for 17th Nov 2020

DATA

TABLE: jib_reservation                                                                     TABLE: jib_booking_period
+-------+--------+--------+---------------------+---------------------+--------------+     +-----------+------------+----------+
| resid | roomid | userid | start_time          | end_time            | participants |     | period_id | start_time | end_time |
+-------+--------+--------+---------------------+---------------------+--------------+     +-----------+------------+----------+
|     1 |      1 |      1 | 2020-11-17 09:00:00 | 2020-11-17 10:30:00 |            6 |     |         1 | 08:00:00   | 08:30:00 |
|     2 |      2 |      2 | 2020-11-17 08:30:00 | 2020-11-17 10:30:00 |           15 |     |         2 | 08:30:00   | 09:00:00 |
|     3 |      2 |      3 | 2020-11-17 12:00:00 | 2020-11-17 14:00:00 |           12 |     |         3 | 09:00:00   | 09:30:00 |
|     4 |      1 |      1 | 2020-11-17 11:00:00 | 2020-11-17 12:00:00 |            5 |     |         4 | 09:30:00   | 10:00:00 |
|     5 |      3 |      2 | 2020-11-17 15:00:00 | 2020-11-17 17:00:00 |            6 |     |         5 | 10:00:00   | 10:30:00 |
+-------+--------+--------+---------------------+---------------------+--------------+     |         6 | 10:30:00   | 11:00:00 |
                                                                                           |         7 | 11:00:00   | 11:30:00 |
                                                                                           |         8 | 11:30:00   | 12:00:00 |
                                                                                           |         9 | 12:00:00   | 12:30:00 |
                                                                                           |        10 | 12:30:00   | 13:00:00 |
                                                                                           |        11 | 13:00:00   | 13:30:00 |
                                                                                           |        12 | 13:30:00   | 14:00:00 |
                                                                                           |        13 | 14:00:00   | 14:30:00 |
                                                                                           |        14 | 14:30:00   | 15:00:00 |
                                                                                           |        15 | 15:00:00   | 15:30:00 |
                                                                                           |        16 | 15:30:00   | 16:00:00 |
                                                                                           |        17 | 16:00:00   | 16:30:00 |
                                                                                           |        18 | 16:30:00   | 17:00:00 |
                                                                                           +-----------+------------+----------+

CODE

<?php
require 'db_inc.php';
$db = pdoConnect('test');

$chosen_date = '2020-11-17';

###
###  Get status of bookings on chosen date
###

$res = $db->prepare("SELECT    r.roomid
                             , concat(p.start_time, ' - ', p.end_time) as slot
                             , u.firstname
                             , v.participants
                        FROM jib_room r 
                             CROSS JOIN 
                             jib_booking_period p 
                             LEFT JOIN 
                             jib_reservation v ON r.roomid = v.roomid
                                               AND TIME(v.start_time) < p.end_time
                                               AND TIME(v.end_time) > p.start_time
                                               AND DATE(v.start_time) = ?
                             LEFT JOIN jib_user u USING (userid)
                        ORDER BY slot, roomid"
                        );
$res->execute([ $chosen_date ]);
$slots = [];

// arrange results into an array
foreach ($res as $r) {
    if (!isset($slots[$r['slot']])) {
        $slots[$r['slot']] = array_fill_keys(range(1,4),'');   // empty room array
    }
    $slots[$r['slot']][$r['roomid']] = $r['firstname'] ? "{$r['firstname']} ({$r['participants']})" : '';;
}

// output array to table
$bookdata = '';
foreach ($slots as $s => $rooms) {
    $bookdata .= "<tr><td>$s</td>";
    foreach ($rooms as $user) {
        $cls = $user ? 'booked' : '';
        $bookdata .= "<td class='$cls'>$user</td>";
    }
    $bookdata .= "</tr>\n";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="creation-date" content="11/16/2020">
<title>Bookings</title>
<style type='text/css'>
    table {
        width: 800px;
        font-family: verdana, sans-serif;
        font-size: 11pt;
        border-collapse: collapse;
    }
    th {
        background-color: black;
        color: white;
        padding: 8px;
    }
    td {
        padding: 4px 8px;
    }
    td.booked {
        background-color: red;
        color: white
    }
</style>
</head>
<body>
    <table border='1'>
        <tr><th>Booking slot</th><th>Room 1</th><th>Room 2</th><th>Room 3</th><th>Room 4</th></tr>
        <?=$bookdata?>
    </table>
</body>
</html>

OUTPUT

[jib0723]

Hi Barand,

Thank you very much. I greatly appreciate your time and help on this. I will study this to understand it better.