Link to home
Start Free TrialLog in
Avatar of Dave Fyfe
Dave FyfeFlag 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?
Avatar of Beverley Portlock
Beverley Portlock
Flag of United Kingdom of Great Britain and Northern Ireland image

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?
Avatar of Dave Fyfe

ASKER

Time field is time.
ASKER CERTIFIED SOLUTION
Avatar of Beverley Portlock
Beverley Portlock
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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