首页 文章

PHP MySQL可用性表

提问于
浏览 次
2

我正在尝试为本周做医生可用性表 . 手术中有3名医生（Curly，Moe，Larry） . 所有3名医生必须始终在每个牢房中，例如星期一 - 上午9点至10点应该首先展示在此期间有预约的医生，我有这个工作 . 我的问题是如何让它在医生下面打印剩下的两个医生的名字 . 这样做的原因是，一旦我有这个部分想通了我将需要对这些医生进行颜色编码，如果预订则为红色，如果可用则为绿色 .

PHP页面：

<?php
$servername = "???";
$username = "???";
$password = "???";
$dbname = "???";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT doctor FROM booking WHERE datereq BETWEEN '2015-11-02 09:00:00' AND '2015-11-02 10:00:00'";
$result = $conn->query($sql);

echo "
<table width='80%' align='center' >
<div id='head_nav'>
<tr>";
echo"<th>Time</th>
<th>Monday</th>
<th>Tuesday</th>
<th>Wednesday</th>
<th>Thursday</th>
<th>Friday</th>
</tr>
</div>";

echo"<tr><th>09:00 - 10:00</th><td>";

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<br>". $row["doctor"]. "<br>";
echo "<br>". $row["doctor"]. "<br>";
echo "<br>". $row["doctor"]. "<br>";

}
} else {
echo "0 Results";
}

$sql = "SELECT doctor FROM booking WHERE datereq BETWEEN '2015-11-03 10:00:00' AND '2015-11-03 11:00:00'";
$result = $conn->query($sql);

echo"<tr><th>10:00 - 11:00</th><td>";

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<br>". $row["doctor"]. "<br>";
echo "<br>". $row["doctor"]. "<br>";
echo "<br>". $row["doctor"]. "<br>";

}
} else {
echo "0 Results";
}


$conn->close();
?>
php mysql

1 回答

  • 1

    这使用您在评论和其他地方看到的模式 . 我把它清理了一下 . 它使你的时间表免费维护，而不是你的 . 您的时间表中有新行 . 日常 . 对我来说，它应该都位于 booking 表中 .

    因此，在以下结果集中，您会看到我只是继续构建先前的结果集，从而引入信息 . 婴儿步骤将网格放在一起 .

    架构

    drop table if exists doctors;
create table doctors 
(   doctorId int(4) primary key,
    doctorName varchar(20) not null -- avoid nulls when they wouldn't be anyway
);

insert into doctors (doctorId, doctorName) values 
('1234', 'Larry'),
('6568', 'Moe'),
('9012', 'Curly');

drop table if exists timeSlots;
create table timeSlots 
(   -- note the timeSlots are not iced in stone
    -- you can wedge other timeslots in the gaps (like 09:30), just order by theTime
    --
    -- so it does not matter that you get the auto incs of timeslots all sequential
    -- when you first make it (timeslot #10 can be 9:30 later if you write it right)
    --
    id int auto_increment primary key,
    theTime time not null   -- avoid mysql keywords or reserved words
    -- this digresses from your model. It is easily maintainable
    -- and does not turn timeSlots (generic) into a booking table. You already have that
    -- so in theory, this table could have as little as 10 to 35 rows max, ever
);

insert into timeSlots (theTime) values ('09:00:00');    -- 1
insert into timeSlots (theTime) values ('10:00:00');    -- 2
insert into timeSlots (theTime) values ('11:00:00');
insert into timeSlots (theTime) values ('12:00:00');
insert into timeSlots (theTime) values ('13:00:00');
insert into timeSlots (theTime) values ('14:00:00');
insert into timeSlots (theTime) values ('15:00:00');
insert into timeSlots (theTime) values ('16:00:00');
insert into timeSlots (theTime) values ('17:00:00');    -- 9

-- select * from doctors;
-- select * from timeSlots;

drop table if exists booking;
create table booking
(   -- surname varchar(20), -- no, de-normalized
    id int auto_increment primary key,
    patientId int not null,
    -- amORpm varchar(2), no, de-normalized
    apptDate date not null, -- needs an index
    timeSlotId int not null,
    doctorId int(4),
    key(apptDate) -- new index
    -- other indexes
    -- foreign key constraints too
);

truncate table booking;
insert into booking (patientId, apptDate, timeSlotId, doctorId) values
(1738, '2015-11-30', 1, 1234),
(1991, '2015-11-30', 3, 6568),
(1098, '2015-11-30', 7, 9012),
(1934, '2015-12-02', 2, 6568),
(1432, '2015-12-02', 8, 1234);

drop table if exists updatepersonal;
create table updatepersonal (
    surname varchar(20),
    id int(4),
    forename varchar(20),
    DOB datetime,
    doctorId  int not null,
    contactno char(11),
    email varchar(40),
    address varchar(100),
    PRIMARY KEY (id)
);

insert into updatepersonal (surname, id, forename, DOB, doctorId, contactno, email, address) values 
('Yates', 1738, 'Keith', '1964-12-02', 1234, '01610736018', 'ky@hotmail.com', '3 Abbey Road, M159GD'),
('Smith', 1032, 'Douglas', '1970-04-12', 6568, '01613470293', 'smith@hotmail.com', '34 Oxford Road, M139GD'),
('Abbasi', 1098, 'Tauqeer', '1964-12-02', 1234, '01618390932', 'abbasit@hotmail.co.uk', '63 Somerville Road, M129KD'),
('Aqeel', 1934, 'Hussain', '1970-04-12', 6568, '01618390479', 'aqeelh@hotmail.com', '100 Wilmslow Road, M167HB'),
('John', 1432, 'Michel', '1964-12-02', 1234, '01613905853', 'johnm@hotmail.com', '387 Stratford Road, M113SD');

    第一个问题是弄湿我们的脚 . 显示给定日期的时间段，并与所有医生交叉加入 . 注意 order by .

    select ts.id,ts.theTime,d.doctorId,d.doctorName 
from timeSlots ts 
cross join doctors d 
order by ts.theTime,d.doctorName;

+----+----------+----------+------------+
| id | theTime  | doctorId | doctorName |
+----+----------+----------+------------+
|  1 | 09:00:00 |     9012 | Curly      |
|  1 | 09:00:00 |     1234 | Larry      |
|  1 | 09:00:00 |     6568 | Moe        |
|  2 | 10:00:00 |     9012 | Curly      |
|  2 | 10:00:00 |     1234 | Larry      |
|  2 | 10:00:00 |     6568 | Moe        |
|  3 | 11:00:00 |     9012 | Curly      |
|  3 | 11:00:00 |     1234 | Larry      |
|  3 | 11:00:00 |     6568 | Moe        |
|  4 | 12:00:00 |     9012 | Curly      |
|  4 | 12:00:00 |     1234 | Larry      |
|  4 | 12:00:00 |     6568 | Moe        |
|  5 | 13:00:00 |     9012 | Curly      |
|  5 | 13:00:00 |     1234 | Larry      |
|  5 | 13:00:00 |     6568 | Moe        |
|  6 | 14:00:00 |     9012 | Curly      |
|  6 | 14:00:00 |     1234 | Larry      |
|  6 | 14:00:00 |     6568 | Moe        |
|  7 | 15:00:00 |     9012 | Curly      |
|  7 | 15:00:00 |     1234 | Larry      |
|  7 | 15:00:00 |     6568 | Moe        |
|  8 | 16:00:00 |     9012 | Curly      |
|  8 | 16:00:00 |     1234 | Larry      |
|  8 | 16:00:00 |     6568 | Moe        |
|  9 | 17:00:00 |     9012 | Curly      |
|  9 | 17:00:00 |     1234 | Larry      |
|  9 | 17:00:00 |     6568 | Moe        |
+----+----------+----------+------------+

    以下内容进一步扩展，以显示一个日期的约会 .

    select ts.id as tsId,ts.theTime,d.doctorId as docId,d.doctorName,b.id as bookingId 
from timeSlots ts 
cross join doctors d 
left join booking b 
on b.apptDate='2015-11-30' and b.timeSlotId=ts.id and b.doctorId=d.doctorId 
order by ts.theTime,d.doctorName;

+------+----------+-------+------------+-----------+
| tsId | theTime  | docId | doctorName | bookingId |
+------+----------+-------+------------+-----------+
|    1 | 09:00:00 |  9012 | Curly      |      NULL |
|    1 | 09:00:00 |  1234 | Larry      |         1 |
|    1 | 09:00:00 |  6568 | Moe        |      NULL |
|    2 | 10:00:00 |  9012 | Curly      |      NULL |
|    2 | 10:00:00 |  1234 | Larry      |      NULL |
|    2 | 10:00:00 |  6568 | Moe        |      NULL |
|    3 | 11:00:00 |  9012 | Curly      |      NULL |
|    3 | 11:00:00 |  1234 | Larry      |      NULL |
|    3 | 11:00:00 |  6568 | Moe        |         2 |
|    4 | 12:00:00 |  9012 | Curly      |      NULL |
|    4 | 12:00:00 |  1234 | Larry      |      NULL |
|    4 | 12:00:00 |  6568 | Moe        |      NULL |
|    5 | 13:00:00 |  9012 | Curly      |      NULL |
|    5 | 13:00:00 |  1234 | Larry      |      NULL |
|    5 | 13:00:00 |  6568 | Moe        |      NULL |
|    6 | 14:00:00 |  9012 | Curly      |      NULL |
|    6 | 14:00:00 |  1234 | Larry      |      NULL |
|    6 | 14:00:00 |  6568 | Moe        |      NULL |
|    7 | 15:00:00 |  9012 | Curly      |         3 |
|    7 | 15:00:00 |  1234 | Larry      |      NULL |
|    7 | 15:00:00 |  6568 | Moe        |      NULL |
|    8 | 16:00:00 |  9012 | Curly      |      NULL |
|    8 | 16:00:00 |  1234 | Larry      |      NULL |
|    8 | 16:00:00 |  6568 | Moe        |      NULL |
|    9 | 17:00:00 |  9012 | Curly      |      NULL |
|    9 | 17:00:00 |  1234 | Larry      |      NULL |
|    9 | 17:00:00 |  6568 | Moe        |      NULL |
+------+----------+-------+------------+-----------+

    如果您将星期一作为日期传递，则以下内容会对其进行扩展，并按日期从星期一开始按不同的列开始 . 因此，如果这是您的星期几开始所需要的，那么这就是您需要传递的内容 .

    它通过交叉连接完成，然后按天连接 . 它通过底部的交叉连接（星期一日期）将您的数据参数（日期，星期一）带入，它简单地变为 variable ， @Monday .

    select ts.theTime,d.doctorName, 
bMon.id as bMon, bTue.id as bTue, bWed.id as bWed, bThu.id as bThu, bFri.id as bFri 
from timeSlots ts 
cross join doctors d 
left join booking bMon 
on bMon.apptDate=@Monday and bMon.timeSlotId=ts.id and bMon.doctorId=d.doctorId 
left join booking bTue 
on bTue.apptDate=date_add(@Monday, INTERVAL 1 DAY) and bTue.timeSlotId=ts.id and bTue.doctorId=d.doctorId 
left join booking bWed 
on bWed.apptDate=date_add(@Monday, INTERVAL 2 DAY) and bWed.timeSlotId=ts.id and bWed.doctorId=d.doctorId 
left join booking bThu 
on bThu.apptDate=date_add(@Monday, INTERVAL 3 DAY) and bThu.timeSlotId=ts.id and bThu.doctorId=d.doctorId 
left join booking bFri 
on bFri.apptDate=date_add(@Monday, INTERVAL 4 DAY) and bFri.timeSlotId=ts.id and bFri.doctorId=d.doctorId 
cross join (select @Monday:='2015-11-30') params 
order by ts.theTime,d.doctorName;

+----------+------------+------+------+------+------+------+
| theTime  | doctorName | bMon | bTue | bWed | bThu | bFri |
+----------+------------+------+------+------+------+------+
| 09:00:00 | Curly      | NULL | NULL | NULL | NULL | NULL |
| 09:00:00 | Larry      |    1 | NULL | NULL | NULL | NULL |
| 09:00:00 | Moe        | NULL | NULL | NULL | NULL | NULL |
| 10:00:00 | Curly      | NULL | NULL | NULL | NULL | NULL |
| 10:00:00 | Larry      | NULL | NULL | NULL | NULL | NULL |
| 10:00:00 | Moe        | NULL | NULL |    4 | NULL | NULL |
| 11:00:00 | Curly      | NULL | NULL | NULL | NULL | NULL |
| 11:00:00 | Larry      | NULL | NULL | NULL | NULL | NULL |
| 11:00:00 | Moe        |    2 | NULL | NULL | NULL | NULL |
| 12:00:00 | Curly      | NULL | NULL | NULL | NULL | NULL |
| 12:00:00 | Larry      | NULL | NULL | NULL | NULL | NULL |
| 12:00:00 | Moe        | NULL | NULL | NULL | NULL | NULL |
| 13:00:00 | Curly      | NULL | NULL | NULL | NULL | NULL |
| 13:00:00 | Larry      | NULL | NULL | NULL | NULL | NULL |
| 13:00:00 | Moe        | NULL | NULL | NULL | NULL | NULL |
| 14:00:00 | Curly      | NULL | NULL | NULL | NULL | NULL |
| 14:00:00 | Larry      | NULL | NULL | NULL | NULL | NULL |
| 14:00:00 | Moe        | NULL | NULL | NULL | NULL | NULL |
| 15:00:00 | Curly      |    3 | NULL | NULL | NULL | NULL |
| 15:00:00 | Larry      | NULL | NULL | NULL | NULL | NULL |
| 15:00:00 | Moe        | NULL | NULL | NULL | NULL | NULL |
| 16:00:00 | Curly      | NULL | NULL | NULL | NULL | NULL |
| 16:00:00 | Larry      | NULL | NULL |    5 | NULL | NULL |
| 16:00:00 | Moe        | NULL | NULL | NULL | NULL | NULL |
| 17:00:00 | Curly      | NULL | NULL | NULL | NULL | NULL |
| 17:00:00 | Larry      | NULL | NULL | NULL | NULL | NULL |
| 17:00:00 | Moe        | NULL | NULL | NULL | NULL | NULL |
+----------+------------+------+------+------+------+------+

    所以上面是你的约会网格 . 继续这个主题，加入患者表格，在5个最右边的列中显示他们的姓氏 . 管他呢 . 这是微不足道的 . 但这个答案太长了，无法阅读 .

    如果数据不为null，则它有一个约会（如你所说的绿色文本） . 如果是 NULL ，那么医生和时间的那个位置还没有预约 .

    回复于

相关问题