*************

SQL code: Upload Copy Format Clear

CREATE TABLE patients ( id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(20) NOT NULL, first_name VARCHAR(20) NOT NULL, middle_name VARCHAR(20) NOT NULL ); CREATE TABLE specialityDoctors ( id INT PRIMARY KEY AUTO_INCREMENT, speciality_doctor VARCHAR(30) NOT NULL ); CREATE TABLE doctors ( id INT PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(20) NOT NULL, first_name VARCHAR(20) NOT NULL, middle_name VARCHAR(20) NOT NULL, speciality_doctor_id INT NOT NULL, FOREIGN KEY (speciality_doctor_id) REFERENCES specialityDoctors (id) ); CREATE TABLE timeTags ( id INT PRIMARY KEY AUTO_INCREMENT, doctor_id INT NOT NULL, date_prm DATE NOT NULL, time_tag TIME NOT NULL, isBusy BOOLEAN DEFAULT FALSE, FOREIGN KEY (doctor_id) REFERENCES doctors (id) ); CREATE TABLE wishTable ( id INT PRIMARY KEY AUTO_INCREMENT, speciality_doctor_id INT NOT NULL, patient_id INT NOT NULL, id_time_tag INT DEFAULT NULL, FOREIGN KEY (speciality_doctor_id) REFERENCES doctors (id), FOREIGN KEY (patient_id) REFERENCES patients (id), FOREIGN KEY (id_time_tag) REFERENCES timeTags (id) ); -- CREATE TABLE appointments -- ( -- id INT PRIMARY KEY, -- doctor_id INT NOT NULL, -- patient_id INT NOT NULL, -- time_tags_id INT NOT NULL, -- FOREIGN KEY (doctor_id) REFERENCES doctors (id), -- FOREIGN KEY (patient_id) REFERENCES patients (id), -- FOREIGN KEY (time_tags_id) REFERENCES timeTags (id) -- ); INSERT specialityDoctors (speciality_doctor) VALUES ('Терапевт'), ('Отоларинголог'), ('Невропатолог'), ('Окулист'); INSERT doctors (last_name, first_name, middle_name, speciality_doctor_id) VALUES ('Кукунин', 'Владислав', 'Валерьевич', 1), ('Каменских', 'Елена', 'Федоровна', 2), ('Польская', 'Анна', 'Мартыновна', 1), ('Соколов', 'Даниил', 'Маркович', 2); INSERT patients (last_name, first_name, middle_name) VALUES ('Горемыко', 'Наталья', 'Борисовна'), ('Грац', 'Николай', 'Германович'), ('Акулова', 'Анастасия', 'Петровна'), ('Гололедова', 'Инна', 'Ивановна'); INSERT wishTable (speciality_doctor_id, patient_id) VALUES (2, 1), (2, 2), (3, 1), (1, 4), (1, 2), (4, 1); INSERT timeTags (doctor_id, date_prm, time_tag) VALUES (1, '2023-12-01', '08:00'), (1, '2023-12-01', '10:00'), (1, '2023-12-01', '12:00'), (1, '2023-12-01', '14:00'), (2, '2023-12-01', '08:00'), (2, '2023-12-01', '10:00'); SELECT "doctors" AS title; SELECT * FROM doctors; SELECT "specialityDoctors" AS title; SELECT * FROM specialityDoctors; SELECT "patients" AS title; SELECT * FROM patients; SELECT "timeTags" AS title; SELECT * FROM timeTags; SELECT "wishTable" AS title; SELECT * FROM wishTable; -- Таблица пожеланий пациентов на прием -- по определенной специальности врача SELECT "Пожелания пациентов по специальностям врачей" AS title; SELECT id, ( SELECT speciality_doctor FROM specialityDoctors WHERE id = wishTable.speciality_doctor_id ) AS 'Специальность врача', ( SELECT CONCAT(last_name, ' ', first_name, ' ', middle_name) FROM patients WHERE id = wishTable.patient_id ) AS 'Пациент', ( SELECT id FROM timeTags WHERE id = wishTable.id_time_tag ) AS 'ID записи к врачу' FROM wishTable;

SQL Server: Preserve result

Result: Copy Clear

PHP code: Copy Format Clear

<?php function updateQuery($pdo, $query, $params) { try { $update= $pdo->prepare($query); $update->execute($params); } catch(Exception $e) { throw new Exception("Что-то пошло не так"); } } function getRowsQuery($pdo, $query, $params) { $select= $pdo->prepare($query); $select->execute($params); $dbresult = $select->fetchAll(PDO::FETCH_ASSOC); if ($dbresult) { return $dbresult; } else { throw new Exception('Что-то пошло не так'); } } function printAssocArray($arr) { foreach ($arr as $key => $value) { foreach ($value as $kkey => $vvalue) { // printf("|[%15s]|",$vvalue[$kkey]); $s = str_pad($vvalue[$kkey], 15); echo "|$s"; } echo "\n"; } } // Выборка не исполненных желаний пациентов $queryNoTags = "SELECT id, speciality_doctor_id, patient_id FROM `wishTable` WHERE id_time_tag IS NULL;"; try { $patientsNoPrm = getRowsQuery($pdo, $queryNoTags, null); echo "Выборка не исполненных желаний пациентов\n"; // print_r($patientsNoPrm); printAssocArray($patientsNoPrm); } catch (Exteption $e) { echo $e->getMessage(), "\n"; } //Выборка свободных бирок у врачей с id их специальности $queryDoctorsTags = " SELECT id, doctor_id, time_tag, isBusy, ( SELECT id FROM `specialityDoctors` WHERE id = `timeTags`.doctor_id ) AS 'speciality_id' FROM `timeTags` WHERE isBusy = FALSE;"; try { $timeTags = getRowsQuery($pdo, $queryDoctorsTags, null); echo "Выборка свободных бирок у врачей с id их специальности\n"; print_r($timeTags); } catch (Exteption $e) { echo $e->getMessage(), "\n"; } // Подготовка запросов для записи к врачу $wishTableUpdate = "UPDATE `wishTable` SET `id_time_tag` = :id_time_tag WHERE `id` = :id;"; $timeTagsUpdate = "UPDATE `timeTags` SET `isBusy` = :isBusy WHERE `id` = :id;"; // Создание записей к врачу с отправкой данных в бд foreach ($patientsNoPrm as $patientKey => &$patientNeed) { $id = $patientNeed['id']; foreach ($timeTags as $tagKey => &$rowTag) { if (($rowTag['speciality_id'] == $patientNeed['speciality_doctor_id']) && ($rowTag['isBusy'] == False)) { $timeTags[$tagKey]['isBusy'] = True; $idTimeTag = $rowTag['id']; try { $res = updateQuery($pdo, $wishTableUpdate, array( 'id_time_tag' => $idTimeTag, 'id' => $id)); $res = updateQuery($pdo, $timeTagsUpdate, array( 'isBusy' => True, 'id' => $idTimeTag)); } catch (Exteption $e) { echo $e->getMessage(), "\n"; } break; } } } // Проверка выполнения желаний пациентов $queryWishes = "SELECT id, ( SELECT speciality_doctor FROM specialityDoctors WHERE id = wishTable.speciality_doctor_id ) AS 'Специальность врача', ( SELECT CONCAT(last_name, ' ', first_name, ' ', middle_name) FROM patients WHERE id = wishTable.patient_id ) AS 'Пациент', ( SELECT id FROM timeTags WHERE id = wishTable.id_time_tag ) AS 'ID записи к врачу' FROM wishTable;"; try { $patientsWishes= getRowsQuery($pdo, $queryWishes, null); echo "Проверка выполнения желаний пациентов\n"; print_r($patientsWishes); } catch (Exteption $e) { echo $e->getMessage(), "\n"; } // Проверка занятости бирок $queryTags = "SELECT * FROM `timeTags`;"; try { $tags= getRowsQuery($pdo, $queryTags, null); echo "Проверка занятости бирок\n"; print_r($tags); } catch (Exteption $e) { echo $e->getMessage(), "\n"; }

PHP version:

Show: Warnings Notice

Result: Copy Clear

Like the site? Share it!

Have a questions? Ask in our telegram chat!

PHP versions: PHP 5.6, PHP 7.4, PHP 8.0, PHP 8.1, PHP 8.2.13
RDBMS versions: MariaDB 11.4, MariaDB 11.5, MariaDB 10, MariaDB 10 Sakila (ReadOnly), MySQL 5.7, MySQL 5.7 Sakila (ReadOnly), MySQL 8.0, MySQL 8.0 Sakila (ReadOnly), SQLite 3, PostgreSQL 10 Bookings (ReadOnly), PostgreSQL 11, PostgreSQL 12, PostgreSQL 13, PostgreSQL 14, PostgreSQL 15, MS SQL Server 2017, MS SQL Server 2019, MS SQL Server 2022, MS SQL Server 2022 AdventureWorks (ReadOnly), Firebird 4.0, Firebird 4.0 (Employee), Oracle Database 19c (HR), Oracle Database 21c, Oracle Database 23c Free, SOQOL

PHPize Online / SQLize Online / SQLtest Online