Checking for free slots in a booking system

Question

I am looking for assistance as I'm trying to create a testing booking system but each person needs to be booked onto a specific machine (laptop) at a specific time. Please see below database:

Apologies for the poor image but hopefully you get the idea. I've got the database to show me booked records, but I want to be able to see whats left.

I currently have 10 machines each with three time slots which is easier you visualise like this:

****************************************************************
* Machine/Laptop *   Time Slot   *      Name      *     Test   *
****************************************************************
*                * 09:30 - 11:00 * John Adams     * English    *
*    Laptop 1    * 11:00 - 13:00 * Book Slot      * Free       *
*                * 13:00 - 14:00 * Raj Patel      * English    *
****************************************************************
*                * 09:00 - 12:00 * Joe King       * English    *
*    Laptop 2    * 11:00 - 13:00 * Andrew Lil     * Maths      *
*                * 13:00 - 12:00 * Book Slot      * Free       *
****************************************************************

Now I don't know how to make a single database query work, but for example purposes heres a really dirty way to do it which would involve checking the database 30 times each time the page is loaded:

$sql = $dbconn->prepare('SELECT COUNT(*) AS `total` 
                          FROM event_booking 
                          INNER JOIN event_machine
                          ON event_booking.machine_id=event_machine.id
                          INNER JOIN event_machine_time
                          ON event_booking.machine_time_id=event_machine_time.id
                          WHERE information_id = '.$_GET["id"].' AND event_machine.id=1 AND event_machine_time.id=2');
    $sql->execute();
    $result = $sql->fetchObject();
    if ($result->total > 0) { echo 'Slot Booked'; } else { echo '<a href="booking.php">Book Slot</a>'; }

For each time this is called I would have to change the event_machine.id=? AND event_machine_time.id=? to call the specific slot.

I'm sure I'm approaching this wrong so any help would be great :)

EDIT:

If it helps heres what I'm going for

As you can see the purple buttons are the active slots and the darker one are booked. For a demo I could only fit 5 of the 10 laptops but you get the idea.

Here is the database query I'm using to call all of the event bookings. This shows all of the bookings made within the event from event_information then I use fetchAll into a table. But I don't know if something could be used with this query to get the booking ID's over to the time slots?

$Event_list = $dbconn->prepare('SELECT
                                  event_booking.id,
                                  event_booking.live,
                                  event_booking.confirmation_email,
                                  event_candidate.firstname,
                                  event_candidate.surname,
                                  event_candidate.email,
                                  event_information.type,
                                  event_machine.id AS machine_get_id,
                                  event_machine.name,
                                  event_machine_time.id AS machine_time_get_id,
                                  event_machine_time.start_time AS machine_start_time,
                                  event_machine_time.end_time AS machine_end_time
                                  FROM event_booking
                                  INNER JOIN event_candidate
                                  ON event_booking.candidate_id=event_candidate.id
                                  INNER JOIN event_information
                                  ON event_booking.information_id=event_information.id
                                  INNER JOIN event_machine
                                  ON event_booking.machine_id=event_machine.id
                                  INNER JOIN event_machine_time
                                  ON event_booking.machine_time_id=event_machine_time.id
                                  WHERE information_id = ?'); 
    $Event_list->execute(array($_GET["id"]));

ANOTHER EDIT:

Another option would be to scrap the event_machine and add a column called machine_name into event_machine_time then use the following:

$Event_Times = $dbconn->query('SELECT *
                                   FROM event_machine_time
                                   LEFT JOIN event_booking
                                   ON event_machine_time.id=event_booking.machine_time_id
                                   ');

Use this within a table:

<td>
        <?php foreach ($Event_Times->fetchAll() as $Event_Times_Row) { ?>
            <?php if($Event_Times_Row["machine_name"] == "Laptop 1") { ?>
            <a class="button<?php if($Event_Times_Row["information_id"] == $_GET["id"]) { echo " button-disabled"; } ?>" href="#"><?php echo date("G:i", strtotime($Event_Times_Row["start_time"])); ?></a>
        <?php } } ?>
</td>

However I would need to call this 10 times and I don't think fetchAll allows this and I would need to repeat this db query 10 times to make it work.

Not your current issue but you are misusing prepared statements. Variables should be bound. `WHERE information_id = ?` then `$sql->execute(array($_GET["id"]));` — chris85, Apr 22 '17 at 18:00
Apologies I whipped this up as an example, but you are spot on I do for all live projects — Joseph Gregory, Apr 22 '17 at 18:03
What's the advantage of storing event_machine_time separately? — Strawberry, Apr 22 '17 at 18:04
Nothing really, it was just my approach, otherwise machine and time could be in the same table, just it would mean I would have three rows each with with same machine name. I suppose its useful for expanding times and machines, but I can change it. — Joseph Gregory, Apr 22 '17 at 18:06
http://stackoverflow.com/questions/43167887/sql-query-for-hotel-room-reservation/43289025#43289025 this is what you're after, it isn't ? — Blag, Apr 22 '17 at 18:33
could you show us what you expect to have ? and provide some data from your db, so we can make test — Blag, Apr 22 '17 at 18:43
I've added some extra bits, but I can join `event_machine` and `event_machine_time` if works better — Joseph Gregory, Apr 22 '17 at 19:44

score 1 · Answer 1 · answered Apr 22 '17 at 21:48

Your approach need some corrections:

event_machine_time table must be related to event_machine table. So event_machine_time table must have a field name event_machine_id.
After that you need not a direct relation between event_booking and event_machine tables, because it is discoverable from relation other relation in the previous step.
It is better to drop start_date& end_date fields from event_information table, because they are equal. you must insert a new filed event_date representing both of them.
You must drop start_time and end_time fields from event_information table, because they are discoverable from event_machine_time table in relation with the main table to maintain data integration.
It is better to include event_information table in the main booking_event table, because there is a 1-to-1 relation between these tables.

After you make these changes all you want to do will be very simpler.

score 0 · Answer 2 · answered Apr 22 '17 at 23:05

Thank you all for your help but this is the conclusion I came to:

I amended my database by dropping the event_machine table as shown here:

In the event_booking table I added a column called machine_name and made three entries with the same laptop name, but different timings e.g.

****************************************
* machine_name * start_time * end_time *
****************************************
* Laptop 1     * 09:30      * 11:00    *
* Laptop 1     * 11:30      * 13:00    *
* Laptop 1     * 13:30      * 15:00    *
* Laptop 2     * 09:30      * 11:00    *
* Laptop 2     * 11:30      * 13:00    *
* Laptop 2     * 13:30      * 15:00    *
****************************************

Using this I then used the following database relationships, which I ran as a fetchAll() loop:

$Event_Times = $dbconn->query('SELECT *
                                   FROM event_machine_time
                                   LEFT JOIN event_booking
                                   ON event_machine_time.id=event_booking.machine_time_id
                                   ORDER BY start_time
                                   ');
$Event_Times_Loop = $Event_Times->fetchAll(PDO::FETCH_ASSOC);

Then I ran the loop 10 times (7 shown in the photo) within a HTML table:

<td>
    <?php foreach ($Event_Times_Loop as $Event_Times_Row) { ?>
    <?php if($Event_Times_Row["machine_name"] == "Laptop 1") { ?>
    <a class="button<?php if($Event_Times_Row["information_id"] == $_GET["id"]) { echo " button-disabled"; } ?>" href="#"><?php echo date("G:i", strtotime($Event_Times_Row["start_time"])); ?></a>
    <?php } } ?>
</td>

So with a bit of CSS to disable the buttons where someone has already booked it seems to work pretty nicely. There's most likely a much better, cleaner solution but al least I have something that works. Also apologies for my terminology I'm sure I've got that wrong too :)

Thanks for all your help!

Ok, It works. but it has normalizing problem. that means that you cannot get a report for machines (without times) for example. — Alireza Zojaji, Apr 23 '17 at 21:08
Sorry I really don't understand, it's because of my understanding of the problem — Joseph Gregory, Apr 23 '17 at 21:15
But step 1 doesn't apply anymore as I've merged `event_machine_time` and `event_machine` together — Joseph Gregory, Apr 23 '17 at 21:26
When you merge these two tables you lose the data of machines (laptops) separately, because machine name and specifications, are identical for 1 machine and must be repeat in merged table. so for data integrity reasons you must have separate `event_machine` table for storing machine specs and reporting vs. machines. — Alireza Zojaji, Apr 23 '17 at 21:32

Checking for free slots in a booking system

2 Answers2