I have only one table named appointment_master in MySQL in which I store the booking date and time entered by the patient. Appointment time is fixed means patient can book an appointment every 10 minutes means if one patient has booked appointment at 10:00 AM then other can book at 10:10 AM and so on. Now if an appointment is booked at 10:00 AM and when other patient try to book an appointment the date and time slot option 10:00 AM should not be displayed to other patient in the form select option tag.
My table is as follows:
name lastname age gender email phone_no app_date app_time
ramesh ahir 30 Male rames@gmail.com 9824758745 2019-01-18 10:20:00
jitesh thacker 35 Male jitesh@gmail.com 9824758745 2019-01-21 10:30:00
use the below query to get the last appointment time
select max(app_time) from appointment_master;
Save this time in a PHP variable $date1 (or any name)
Save the new appointment time as $date2 variable
get the difference between the two variables
Use if condition to prevent the appointment if the time difference is less than 10 minutes
Consider the following, very simplified example:
DROP TABLE IF EXISTS slots;
CREATE TABLE slots
(id SERIAL PRIMARY KEY,slot VARCHAR(12) NOT NULL UNIQUE);;
INSERT INTO slots VALUES
(1,'Slot 1'),
(2,'Slot 2'),
(3,'Slot 3'),
(4,'Slot 4'),
(5,'Slot 5');
DROP TABLE IF EXISTS bookings;
CREATE TABLE bookings
(booking_id SERIAL PRIMARY KEY
,user_id INT NOT NULL
,slot_id INT NOT NULL UNIQUE
);
INSERT INTO bookings VALUES
(1,101,3);
To show available slots, we can do something like this...
SELECT s.*
FROM slots s
LEFT
JOIN bookings b
ON b.slot_id = s.id
WHERE b.booking_id IS NULL;
+----+--------+
| id | slot |
+----+--------+
| 1 | Slot 1 |
| 2 | Slot 2 |
| 4 | Slot 4 |
| 5 | Slot 5 |
+----+--------+
...or this...
SELECT s.*
, CASE WHEN b.booking_id IS NULL THEN 'yes' ELSE 'no' END available
FROM slots s
LEFT
JOIN bookings b
ON b.slot_id = s.id;
+----+--------+-----------+
| id | slot | available |
+----+--------+-----------+
| 1 | Slot 1 | yes |
| 2 | Slot 2 | yes |
| 3 | Slot 3 | no |
| 4 | Slot 4 | yes |
| 5 | Slot 5 | yes |
+----+--------+-----------+
To make sure two users can't book the same slot simultaneously, we can do something like this...
INSERT INTO bookings (user_id,slot_id)
SELECT 102,1
FROM (SELECT 1) x
LEFT
JOIN bookings y
ON y.slot_id = 1
WHERE y.booking_id IS NULL;
SELECT * FROM bookings;
+------------+---------+---------+
| booking_id | user_id | slot_id |
+------------+---------+---------+
| 1 | 101 | 3 |
| 2 | 102 | 1 |
+------------+---------+---------+
...which prevents another user booking the same slot...
INSERT INTO bookings (user_id,slot_id)
SELECT 103,1
FROM (SELECT 1) x
LEFT
JOIN bookings y
ON y.slot_id = 1
WHERE y.booking_id IS NULL;
SELECT * FROM bookings;
+------------+---------+---------+
| booking_id | user_id | slot_id |
+------------+---------+---------+
| 1 | 101 | 3 |
| 2 | 102 | 1 |
+------------+---------+---------+
Everything else can be handled in your application code (PHP).