PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE SpecialityDoctors( id int not null primary key, name varchar(256), ); CREATE TABLE Doctors( id int not null primary key, fullName varchar(256), specialityId int not null CONSTRAINT FK_Doctors_SpecialityDoctors FOREIGN KEY (specialityId) REFERENCES SpecialityDoctors(id) ); CREATE TABLE Patients( id int not null primary key, fullName varchar(256) ); CREATE TABLE Preferences( id int not null primary key, IdSpeciality int not null, IdPatient int not null, Preference varchar(512) CONSTRAINT FK_Preference_SpecialityDoctors FOREIGN KEY (IdSpeciality) REFERENCES SpecialityDoctors(id) , CONSTRAINT FK_Preference_Patients FOREIGN KEY (IdPatient) REFERENCES Patients(id) ); CREATE TABLE Appointment( id int not null primary key, IdDoctor int not null, IdPatient int not null, apTime datetime not null, CONSTRAINT FK_Appointment_Doctors FOREIGN KEY (IdDoctor) REFERENCES Doctors(id) , CONSTRAINT FK_Appointment_Patients FOREIGN KEY (IdPatient) REFERENCES Patients(id) ); INSERT INTO SpecialityDoctors (id, name) VALUES (1, 'Cardiologist'), (2, 'Dermatologist'), (3, 'Endocrinologist'); -- -- INSERT INTO Doctors -- INSERT INTO Doctors (id, fullName, specialityId) -- VALUES -- (1, 'John Smith', 1), -- (2, 'Anna Johnson', 2), -- (3, 'Michael Davis', 3); -- -- INSERT INTO Patients -- INSERT INTO Patients (id, fullName) -- VALUES -- (1, 'Emily Brown'), -- (2, 'Daniel Wilson'), -- (3, 'Sophia Martinez'); -- -- INSERT INTO Preference -- INSERT INTO Preferences (Id, IdSpeciality, IdPatient, Preference) -- VALUES -- (1, 1, 1, 'Afraid of injections '), -- (2, 2, 2, 'I have soft skin,'), -- (3, 3, 3, 'I will only go to a woman'); -- -- INSERT INTO Appointment -- INSERT INTO Appointment (Id, IdDoctor, IdPatient, "Time") -- VALUES -- (1, 1, 1, '2023-05-17 10:00:00'), -- (2, 2, 2, '2023-05-18 14:30:00'), -- (3, 3, 3, '2023-05-19 09:45:00'); SELECT p.fullName AS PatientName, d.fullName AS DoctorName, s.name AS Specialty, a.apTime AS AppointmentTime, pr.Preference as Preference FROM Appointment a JOIN Doctors d ON d.id = a.IdDoctor JOIN Patients p ON p.id = a.IdPatient JOIN Preferences pr ON pr.IdPatient = p.id AND pr.IdSpeciality = d.specialityId JOIN SpecialityDoctors s ON s.id = pr.IdSpeciality;
Copy Clear
Copy Format Clear
<?php class Doctor { public $id; public $fullName; public $specialityId; public function __construct($id, $fullName, $specialityId){ $this-> id = $id; $this-> fullName = $fullName; $this-> specialityId = $specialityId; } }; class Patient { public $id; public $fullName; public function __construct($id,$fullName){ $this->id = $id; $this->fullName = $fullName; } } class Preferences { public $id; public $idSpeciality; public $idPatient; public $preference; public function __construct($id,$idSpeciality, $idPatient,$preference){ $this->id = $id; $this->idSpeciality = $idSpeciality; $this->idPatient = $idPatient; $this->preference = $preference; } } class Appointment{ public $id; public $idDoctor; public $idPatient; public $time; public function __construct($id, $idDoctor, $idPatient, $time){ $this -> id = $id; $this -> idDoctor = $idDoctor; $this -> idPatient = $idPatient; $this -> time = $time; } } function findById($arr,$id){ foreach( $arr as $el){ if($el->id == $id){ return $el; } } return null; } $doctors = [ new Doctor(1, 'John Smith', 1), new Doctor(2, 'Anna Johnson', 2), new Doctor(3, 'Michael Davis', 3) ]; $patients = [ new Patient(1, 'Emily Brown'), new Patient(2, 'Daniel Wilson'), new Patient(3, 'Sophia Martinez') ]; $preferences = [ new Preferences(1, 1, 1, 'Afraid of injections '), new Preferences(2, 2, 2, 'I have soft skin,'), new Preferences(3, 3, 3, 'I will only go to a woman') ]; $appointments = [ new Appointment(1, 1, 1, '2023-05-17 10:00:00'), new Appointment(2, 2, 2, '2023-05-18 14:30:00'), new Appointment(3, 3, 3, '2023-05-19 09:45:00') ]; // print_r($doctors); // print_r($patients); // print_r($preferences); // print_r($appointments); foreach($appointments as $appointment ){ $doctor = findById($doctors,$appointment->idDoctor); $patient = findById($patients, $appointment->idPatient); $preference = findById($preferences,$patient->id); $db::table('Patients')->insert(['id'=> $patient->id,'fullName'=>$patient->fullName]); $db::table('Doctors')->insert(['id'=> $doctor->id,'fullName'=>$doctor->fullName, 'specialityId'=>$doctor->specialityId]); $db::table('Preferences')->insert(['id'=> $preference->id,'IdSpeciality'=>$preference->idSpeciality, 'IdPatient'=>$preference->idPatient,'Preference'=>$preference->preference]); $db::table('Appointment')->insert(['id'=> $appointment->id,'IdDoctor'=>$appointment->idDoctor, 'IdPatient'=>$appointment->idPatient,'apTime'=>$appointment->time]); // print_r($appointment); // print_r($doctor); // print_r($patient); // print_r($preference); } $doctorsDB = $db::table('Doctors')->get(); print_r($doctorsDB);
Show:  
Copy Clear
Like the site? Share it!
Have a questions? Ask in our telegram chat!
© 2024 PHPize - format run and share PHP code with SQL queries online | About project | Disclaimer
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