php - 如何在 PHP 中为医生预约系统选择唯一可用的时间和日期

Question

我在 MySQL 中只有一个名为 appointment_master 的表，其中存储了患者输入的预约日期和时间。预约时间是固定的意味着患者可以每 10 分钟预约一次意味着如果一个患者在 10:00 AM 预约，那么其他患者可以在 10:10 AM 预约，依此类推。现在，如果在上午 10:00 预约了约会，并且当其他患者尝试预约时，日期和时间段选项上午 10:00 不应在表单选择选项标签中显示给其他患者。

我的表格如下:

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

Answer 1

考虑以下非常简化的示例:

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);

要显示可用的插槽，我们可以这样做...

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 |
+----+--------+

...或者这个...

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       |
+----+--------+-----------+

为了确保两个用户不能同时预订同一个时段，我们可以这样做......

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 |
+------------+---------+---------+

...这会阻止其他用户预订同一时段...

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 |
+------------+---------+---------+

其他一切都可以在您的应用程序代码 (PHP) 中处理。