PHP Trivia Game with Databases

In this activity, we’ll be experimenting with databases in PHP by expanding our Trivia game to include a database!

Getting Started

You should begin by downloading the final version of our Trivia Game from class on Wednesday. You can find the code on the course site.

Creating a `users` table

Note: We’ll do this together as a class!

Adding the `Database` and `Config` classes

Note: We’ll do this together as a class!

So far, our example presents a welcome screen to the user, asking for their name, email, and a password; however, we have not considered the password yet. For this activity, we will implement the following authentication logic:

If the user has never used the system before, we’ll save their name, email, and password into the database.
If the user is returning, if they enter their email and password, we’ll check that a user exists with those credentials; if so, we’ll restore their session.

An Important Notice About Passwords

Do NOT ever ever EVER store user passwords in the database as clear text! Why??

PHP Provides secure one-way hashing for passwords. We should use this built-in functionality to ensure the security of our user data.

Store password: To create a one-way hash of a given password ($password) with PHP’s current default algoritym (bcrypt):
$hash = password_hash($password, PASSWORD_DEFAULT);
We can then safely store the $hash in the database.

Verify password: To verify that a clear-text given password ($password) matches a hashed password ($hash):
$isValid = password_verify($password, $hash);
This method returns true if the password matches the hash and false on failure. Note: we cannot re-hash the password and check that the hashes match. We also cannot decrypt the hash and check clear-text passwords.

In the login() method, alter the initial if statement to also check that the password field is provided and not empty. Then, we will add additional functionality to the body of this if statement.

First, let’s check if this particular user has an account in our system; that is, do they have a row in the users table:
```
 $results = $this->db->query("select * from users where email = $1;");
```
Our query() function returns an associative array of table rows, assuming that the query succeeds. It returns false on error, so it would be helpful to check that $results is not false.
1. If $results is empty, then the user does not have an account, so let’s create one. We must INSERT into our table. Implement this logic. Remember to use password_hash on the given password in $_POST["password"] so that you are not storing the clear-text password. Your PHP code should look something like the following:
```
 $result = $this->db->query("your insert statement here with a $1, $2, and $3;",
         $_POST["fullname"], $_POST["email"], password_hash($_POST["password"],
         PASSWORD_DEFAULT));
```
  We will then store the name and email in $_SESSION and redirect the browser to the question page as usual.
2. If $results is not empty, then the user may have an account. At this point, we need to verify that the passwords match. Check the passwords. Remember to use password_verify above. Hint: you may want to print_r() the $results array to see the array structure! The hashed password will be in this array. The clear-text password will be the one entered by the user in $_POST.
  
  If the passwords match, store the name and email from the database in $_SESSION and then continue on to redirect the browser to the question page as usual.
  
  However, if the passwords do not match, then we shouldn’t allow the user to log in. Instead, let’s either display the login page again with a message or redirect them back to login. Hint: we used this logic last time when the name or email were blank!

Loading Questions

So far, we’ve primarily discussed interacting with PostgresSQL on the command line with psql or through the PhpPGAdmin interface, or directly in our application. However, there may be times we want to bulk-load data into our database. We could do this in one of two ways:

Create a SQL file with SQL commands and directly load it into the database. This file would only have SQL statements (and possibly SQL comments, which start with --).
1. PSQL has a built-in \i command that allows reading in a .sql file and applying it to the database. Note: our Docker containers are currently not set up for reading from the host file system. If you want to go this route for development, you’ll need to add a line to the docker-compose.yml file in the root of our development environment! (see below)
2. PhpPGAdmin’s SQL interface allows uploading a file and running it.
Write a one-off PHP script that loads the data and then updates the database appropriately.

For time, we’ll consider the second option here. We’ve created a PHP script below that loads a number of trivia questions from an API and loads them into our database. Download and run this script to populate your database.

Running a PHP Script

How can we run this script? Can we just use the command line or double-click? Not really.

We need to run this under PHP, so it will need to be in our docker container. The fastest way to do that is to save this script in your web/www directory so that Apache serves it directly, then access http://localhost:8080/.../populate-database.php. That request will then have Apache execute the PHP code. Once the script has run, you’ll want to delete the script or move it out of web/www. Why? We don’t want anyone to run this script again, or we’ll add additional questions to our database!

populate-database.php

<?php
    // Note that these are for the local Docker container
    $host = "db";
    $port = "5432";
    $database = "example";
    $user = "localuser";
    $password = "cs4640LocalUser!"; 

    $dbHandle = pg_connect("host=$host port=$port dbname=$database user=$user password=$password");

    if ($dbHandle) {
        echo "Success connecting to database<br>\n";
    } else {
        die("An error occurred connecting to the database");
    }

    // Drop tables and sequences (that are created later)
    $res  = pg_query($dbHandle, "drop sequence if exists question_seq;");
    $res  = pg_query($dbHandle, "drop sequence if exists user_seq;");
    $res  = pg_query($dbHandle, "drop sequence if exists userquestion_seq;");
    $res  = pg_query($dbHandle, "drop table if exists questions;");
    $res  = pg_query($dbHandle, "drop table if exists users;");

    // Create sequences
    $res  = pg_query($dbHandle, "create sequence question_seq;");
    $res  = pg_query($dbHandle, "create sequence user_seq;");
    $res  = pg_query($dbHandle, "create sequence userquestion_seq;");

    // Create tablse
    $res  = pg_query($dbHandle, "create table questions (
            id  int primary key default nextval('question_seq'),
            question    text,
            answer      text
    );");
    $res  = pg_query($dbHandle, "create table users (
            id  int primary key default nextval('user_seq'),
            name text,
            email text,
            password text,
            score int);");
    $res  = pg_query($dbHandle, "create table user_questions (
            user_id int,
            question_id int);");

    // Read json API endpoint
    // Note: You can change the kinds of questions you get by picking
    //       a different category at OpenTDB and using that API instead!
    //       Build a different API query using this website:
    //       https://opentdb.com/api_config.php
    $questions = json_decode(
        file_get_contents("https://opentdb.com/api.php?amount=15&category=18&difficulty=easy&type=multiple"), true);

    // Check that the endpoint worked
    if ($questions === false || !isset($questions["results"]))
        die("Error reading the API endpoint");

    // Prepare a statement, then execute it repeatedly
    // This is a more effient method than repeatedly calling query()
    $res = pg_prepare($dbHandle, "myinsert", "insert into questions (question, answer) values 
    ($1, $2);");
    foreach ($questions["results"] as $q) {
        $res = pg_execute($dbHandle, "myinsert", [$q["question"], $q["correct_answer"]]);
        echo "Added question: {$q["question"]} with answer {$q["correct_answer"]}<br>\n";
    }
    echo "Done!";

Update Trivia Functionality

The last thing we’ll cover in this activity is updating our database for the game’s functionality. Now that we have questions in the database, we’ll update how questions are chosen to be displayed to the user. Then, we’ll ensure that the user’s score is always saved to the database.

Reading questions from the database

First, we must remove the call to loadQuestions() from the constructor. Additionally, we no longer need the $questions field. Remove both of those.

Next, we must update the logic in getQuestion() to read from the database instead. We’ll do this in two parts (but out of order):

The second return statement originally returned the question with the given $id. Rewrite this logic to use the database. Specifically, use the following lines as a guide to replace this return statement:

 // Select the question's id, question, and answer from the database
 $results = $this->db->query("your select statement here with $1;", $id);
 // Check that $results is not empty (i.e., that this is a valid id)
 return $results[0];

The body of the if statement, including the return provides a random question. We’ll do that with SQL instead. Replace the body of the if with the following code, completing it as necessary:

 $results = $this->db->query("select id, question, answer 
                                 from questions order by random() limit 1;");
 if ($results !== false && isset($results[0])) {
     $_SESSION["qid"] = $results[0]["id"];
     return $results[0];
 }
 // Look! We can throw exceptions like in Java!
 throw new Exception("No questions in the database");

Updating the user’s score

In the answerQuestion() method, along with updating the score in the session ($_SESSION["score"]), also add one line that updates the database:

$this->db->query("update users set score = $1 where email = $2;",
                    /* what params are needed? */);

Should we update the user’s score in the database every time they answer a question correctly? Or can we “get away” with only updating the score when they log out?

Submission

Answer the questions and submit your TriviaController.php on Gradescope in today’s activity submission form. If you worked in a group, please submit once for the entire group, but be sure to include everyone in the Gradescope submission.

In the version of Trivia we’ve created today, the user will get a question from the database at random. However, they may get the same question multiple times!

Question: how could we use the user_questions table to ensure that the user never got the same question twice? When would we add a row to this join/link table? When would we check this table before providing a question to the user?

See this explanation for more details!

Updating Database Container in Docker

If you would like to allow your database Docker container access to the local filesystem so that you can use PSQL’s \i loading command, you will need to update the docker_compose.yml file and rebuild your container.

Update docker_compose.yml: Add the following line under volumes in the upper section labeled db:
      - ./db/:/opt/src/
Be sure it’s indented the same as the prior line. This will map the db directory in your development environment to /opt/src/ in the Docker environment.

Rebuild your container: In Docker Desktop, you may need to delete the containers and re-add them. On the command line, you can cd into the development environment directory and issue the command: docker compose up --build to start the containers and rebuild them.

Reading SQL files: Now any files you put in the db directory will be available in the database container. When you run psql in the container, you can now directly run sql scripts from postgres:
\i /opt/src/mysqlscript.sql