按预约和检查类型限制患者人数(PHP - MYsql) [英] Limit number of patients by appointment and type of exam (PHP - MYsql)
问题描述
我正在为办公室开发一个网络系统，在家中的患者可以在系统中注册，这样就可以预约特定类型的检查.
当患者请求预约时，他会放置他的个人数据，并放置所需的测试类型.我在 mysql 中有一个名为考试类型"的表，其中可以找到 5 种类型的考试，并附有实践提供的方式的时间表，也就是说，每种类型的考试每天都在指定的时间间隔内实现.
到目前为止一切都进展顺利，我想限制可以要求一天进行这种测试的患者数量，但我未能成功实现.
办公室每天只允许预约 20 次，但每种类型的检查最多只能有 5 名患者.
我想知道，您如何限制每天只能预约 5 名患者进行此类检查?
我不知道如何限制系统的这一部分，我希望他们帮助我.感谢任何支持我提出问题的人.
请考虑以下事项:
DROP TABLE 如果存在预订；CREATE TABLE 预订(booking_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,user_id INT 非空, 输入 INT NOT NULL,booking_date 日期非空，唯一(用户 ID，预订日期));插入预订(用户 ID、类型、预订日期)选择 1, 1, '2017-08-05'发件人 (选择 1) x哪里(选择计数(*)来自预订WHEREbooking_date = '2017-08-05'AND 类型 = 1) <5;查询正常，1 行受影响(0.00 秒)记录:1 重复:0 警告:0插入预订(用户 ID、类型、预订日期)选择 2, 1, '2017-08-05'发件人 (选择 1) x哪里(选择计数(*)来自预订WHEREbooking_date = '2017-08-05'AND 类型 = 1) <5;查询正常，1 行受影响(0.00 秒)记录:1 重复:0 警告:0插入预订(用户 ID、类型、预订日期)选择 3, 1, '2017-08-05'发件人 (选择 1) x哪里(选择计数(*)来自预订WHEREbooking_date = '2017-08-05'AND 类型 = 1) <5;查询正常，1 行受影响(0.00 秒)记录:1 重复:0 警告:0插入预订(用户 ID、类型、预订日期)选择 4, 1, '2017-08-05'发件人 (选择 1) x哪里(选择计数(*)来自预订WHEREbooking_date = '2017-08-05'AND 类型 = 1) <5;查询正常，1 行受影响(0.00 秒)记录:1 重复:0 警告:0插入预订(用户 ID、类型、预订日期)选择 5, 1, '2017-08-05'发件人 (选择 1) x哪里(选择计数(*)来自预订WHEREbooking_date = '2017-08-05'AND 类型 = 1) <5;查询正常，1 行受影响(0.00 秒)记录:1 重复:0 警告:0SELECT * FROM 预订；+------------+---------+------+--------------+|预订_id |用户 ID |类型 |预订日期 |+------------+---------+------+--------------+|1 |1 |1 |2017-08-05 ||2 |2 |1 |2017-08-05 ||3 |3 |1 |2017-08-05 ||4 |4 |1 |2017-08-05 ||5 |5 |1 |2017-08-05 |+------------+---------+------+--------------+5 行(0.00 秒)插入预订(用户 ID、类型、预订日期)选择 6, 1, '2017-08-05'发件人 (选择 1) x哪里(选择计数(*)来自预订WHEREbooking_date = '2017-08-05'AND 类型 = 1) <5;查询正常，0 行受影响(0.00 秒)记录:0 重复:0 警告:0SELECT * FROM 预订；+------------+---------+------+--------------+|预订_id |用户 ID |类型 |预订日期 |+------------+---------+------+--------------+|1 |1 |1 |2017-08-05 ||2 |2 |1 |2017-08-05 ||3 |3 |1 |2017-08-05 ||4 |4 |1 |2017-08-05 ||5 |5 |1 |2017-08-05 |+------------+---------+------+--------------+5 行(0.00 秒)I am developing a web system for an office where patients from home can register in the system and in this way can make an appointment for a specific type of examination.
When the patient requests an appointment, he places his personal data, and also places the type of test that is desired. I have a table in mysql called "exam type" where 5 types of exams are found with a schedule of the way the practice offers it, that is to say, each type of examination is realized in a specified interval of time daily.
Everything has gone well so far where I want to limit the number of patients that can request a day of the kind of test to be done, I have not been able to achieve it successfully.
The office only allows 20 appointments per day but for each type of examination only a maximum of 5 patients.
I would like to know, how could you limit that only 5 patients can request appointments for that type of examination daily?
I do not know how I can limit this part of the system and I would like them to help me. I am grateful to anyone who supports me in my question.
Consider the following:
DROP TABLE IF EXISTS bookings;
CREATE TABLE bookings
(booking_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,user_id INT NOT NULL
,type INT NOT NULL
,booking_date DATE NOT NULL
,UNIQUE(user_id,booking_date)
);
INSERT INTO bookings (user_id, type, booking_date)
SELECT 1
, 1
, '2017-08-05'
FROM (SELECT 1) x
WHERE (SELECT COUNT(*)
FROM bookings
WHERE booking_date = '2017-08-05'
AND type = 1) < 5;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
INSERT INTO bookings (user_id, type, booking_date)
SELECT 2
, 1
, '2017-08-05'
FROM (SELECT 1) x
WHERE (SELECT COUNT(*)
FROM bookings
WHERE booking_date = '2017-08-05'
AND type = 1) < 5;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
INSERT INTO bookings (user_id, type, booking_date)
SELECT 3
, 1
, '2017-08-05'
FROM (SELECT 1) x
WHERE (SELECT COUNT(*)
FROM bookings
WHERE booking_date = '2017-08-05'
AND type = 1) < 5;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
INSERT INTO bookings (user_id, type, booking_date)
SELECT 4
, 1
, '2017-08-05'
FROM (SELECT 1) x
WHERE (SELECT COUNT(*)
FROM bookings
WHERE booking_date = '2017-08-05'
AND type = 1) < 5;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
INSERT INTO bookings (user_id, type, booking_date)
SELECT 5
, 1
, '2017-08-05'
FROM (SELECT 1) x
WHERE (SELECT COUNT(*)
FROM bookings
WHERE booking_date = '2017-08-05'
AND type = 1) < 5;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
SELECT * FROM bookings;
+------------+---------+------+--------------+
| booking_id | user_id | type | booking_date |
+------------+---------+------+--------------+
| 1 | 1 | 1 | 2017-08-05 |
| 2 | 2 | 1 | 2017-08-05 |
| 3 | 3 | 1 | 2017-08-05 |
| 4 | 4 | 1 | 2017-08-05 |
| 5 | 5 | 1 | 2017-08-05 |
+------------+---------+------+--------------+
5 rows in set (0.00 sec)
INSERT INTO bookings (user_id, type, booking_date)
SELECT 6
, 1
, '2017-08-05'
FROM (SELECT 1) x
WHERE (SELECT COUNT(*)
FROM bookings
WHERE booking_date = '2017-08-05'
AND type = 1) < 5;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
SELECT * FROM bookings;
+------------+---------+------+--------------+
| booking_id | user_id | type | booking_date |
+------------+---------+------+--------------+
| 1 | 1 | 1 | 2017-08-05 |
| 2 | 2 | 1 | 2017-08-05 |
| 3 | 3 | 1 | 2017-08-05 |
| 4 | 4 | 1 | 2017-08-05 |
| 5 | 5 | 1 | 2017-08-05 |
+------------+---------+------+--------------+
5 rows in set (0.00 sec)
这篇关于按预约和检查类型限制患者人数(PHP - MYsql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
