-- Врачи
CREATE TABLE doctors (
id INT NOT NULL IDENTITY(0, 1) PRIMARY KEY,
Name VARCHAR(255)
);
INSERT INTO doctors (Name) VALUES ('Doctor1');
INSERT INTO doctors (Name) VALUES ('Doctor2');
INSERT INTO doctors (Name) VALUES ('Doctor3');
INSERT INTO doctors (Name) VALUES ('Doctor4');
INSERT INTO doctors (Name) VALUES ('Doctor5');
INSERT INTO doctors (Name) VALUES ('Doctor6');
-- Пациенты
CREATE TABLE clients (
id INT NOT NULL IDENTITY(0, 1) PRIMARY KEY,
Name VARCHAR(255)
);
INSERT INTO clients (Name) VALUES ('Cl1');
INSERT INTO clients (Name) VALUES ('Cl2');
INSERT INTO clients (Name) VALUES ('Cl3');
INSERT INTO clients (Name) VALUES ('Cl4');
INSERT INTO clients (Name) VALUES ('Cl5');
INSERT INTO clients (Name) VALUES ('Cl6');
-- Пожелания
CREATE TABLE wishes (
id INT NOT NULL IDENTITY(0, 1) PRIMARY KEY,
id_client INT,
id_doctor INT,
wish TEXT,
FOREIGN KEY (id_client) REFERENCES clients(id),
FOREIGN KEY (id_doctor) REFERENCES doctors(id)
);
INSERT INTO wishes (id_client, id_doctor, wish) VALUES (0, 0, 'cl 0 w 0');
INSERT INTO wishes (id_client, id_doctor, wish) VALUES (0, 1, 'cl 0 w 1');
INSERT INTO wishes (id_client, id_doctor, wish) VALUES (1, 0, 'cl 1 w 0');
-- Записи
CREATE TABLE appoints (
id INT NOT NULL IDENTITY(0, 1) PRIMARY KEY,
id_client INT,
id_doctor INT,
FOREIGN KEY (id_client) REFERENCES clients(id),
FOREIGN KEY (id_doctor) REFERENCES doctors(id)
);
INSERT INTO appoints (id_client, id_doctor) VALUES (0, 0);
INSERT INTO appoints (id_client, id_doctor) VALUES (0, 1);
INSERT INTO appoints (id_client, id_doctor) VALUES (1, 0);
INSERT INTO appoints (id_client, id_doctor) VALUES (1, 2);
INSERT INTO appoints (id_client, id_doctor) VALUES (2, 3);
INSERT INTO appoints (id_client, id_doctor) VALUES (3, 4);
INSERT INTO appoints (id_client, id_doctor) VALUES (4, 0);
INSERT INTO appoints (id_client, id_doctor) VALUES (5, 0);
INSERT INTO appoints (id_client, id_doctor) VALUES (5, 5);
<?php
function fetch_and_fill(string $query, &$arr) {
global $pdo;
$stmt = $pdo->query($query);
$rows = $stmt->fetchAll();
foreach($rows as $row) {
array_push($arr, array('id' => $row['id'], 'Name' => $row['Name']) );
}
}
$doctors = array();
$clients = array();
$appoints = array();
fetch_and_fill("SELECT id, Name FROM doctors;", $doctors);
fetch_and_fill("SELECT id, Name FROM clients;", $clients);
foreach ($clients as $client) {
$stmt = $pdo->query("SELECT id_doctor FROM appoints WHERE id_client=" . $client['id']);
$rows = $stmt->fetchAll();
array_push($appoints, array('Client' => $client['id'], 'Doctor' => $rows) );
}