Skip to main content Link Menu Expand (external link) Document Search Copy Copied

Trivia Game with Sessions and Database

Here is our example Trivia game code, using a Front Controller design, PHP sessions, and a PostgresSQL database.

The Controller’s run() method has been updated below! There was a bug in the original version that blocked the login command from working – we can’t assume that the $_SESSION variable has name set if the user is in the process of logging in.

New Files

These files are new in this version of the trivia game.

OPTIONAL: Database Setup Script

This is an example script that can be opened in the web browser (if placed in www) that will create tables a database and fill them with sample content, all from PHP. You may find it helpful when creating your database, if you don’t want to use PhpPGAdmin or the command-line PSQL interface.
It incluces drop statements for each table it creates in the database, so that it can be run multiple times. Each time it’s opened in the browser, it will drop all the tables and sequences and re-create them.

<?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";
    } else {
        echo "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);");

    // Read json and insert the trivia questions into the database, assuming
    // the trivia-s24.json file is in the same directory as this script.
    $questions = json_decode(
        file_get_contents("trivia-s24.json"), true);

    $res = pg_prepare($dbHandle, "myinsert", "insert into questions (question, answer) values 
    ($1, $2);");
    foreach ($questions as $q) {
            $res = pg_execute($dbHandle, "myinsert", [$q["question"], $q["answer"]]);
    }

Database.php and Config.php

I’ve omitted these files here. They are available in the web/src/example directory in your local development environment. You can copy/paste them verbatim into the Trivia game’s directory under src and they should just work.

Updated Files

The following files have been updated from the prior version of the Trivia game to add session and database information

TriviaController.php

The TriviaContoller class is defined in a file that resides in the src/ directory. This means that Apache cannot directly serve the file. Note that when it is deployed to the cs4640 server, this file will need to be placed outside the public_html directory.

<?php

class TriviaController {

    private $questions = [];
    
    private $db;

    // An error message to display on the welcome page
    private $errorMessage = "";

    /**
     * Constructor
     */
    public function __construct($input) {
        // We should always start (or join) a session at the top
        // of execution of PHP -- the constructor is the best place
        // to do that.
        session_start(); // start a session!
        
        // Connect to the database by instantiating a
        // Database object (provided by CS4640).  You have a copy
        // in the src/example directory, but it will be below as well.
        $this->db = new Database();

        // Set input
        $this->input = $input;

        // Loading questions no longer necessary, as they are
        // in the database
        //$this->loadQuestions();
    }

    /**
     * Run the server
     * 
     * Given the input (usually $_GET), then it will determine
     * which command to execute based on the given "command"
     * parameter.  Default is the welcome page.
     */
    public function run() {
        // Get the command
        $command = "welcome";
        if (isset($this->input["command"]))
            $command = $this->input["command"];

        // NOTE: UPDATED 3/29/2024!!!!!
        // If the session doesn't have the key "name", AND they
        // are not trying to login (UPDATE!), then they
        // got here without going through the welcome page, so we
        // should send them back to the welcome page only.
        if (!isset($_SESSION["name"]) && $command != "login")
            $command = "welcome";

        switch($command) {
            case "question":
                $this->showQuestion();
                break;
            case "answer":
                $this->answerQuestion();
                break;
            case "login":
                $this->login();
                break;
            case "logout":
                $this->logout();
                // no break; logout will also show the welcome page.
            default:
                $this->showWelcome();
                break;
        }
    }

    /**
     * Login Function
     *
     * This function checks that the user submitted the form and did not
     * leave the name and email inputs empty.  If all is well, we set
     * their information into the session and then send them to the 
     * question page.  If all didn't go well, we set the class field
     * errorMessage and show the welcome page again with that message.
     *
     * NOTE: This is the function we wrote in class!  It **should** also
     * check more detailed information about the name/email to make sure
     * they are valid.
     */
    public function login() {
        if (isset($_POST["fullname"]) && isset($_POST["email"]) &&
            !empty($_POST["fullname"]) && !empty($_POST["email"])) {
            $_SESSION["name"] = $_POST["fullname"];
            $_SESSION["email"] = $_POST["email"];
            $_SESSION["score"] = 0;
            header("Location: ?command=question");
            return;
        }
        $this->errorMessage = "Error logging in - Name and email is required";
        $this->showWelcome();
    }

    /**
     * Alternate Login Function
     *
     * **NEW**: we can replace the function above with this function which
     * will check the user's credentials against their information in the
     * database's users table to see if their password is correct.
     *
     * 1) if the user is not in the table, it automatically adds them and saves
     * the 1-way hash of their password to the table (so that they can log in again later)
     * 2) if the user is in the table, then it verifies that the password they
     * provided is correct.   If so, it allows them to continue playing, reading their
     * score out of the database.
     *
     * NOTE: you should **not** save passwords in clear text -- only the hashed passwords
     * are stored in the database.
     */
    public function loginDatabase() {
        // User must provide a non-empty name, email, and password to attempt a login
        if(isset($_POST["fullname"]) && !empty($_POST["fullname"]) &&
            isset($_POST["email"]) && !empty($_POST["email"]) &&
            isset($_POST["passwd"]) && !empty($_POST["passwd"])) {

                // Check if user is in database, by email
                $res = $this->db->query("select * from users where email = $1;", $_POST["email"]);
                if (empty($res)) {
                    // User was not there (empty result), so insert them
                    $this->db->query("insert into users (name, email, password, score) values ($1, $2, $3, $4);",
                        $_POST["fullname"], $_POST["email"],
                        // Use the hashed password!
                        password_hash($_POST["passwd"], PASSWORD_DEFAULT), 0);
                    $_SESSION["name"] = $_POST["fullname"];
                    $_SESSION["email"] = $_POST["email"];
                    $_SESSION["score"] = 0;
                    // Send user to the appropriate page (question)
                    header("Location: ?command=question");
                    return;
                } else {
                    // User was in the database, verify password is correct
                    // Note: Since we used a 1-way hash, we must use password_verify()
                    // to check that the passwords match.
                    if (password_verify($_POST["passwd"], $res[0]["password"])) {
                        // Password was correct, save their information to the
                        // session and send them to the question page
                        $_SESSION["name"] = $res[0]["name"];
                        $_SESSION["email"] = $res[0]["email"];
                        $_SESSION["score"] = $res[0]["score"];
                        header("Location: ?command=question");
                        return;
                    } else {
                        // Password was incorrect
                        $this->errorMessage = "Incorrect password.";
                    }
                }
        } else {
            $this->errorMessage = "Name, email, and password are required.";
        }
        // If something went wrong, show the welcome page again
        $this->showWelcome();
    }


    /**
     * Logout
     *
     * Destroys the session, essentially logging the user out.  It will then start
     * a new session so that we have $_SESSION if we need it.
     */
    public function logout() {
        session_destroy();
        session_start();
    }
    
    /**
     * Our getQuestion function, now as a method!
     */
    public function getQuestion($id=null) {

        // If $id is not set, then get a random question
        // We wrote this in class.
        if ($id === null) {
            // Read ONE random question from the database
            $qn = $this->db->query("select * from questions order by random() limit 1;");

            // The query function calls pg_fetch_all, which returns an **array of arrays**.
            // That means that if we only have one row in our result, it's an array at
            // position 0 of the array of arrays.
            // Note: we should check that $qn here is _not_ false first!
            return $qn[0];
        }
        
        // If an $id **was** passed in, then we should get that specific
        // question from the database.
        //
        // NOTE: We did **not** write this in class, but it is provided/updated
        // below:
        if (is_numeric($id)) {
            $res = $this->db->query("select * from questions where id = $1;", $id);
            if (empty($res)) {
                return false;
            }
            return $res[0];
        }
       
        // Anything else, just return false
        return false;
    }

    /**
     * Show a question to the user.  This function loads a
     * template PHP file and displays it to the user based on
     * properties of this object and the SESSION information.
     */
    public function showQuestion($message = "") {
        $name = $_SESSION["name"];
        $email = $_SESSION["email"];
        $score = $_SESSION["score"];
        $question = $this->getQuestion();
        include("/opt/src/trivia/templates/question.php");
    }

    /**
     * Show the welcome page to the user.
     */
    public function showWelcome() {
        // Show an optional error message if the errorMessage field
        // is not empty.
        $message = "";
        if (!empty($this->errorMessage)) {
            $message = "<div class='alert alert-danger'>{$this->errorMessage}</div>";
        }
        include("/opt/src/trivia/templates/welcome.php");
    }

    /**
     * Check the user's answer to a question.
     */
    public function answerQuestion() {
        $message = "";
        if (isset($_POST["questionid"]) && is_numeric($_POST["questionid"])) {

            $question = $this->getQuestion($_POST["questionid"]);

            if (strtolower(trim($_POST["answer"])) == strtolower($question["answer"])) {
                $message = "<div class=\"alert alert-success\" role=\"alert\">
                    Correct!
                    </div>";
                // Update the score in the session
                $_SESSION["score"] += 10;

                // **NEW**: We'll update the user's score in the database, too!
                $this->db->query("update users set score = $1 where email = $2;", 
                                    $_SESSION["score"], $_SESSION["email"]);
            }
            else {
                $message = "<div class=\"alert alert-danger\" role=\"alert\">
                    Incorrect! The correct answer was: {$question["answer"]}
                    </div>";
            }
        }

        $this->showQuestion($message);
    }

}

templates/question.php

<!DOCTYPE html>
<html lang="en" data-bs-theme="light">
<head>
  <meta charset="UTF-8">  
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <meta name="author" content="CS4640 Spring 2024">
  <meta name="description" content="Our Front-Controller Trivia Game">  
  <title>PHP Form Example - Trivia</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet"  integrity="sha384-T3c6CoIi6uLrA9TneNEoa7RxnatzjcDSCmG1MXxSR1GAsXEV/Dwwykc2MPK8M2HN"  crossorigin="anonymous">       
</head>

<body>
    
<div class="container" style="margin-top: 15px;">
        <div class="row">
                <div class="col-xs-12">
                <h1>Trivia Game</h1>
                <!-- Show the user's information and score -->
                <h2>Welcome <?=$name?>! (<?=$email?>)  Score: <?=$score?></h2>
                </div>
            </div>
            <div class="row">
                <div class="col-xs-12">
                <?=$message?>
                </div>
            </div>
            <div class="row">
                <div class="col-xs-12">

                <div class="card">
                    <div class="card-header">
                        Question
                    </div>
                    <div class="card-body">
                        <h5 class="card-title"><?=$question["question"]?></h5>
                    </div>
                </div>
                </div>
                
            </div>
            <div class="row">
                <div class="col-xs-12">
                <form action="?command=answer" method="post">
                    <input type="hidden" name="questionid" value="<?=$question["id"]?>">

                    <div class="mb-3">
                        <label for="answer" class="form-label">Trivia Answer: </label>
                        <input type="text" class="form-control" id="trivia-answer" name="answer">
                    </div>

                    <button type="submit" class="btn btn-primary">Submit Answer</button>
                    <a href="?command=logout" class="btn btn-danger">Logout</a>
                </form>
                </div>
            </div>
        </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-C6RzsynM9kWDrMNeT87bh95OGNyZPhcTNXj1NW7RuBCsyN/o0jlpcV8Qyq46cDfL" crossorigin="anonymous"></script>
</body>
</html>

template/welcome.php

<!DOCTYPE html>
<html lang="en" data-bs-theme="light">
<head>
  <meta charset="UTF-8">  
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <meta name="author" content="CS4640 Spring 2024">
  <meta name="description" content="Our Front-Controller Trivia Game">  
  <title>PHP Form Example - Trivia</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet"  integrity="sha384-T3c6CoIi6uLrA9TneNEoa7RxnatzjcDSCmG1MXxSR1GAsXEV/Dwwykc2MPK8M2HN"  crossorigin="anonymous">       
</head>

<body>

<div class="container" style="margin-top: 15px;">
            <div class="row">
                <div class="col-xs-12">
                <h1>Welcome to our Trivia Game!</h1>
                <p>Please answer the questions below to begin playing</p>
                </div>
            </div>
            <?=$message?>
            <div class="row">
                <div class="col-xs-12">
                <form action="?command=login" method="post">
                    <div class="mb-3">
                        <label for="name" class="form-label">Name</label>
                        <input type="text" class="form-control" id="name" name="fullname">
                    </div>
                    <div class="mb-3">
                        <label for="email" class="form-label">Email address</label>
                        <input type="email" class="form-control" id="email" name="email" aria-describedby="emailHelp">
                        <div id="emailHelp" class="form-text">We'll never share your email with anyone else.</div>
                    </div>
                    <div class="mb-3">
                        <label for="passwd" class="form-label">Password</label>
                        <input type="password" class="form-control" id="passwd" name="passwd">
                    </div>

                    <button type="submit" class="btn btn-primary">Start</button>
                </form>
                </div>
            </div>
        </div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-C6RzsynM9kWDrMNeT87bh95OGNyZPhcTNXj1NW7RuBCsyN/o0jlpcV8Qyq46cDfL" crossorigin="anonymous"></script>
</body>
</html>

Unchanged Files

The following files are unchanged from the prior version of the Trivia game.

index.php

Our index file is in the web/www directory. If we were to deploy it to the cs4640 server, it would be in our public_html directory.

<?php

// DEBUGGING ONLY! Show all errors.
error_reporting(E_ALL);
ini_set("display_errors", 1);

// Class autoloading by name.  All our classes will be in a directory
// that Apache does not serve publicly.  They will be in /opt/src/, which
// is our src/ directory in Docker.
spl_autoload_register(function ($classname) {
        include "/opt/src/trivia/$classname.php";
});

// Other global things that we need to do
// (such as starting a session, coming soon!)

// Instantiate the front controller
$trivia = new TriviaController($_GET);

// Run the controller
$trivia->run();

trivia-s24.json

Here is the JSON file used to populate the trivia questions.

[
    {
        "question": "How many floors does the Eiffel Tower have?",
        "answer": "3"
    },
    {
        "question": "Where must Frodo Baggins take the ring in order to destroy it?",
        "answer": "Mt. Doom"
    },
    {
        "question": "What are the spots on dice called?",
        "answer": "Pips"
    },
    {
        "question": "What beverage does Mr. D drink instead of wine in Percy Jackson?",
        "answer": "Diet Coke"
    },
    {
        "question": "What are the two landlocked countries in South America?",
        "answer": "Bolivia and Paraguay"
    },
    {
        "question": "What is the national animal of Scotland?",
        "answer": "Unicorn"
    },
    {
        "question": "Willem Dafoe has appeared in what number of feature films as of March 1, 2024?",
        "answer": "147"
    },
    {
        "question": "What's the only WGS class that counts as an CS integration elective?",
        "answer": "Sex and Resistance on the Internet"
    },
    {
        "question": "What is the oldest animal on Earth?",
        "answer": "Seychelles giant tortoise"
    },
    {
        "question": "What country has Latin as one of its official languages?",
        "answer": "The Vatican (It qualifies as an independent country)"
    },
    {
        "question": "Freddie Prinze Jr. and Sarah Michelle Gellar, now married, starred together in this classic 2002 adventure comedy movie.",
        "answer": "Scooby-Doo"
    },
    {
        "question": "What was Daffy Duck's job on his business card in the Looney Tune's show?",
        "answer": "Wizard"
    },
    {
        "question": "In what year were the first Air Jordan sneakers released?",
        "answer": "1984"
    },
    {
        "question": "230 - 220 * 0.5",
        "answer": "5!"
    },
    {
        "question": "What do you call fake spaghetti?",
        "answer": "an impasta"
    },
    {
        "question": "What planet was the final battle in Star Wars Episode 6 fought on?",
        "answer": "Endor"
    },
    {
        "question": "To the nearest degree, how far does the Leaning Tower of Pisa lean?",
        "answer": "4"
    },
    {
        "question": "who was the times person of the year for 2023",
        "answer": "taylor swift"
    },
    {
        "question": "What is the capital of Papua New Guinea?",
        "answer": "Port Moresby"
    },
    {
        "question": "What is the name of Games Workshop's premier tabletop game?",
        "answer": "Warhammer"
    },
    {
        "question": "What is a group of crows called?",
        "answer": "A murder"
    },
    {
        "question": "What is the most populated capital city in the world?",
        "answer": "Beijing"
    },
    {
        "question": "How many legs does a lobster have?",
        "answer": "10"
    },
    {
        "question": "What is the meaning of life?",
        "answer": "42"
    },
    {
        "question": "What is Shaggy from Scooby Doo's real name?",
        "answer": "Norville Rogers"
    },
    {
        "question": "Which national park is home to the largest known tree in the world?",
        "answer": "Sequoia National Park"
    },
    {
        "question": "What work of literature is regarded as the \"first novel\"?",
        "answer": "Genji Monogatari (The Tale of Genji)"
    },
    {
        "question": "What color is scooby's tag?",
        "answer": "Blue"
    },
    {
        "question": "Who wrote the short story Bartleby, The Scrivener",
        "answer": "Herman Melville"
    },
    {
        "question": "What's the name of the student radio on grounds?",
        "answer": "WXTJ"
    },
    {
        "question": "Who created the first computer?",
        "answer": "Charles Babbage"
    },
    {
        "question": "What vehicle is Volkswagen best known for in the world?",
        "answer": "The Beetle"
    },
    {
        "question": "This hit late 90s-early 00s fantasy action tv show starring Sarah Michelle Gellar and David Boreanaz involved vampires, magic, and doomsdays.",
        "answer": "Buffy the Vampire Slayer"
    },
    {
        "question": "What year was Kermit the Frog first shown on TV?",
        "answer": "1955"
    },
    {
        "question": "Which month of the year is National Ice Cream Month?",
        "answer": "July"
    },
    {
        "question": "What is the speed of light",
        "answer": "300,000,000 m\/s!"
    },
    {
        "question": "In which year did the Berlin Wall fall, symbolizing the end of the Cold War division between East and West Germany?",
        "answer": "Nov-89"
    },
    {
        "question": "What soccer team has won the most Champions League Titles in history?",
        "answer": "Real Madrid"
    },
    {
        "question": "What was the first submarine to complete a submerged circumnavigation of the Earth?",
        "answer": "USS Triton"
    },
    {
        "question": "what performer played at coachella 2023 after a long hiatus",
        "answer": "frank ocean"
    },
    {
        "question": "What is the largest landlocked country in the world?",
        "answer": "Kazakhstan"
    },
    {
        "question": "Is the program League of Legends considered malware?",
        "answer": "Yes"
    },
    {
        "question": "What is the largest desert in the world?",
        "answer": "Antarctica"
    },
    {
        "question": "What type of cloud produces thunderstorms?",
        "answer": "Cumulonimbus clouds"
    }
]