Jump to content

Using PHP to remove a time slot after it was booked


jib0723

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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;

 

Link to comment
Share on other sites


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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

image.png.2d6c4b5c9b464bd1c4545867dcd9e79d.png

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.

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