php - 通过预约和检查类型限制患者数(PHP-MYsql)

Closed. This question needs to be more focused。它当前不接受答案。
                            
                        
                    
                
                            
                                
                
                        
                            
                        
                    
                        
                            想改善这个问题吗？更新问题，使其仅通过editing this post专注于一个问题。
                        
                        2年前关闭。
                                                                                            
                
        
我正在为办公室开发一个Web系统，在家中的患者可以在该系统中注册，并以此方式可以预约特定类型的检查。

当患者请求预约时，他将放置他的个人数据，并放置所需的测试类型。我在mysql中有一张表，称为“考试类型”，其中找到5种考试，并附有练习的时间表，也就是说，每种考试在每天的指定时间间隔内进行。

到目前为止，一切进展顺利，我想限制可以要求一天进行某种测试的患者数量，但我一直未能成功实现。

该办公室每天只允许预约20位病人，但每种检查最多只能有5位病人。

我想知道，您如何限制每天只有5名患者可以要求预约这种类型的检查？

我不知道如何限制系统的这一部分，我希望他们能为我提供帮助。我感谢任何支持我的问题的人。

最佳答案

考虑以下：

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)