Lab 9

Integrating PHP, MySQL, Bootstrap & AJAX/JavaScript

Implementing scripts using best practices

Getting Started

Click on the following link and login with Username alice and Password abc
Mystery Application
Use the Insert Questions link to add two good questions and one bad one
Use the Delete Questions link to delete the bad question
Logout
Try to access home.php
Notice that this mystery application is very similar to one you must implement
Right-click the following link and Save Link As...
lab9.zip
Uncompress the zip file
Copy all the file to your lab9 folder
Upload your lab9 folder to your server
Test it to make sure it works for you on your server.

Part 1: Linking pages the smart way

Note how annoying it was to not have back links to home.php. Instead of adding back links, we will create a navigation menu for each page.

Open functions.php in Notepad++ and examine the print_html_header function. Note how it prints an HTML header and splices in the page title. It also includes all the CSS links for Bootstrap 4.

The following code will create a navigation menu that we can put inside the container div tag but before the h2 tag. However, instead of hard-coding this menu, we will use an array and a loop, so we can indicate which page is currently active based on the title of the page:

<ul class="nav nav-pills">
  <li class="nav-item">
    <a class="nav-link active" href="home.php">Home</a>
  </li>
  <li class="nav-item">
    <a class="nav-link" href="insert_question.php">Insert Questions</a>
  </li>
  <li class="nav-item">
    <a class="nav-link" href="delete_question.php">Delete Questions</a>
  </li>
</ul>

The HTML code above uses Bootstrap classes. The class active will highlight the link using a "pill" shape. But, the active page will change, which is why we need to print menu using a loop as follows:
Inside of the print_html_header, the first thing you want to do is define an associative array for storing the titles and URLs of the pages you want to link to:
```
$items['home.php'] = "Home";
$items['insert_question.php'] = "Insert Question";		
```
Add menu items for Play Trivia, View Leader Board, Rank Questions, Insert Question, Delete Question and Logout. Look at home.php to see the file names of these scripts.
Define a string for storing the menu's HTML code:
```
$menu = '<ul class="nav nav-pills">';
```

Loop over the associative array:

foreach ($items as $key=>$value) {
  // See if the menu item is the active page
  // Create each menu item
}

Inside the loop, see if the $title of the page matches the menu item's value:
```
  $active = "";
  if ($value==$title) $active = "active";
```
Inside the loop, concatenate each menu item to the string. Notice how we use the variable $active, which will equal "active" if the $title==$value
```
  $menu .= '
  <li class="nav-item">
    <a class="nav-link '.$active.'" href="'.$key.'">'.$value.'</a>
  </li>
  ';
```
After the loop, close the menu's ul tag:
```
$menu .= '</ul>';
```
Once the menu code is finished, we can splice the $menu variable inside the container div and right before the h2 tag.
Finally, if the title of the page is "Login" then we don't want to print the menu at all. Set $menu = ""; and only add concatenate the menu items if the title of the page is NOT "Login"

Part 2: Fixing a security hole

Only admin users should be able to delete questions. If you login as bob with password 123, you will see that Bob can delete questions, which is a problem because Bob is not an admin. To fix this problem, we need to remember the usertype and username when a user logs in. Then, we want to block access to the delete_question.php script unless the usertype is admin. We also want to hide all links to the delete question script.

Open index.php, which is the login script, and modify the query to return both the password and usertype:
```
SELECT password, usertype FROM Users WHERE username='$submitted_username'
```
Note that after the query executes and we fetch the row, usertype will be store in $row[1]
If the password is verified, we want to set two additional session variables before redirecting our location to home.php
```
 $_SESSION['username'] = $submitted_username;
 $_SESSION['usertype'] = $row[1];
```
Open delete_question.php and modify the if statement that controls access to the page so that PHP will die if the usertype stored in the session is not admin
Open functions.php and modify the print_html_header function so that the delete question menu item is only added if the usertype stored in the session is admin. Note how we don't have to modify the HTML code, we are just controlling what links are added to the array.
Open home.php and consider that the navigation menu we just created can completely replace the hard-coded links of the home page. Instead, the home page should welcome the user by their username and point out the links in the navigation menu. Print a paragraph that says "Welcome ????? to the Trivia game, use the menu above to play or to add questions." But, replace ????? with the username stored in the session.
Test you changes to be sure they worked. When you login as Bob you should not see the delete question navigation link. Also, try to navigate directly to delete_question.php When you login as Alice, you should be able to see it and view the questions to delete.

Part 3: Improving the delete question script

Please only delete the "garbage" questions you create. We need real questions in the database for others to test this script.

Part A: Bootstrap-formatted output

To help decide which questions to delete an admin user should be able to see the choices and the correct answer. Currently, this script just displays the question text and a hyperlink to delete the question. Note that instead of using a form, we use a hyperlink and the $_GET variable to determine which question to delete.

Open delete_question.php and modify the query to return the following fields in order:
```
SELECT question, choice1, choice2, choice3, choice4, answer, id FROM Questions
```
Note that after the query executes and we fetch the row, the question text will be store in $row[0], the choices will be stored at index 1, 2, 3, and 4, the answer will be stored at index 5, and the id will be stored at index 6.

Instead of displaying the questions in a table, display them using the following Bootstrap HTML structure where you should replace the question, choices and id with the data fetched in each row.

<div class="card">
  <div class="card-block">
    <h4 class="card-title">question</h4>
  </div>
  <ul class="list-group list-group-flush">
    <li class="list-group-item ">choice1</li>
    <li class="list-group-item ">choice2</li>
    <li class="list-group-item active">choice3</li>
    <li class="list-group-item ">choice4</li>
  </ul>
  <div class="card-block">
    <a class="btn btn-danger" href="delete_question.php?id=id">Delete</a>
  </div>
</div>

Note you should use a for loop to loop from $i = 1 to 4. to print the choices, i.e. $row[1] to $row[4]. If the index $i equals the answer stored in $row[5], we need to print the active class to highlight the answer.

Part B: Using AJAX

Every time we click a delete hyperlink, note that delete_question.php is executed again and again on the server. Each time, we have to generate and transmit lengthy Bootstrap HTML code to display all the remaining questions. In this next part, we are going to replace the hyper links with JavaScript-controlled buttons that call a PHP script (via AJAX) This script will simply perform the delete. It only needs to know the question id. Then, we will use JavaScript to remove the question from the displayed page. Thus, the page never needs to reload. Note that you won't see other deleted pages.

Open delete_question.php and change the delete hyperlink to the following button:

echo '<input type="button" class="btn btn-danger btn-delete" id="q'.$row[6].'" value="Delete">';

Open js/script.js add event listeners to all the elements with class="btn-delete"

var btns = document.querySelectorAll(".btn-delete");

for (var i = 0; i < btns.length; i++) {
	btns[i].addEventListener("click",ajaxDelete);
}

Write a function to call a server-side script called "delete_question_json.php"

function ajaxDelete() {
	var id = this.id;
	alert(id);
	id = id.substring(1);
	alert(id);
	var request = new XMLHttpRequest();
	request.addEventListener("load", removeQuestion);
	request.open("POST", "delete_question_json.php", true);
	request.setRequestHeader("Content-Type", "application/json");
	request.send(id);
}

Write the event handler function that is called when the AJAX response is loaded

function removeQuestion() {
	var id = this.response;
	alert(id);
	var btn = document.querySelector("#"+id);
	var card = btn.parentNode.parentNode;
	card.parentNode.removeChild(card);
}

Create a new file called delete_question_json.php that performs the delete on the specified id and returns the HTML id of the button so we can identify which question card to remove.

<?
	require_once("functions.php");
	$id = file_get_contents('php://input');
	$mysqli = db_connect();				
	$mysqli->query("DELETE FROM Questions WHERE id=$id");
	$mysqli->close();
	echo "q".$id;
?>

DELIVERABLE

None. This lab is attendance only.