Project 4

Server-side Database-driven PHP Application

This is a team project. Students are encouraged to work in pairs. Individual teams and teams of size 3 are by permission only. Please contact the instructor for permission.

You will use one of the FTP accounts that we have been using in lab to upload/submit. You do not need to submit this project via Blackboard.

Setup Task [5 points]

1. Pick your FTP account or your partner's account as a primary account to develop and submit the project.
2. You must upload an index.html file into the root folder of the primary account you selected, so that the folder listing will not be displayed.
3. In the root folder of the primary account you selected, create a unique project folder name that will be difficult to guess. Do not pick project4, Project4, Project 4, etc. Do not use spaces in your folder name. Note that folder names are case sensitive, so secret and Secret are different folders. Don't use either of those names. Use only letters and numbers in your file name.
4. If you picked the name drlimrules, (don't pick that name), and your FTP username is s987654, then your project URL is
   
   http://s987654.sienasellbacks.com/drlimrules
5. Your main login page must be at that URL on the project due date. See the schedule for due dates.
6. Before April 10th, both members of the team must email the instructor their partner's name and the URL of where their project will reside.
7. The email must be titled Project 4 URL
8. If your name is Tracey Sketchit and your partner's name is Ash Ketchum and your URL is http://s987654.sienasellbacks.com/drlimrules, your email should simply contain the following content:
   

   My team partner is Ash Ketchum and our URL is
   http://s987654.sienasellbacks.com/drlimrules
   

9. Note that I should also get an email from Ash:
   

   My team partner is Tracey Sketchit and our URL is
   http://s987654.sienasellbacks.com/drlimrules
   

Trivia Application

Note that the setup task above is worth 5 points.

Database Tables & Files

Your application will store data in two tables Users and Questions as well as a file called rankings.txt. Because we are working in a shared environment, your table names need to be unique. I recommend naming them Users?????????? and Questions?????????? where ?????????? is a difficult to guess sequence of characters and/or numbers.

While you will want to create new tables, the tables are identical to the ones we created in labs 7 and 8.

To be specific, here are the SQL statements to generate the tables:

CREATE TABLE Users?????????? ( 
  username VARCHAR(64) NOT NULL, 
  password VARCHAR(64) NULL, 
  usertype VARCHAR(64) NOT NULL DEFAULT 'normal', 
  games INT NOT NULL DEFAULT '0', 
  points FLOAT NOT NULL DEFAULT '0.0', 
  PRIMARY KEY (username) 
)

games are the number of times the user plays trivia.

points are the total number of points they earned. Here I'm using a float just in case we want questions to be worth fractional points. But, one "trivia game" will present 10 random questions where correctly answering a question earns 1 point.

CREATE TABLE Questions?????????? ( 
  id INT NOT NULL AUTO_INCREMENT,
  question VARCHAR(1024) NOT NULL,
  choice1 VARCHAR(1024) NOT NULL,
  choice2 VARCHAR(1024) NOT NULL,
  choice3 VARCHAR(1024) NOT NULL,
  choice4 VARCHAR(1024) NOT NULL,
  answer INT NOT NULL, 
  PRIMARY KEY (`id`) 
)

Core Functionality [up to 75 points]

Implement a web application that allows any user to...

1. Create a login account where they specify a username and password. This will insert a row in the Users table with default values for usertype (normal), games (0) and points (0.0). [5 points]
2. Login using the specified username and password. [5 points]

After logging in, the user is displayed with a menu of links to do the following. [5 points]

3. Add a trivia question where they can specify 4 choices and the correct answer. This will insert a row in the Questions table with the specified values. [10 points]
4. Play trivia where 10 questions will be selected at random. Players earn one point for each correctly answered question. At the end of the game, the points are added to the user's point total and the number of games for the user is incremented. Thus, it is important to remember which user is playing. At login, you will want to store this information using $_SESSION['username']. [15 points]
5. View the leader board which will display username and points sorted by points. [10 points]
6. Rank trivia questions will randomly select two questions and display the question text and choices, but not the answer. The user will then pick the one they think is better. The ranking will be recorded in the file rankings.txt, just like we did in project 3. Use the question id to identify the specific questions. If a user likes question 7 better than question 3, the string 7>3, will be appended to file. [10 points]
7. Logout. This will call session_destroy() and unset the SESSION variable on the server. Remember that you have to start/restore a session to destroy a session. [5 points]

Back Links / Redirection After a user adds a question, plays a game of trivia, views the leader board or ranks questions, you must either have back links to the menu of options or you should redirect the browser back to the main menu page. Below is the code to do a redirect to a script called home.php assuming your FTP is s987654 and you secret folder is drlimrules. [5 points]

header("Location: http://s987654.sienasellbacks.com/drlimrules/home.php");

Secure Your Scripts: In development, it is OK if your scripts are publicly accessible. But, you must eventually secure all your scripts. The functionality above should only work if the user logs in. Remember that you can set a session variable at login and then check it at the top of each secured script. You should use the die function to terminate scripts so they do not produce output. [5 points]

session_start();
if ($_SESSION['authenticated'] != true) {
  die("Access denied");	
}

Administrative Functionality [up to 30 points]

8. Create admin users: Write a URL-key protected script to add administrative users to the database (see lab 7) Use the PHP password_hash function with the PASSWORD_BCRYPT option to add securely-encrypted passwords. Here is how to encrypt "abc":
   $hashOfABC = password_hash("abc", PASSWORD_BCRYPT);
   Here is the SQL query to add the user 'alice' as an 'admin' user:
   $sql = "INSERT INTO Users?????????? VALUES ('alice', '".$hashOfABC."', 'admin', '0', '0')"; [5 points]
9. Admin menu: When an administrative user logs in, they will have menu options to delete users and questions. During login, you must check to see if the usertype is admin. The login query can also fetch the usertype:
   

   $result = $mysqli->query("SELECT password, usertype FROM ????? WHERE username='$usr'");
   $row = $result->fetch_row();
   $stored_password = $row[0];
   $user_type = $row[1];
   

   A session variable can be used to store the usertype, i.e.,
   $_SESSION['usertype'] = $user_type. If $_SESSION['usertype'] == "admin" you can generate additional menu items to the following admin-only scripts. [points 5]
10. Delete Users: Write a script that only admins can run that displays all the user data with a delete button next to each user that allows you to delete users (see labs 7 and 9). [points 5]
11. Delete Questions: Write a script that only admins can run that displays all the question data with a delete button next to each question that allows you to delete questions (see labs 7 and 9). [points 5]
12. Delete Low Ranked Questions: Modify the delete questions script so that it calculates the ranking of the questions (see lab 10) and then displays the questions in order of ranking (lowest to highest) with a delete button so it is easier to remove low ranked questions. [points 10]

Additional Features [up to 15 points]

13. Mobile CSS: Use Bootstrap classes or your own CSS so your web application looks good on a mobile device. [1-5 points]
14. AJAX: Use AJAX appropriately to avoid reloading the entire page when playing trivia [1-10 points]

What to do first?

The admin functionality to display and delete users and questions is very useful. You should actually implement items #8, #9, #10 and #11 first.

Script for creating, showing and deleting users should already be complete (see lab 7). But, instead of creating a drop-down menu for deleting users, we can add delete buttons to the show user script. Thus, we know the details of the user we wish to delete. In lab 9, we will do this as an activity to help you get moving.

Script for creating and showing questions should also be complete (see lab 8). You just have to add delete functionality similar to deleting users.

You already implemented a login script (see lab 7). But, you have to add functionality to fetch the user type so the main menu or homepage can be customized for admin users.

The main menu or homepage is just a basic web page with links to the other scripts. All you have to do is session protect this page, which means it needs to be a .php file, so you can add PHP code.

In lecture and lab 10, we will revisit the item ranking script, which you can customize to rank your questions. Ranking questions is the same as ranking items/ponies (see Project 3). But, instead of selecting items from a hard-coded JavaScript array, you must write a script to randomly fetch two questions from the database that is constantly changing. Note that playing trivia does exactly the same thing, but only selects one question. A key difference, is that you simply have to display the question and choices, whereas playing trivia requires generating a form that works.