Avatar of Dave Fyfe
Dave Fyfe
Flag for New Zealand asked on

PHP Time slot availability check

Hi

I have a database listing timeslots for particular days, consisting of
SlotID
Time
Status

I output these to a html table to display, for example,
Time   Status            Slot ID
09:00 Available         1
09:10 Available         2
09:20 Available         3
09:30 Busy                4
09:40 Available         5

Some of my appointment times may be more than the 10 minute slot interval so I need to ability to check if the next time slot is available, so for example, I need to make an appointment for 20 minutes at 09:00 and both SlotID 1 and 2 are available so I can book it and make it busy. However, If I try to book a 20 minute appointment at 09:20 an alert box should appear as only the 1st 10 minutes are available.

Is this possible? May be asking too much can anyone assist?
PHP

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
Beverley Portlock

The key part to the is the data type you use for storing the TIME data. Is it just a VARCHAR, an INT or a TIME data type?
Dave Fyfe

ASKER
Time field is time.
ASKER CERTIFIED SOLUTION
Beverley Portlock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ray Paseur

The thing you need (and almost have) is called a "resource scheduling calendar."  In the general design of this thing, your data base table would have the following columns:

id INT AUTO_INCREMENT
resource_id INT
owner_id INT
busy_start DATETIME
busy_end DATETIME

The id column is just a convenience for your programming.  The resource_id is a pointer to the resource which is probably named and described in a separate table.  Same thing for the owner_id.  The busy_ columns tell the boundaries of the time when the resource has been selected and assigned to a client component named in owner_id.  As you can see this structure would enable you to make and track the assignments of a teacher to a classroom, or an artist to a model, or a doctor to a patient, etc.

When checking and assigning resources your code would want to LOCK TABLES.

To determine if a resource is available over a particular time slot your query looks something like this:

SELECT resource_id FROM rsc_table
WHERE ('$my_start' BETWEEN busy_start AND busy_end)
AND ('$my_end' BETWEEN busy_start AND busy_end)
LIMIT 1

If there are any rows found by the query, the resource is not available.  If there are no rows found, the resource is available and can be scheduled with this query.

INSERT INTO rsc_table ( resource_id, owner_id, busy_start, busy_end )
VALUES ($my_resource_id, $my_owner_id, '$my_start', '$my_end')

This frees you from the constraints of pre-allocated timeslots which, in my experience, tend to be something everyone agrees on at first and then everyone wants to change right after you complete the programming to depend on the timeslots!  So don't do that, just let the resources and owners be identified with the times of the schedule.  Use the appropriate DATETIME values as described in this article and you should be good to go.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Best to all, ~Ray
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes