Exercise: Connect a database in PHP

Create a database

Start phpmyadmin on your computer (http://localhost/phpmyadmin/ or http://localhost:8080/phpmyadmin/.
Create a new database: medizininformatik (or name it as you wish)
If you receive the message "403 Forbidden", you have to edit a file. Inside the XAMPP application, go to Volumes/Mount, then click on Explore. Edit the file lamp/etc/extra/httpd-xampp.conf.
Replace the code 
Require local
with the code 
Require all granted
The page should now appear normally.
Download the following database: medizininformatik2.sql.txt. Import it inside the new created database (medizininformatik).

Manipulate the database

In phpmyadmin, use the SQL mode for writing some SQL-Queries.

  • Write a query for selecting all patients.
    Solution : 
    SELECT * FROM patient
  • Write a query for selecting only the names and first names of all patients.
    Solution : 
    SELECT name, first_name FROM patient WHERE 1
  • Write a query that displays name and first name of all staff, plus their function
    Solution : 
    SELECT name,first_name,function_name
  FROM staff, function WHERE
  staff.fonctionID=function.functionID

    the solution can also be: 
    SELECT name,first_name,function_name
  FROM staff inner join function
  ON  staff.fonctionID=function.functionID
  WHERE 1
  • Write a query that displays for the patient with ID=1 the temperatures.
    Solution : 
    SELECT name, first_name, value, time
  FROM patient, vital_sign
  WHERE patient.patientID = vital_sign.patientID
    AND vital_sign.signID = 1
    AND patient.patientID=1
  • Change your query to display all vital signs (with the names of each sign "Temperature", "Pulse", ...)
    Solution : 
    SELECT name, first_name, value, time, sign_name
  FROM patient, vital_sign, sign
  WHERE patient.patientID = vital_sign.patientID
    AND vital_sign.signID = sign.signID

Write some pages using PDO

  • Write a page displaying the list of all patients.
  • Instert a link in this page, if you click on one patient, it opens a page about this patient.
  • The patient's page contains the names (first and last) and list of vital signs, and also medicines taken.

Login

Modify your program, in order to use the database for the login. The usernames are to be found in table staff, whereas the passwords are in the table credential.
Then you print the list of all the patients, for the users that registred successfully inside the system. For each patient, you will write a link, that will open a page containing the details of the patient (today, this page contains only the name, first name, MRN, date of birth and gender).
Credentials: The passwords are stored "hashed" in order not to be too easy to steal. So we created the password with the function: 

INSERT INTO `medizininformatik`.`credential` (`credentialID`, `staffID`,
`hashed_password`, `hashed_nfctag`) VALUES (NULL, '1', sha('house'), '');
For each user, we used the username as password (it is not very secure indeed).

BACK TO TOP © 2014-2021 Emmanuel Benoist