Lab 7

PHP & MySQL

Creating tables; selecting, inserting and deleting data

---

Part 1: Create Tables

1. Open Notepad++ and create a new file called create_table.php and be sure it is saved in your lab7 folder.
2. Please do not use uppercase letters or spaces in filenames for this lab.
3. Add the following code to the file:

   
   <?
   if($_GET['key']!="XXX") {
   	die("Access denied");
   }
   
   $mysqli = new mysqli("localhost", "sienasel_sbxusr", "Sandbox@)!&", "sienasel_sandbox");			
   
   $sql = "CREATE TABLE ????? ( 
   					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) 
   				)";
   
   $mysqli->query($sql);
   $mysqli->close();
   ?>
   

4. Replace XXX with a secret code, i.e., some sequence of letters or numbers that you will remember. Do not use spaces in your secret code.
5. Replace ????? with a unique table name, e.g., "BreimersUsers" or "MyAwesomeTable"
6. Do not use spaces in your table name. You can use uppercase letters.
7. Save your file
8. Use WinSCP to connect to the server and upload your lab folder to the server
9. Type the following URL into your browser:
   http://s123456.sienasellbacks.com/lab7/create_table.php?key=XXX
   where "s123456" is your userid and XXX is the secret code you used.
10. This will create the table on the server. We will verify that it worked in the next step.
11. Notice how we use the URL parameters and the $_GET variable to prevent others from running this code. You must know the key for the script to run.

---

Part 2: Show Columns

1. In Notepad++, create a new file called show_columns.php and be sure it is saved in your lab7 folder.
2. Add the following code to the file:

   
   <?
   
   $sql = "SHOW COLUMNS FROM ?????";
   
   $mysqli = new mysqli("localhost", "sienasel_sbxusr", "Sandbox@)!&", "sienasel_sandbox");			
   $result = $mysqli->query($sql);
   $mysqli->close();
   
   echo '<table>';
   echo '<tr><th>field name</th><th>data type</th><th>null?</th><th>index</th><th>default value</th></tr>';
   while ($row = $result->fetch_row()) {
   	echo '<tr>';
   	foreach ($row as $value) {
   		echo '<td>'.$value.'</td>';
   	}
   	echo '</tr>';
   }
   echo '</table>';
   
   ?>
   

3. Replace ????? with your unique table name
4. Save your file
5. Use WinSCP to connect to the server and upload the file you created to your lab7 folder on the server.
6. Type the following URL into your browser:
   http://s123456.sienasellbacks.com/lab7/show_columns.php
   where "s123456" is your userid.
7. Verify that the column names and field information for the table you created is properly displayed.

---

Part 3: Adding Data

1. In Notepad++, create a new file called add_users.php and be sure it is saved in your lab7 folder.
2. Add the following code to the file:

   
   <?
   
   if($_GET['key']!="3587") {
   	die("Access denied");
   }
   
   $sql = "INSERT INTO ????? VALUES 
   ('alice', '".'$2y$10$rGSvwmvurEuoNgei6WSCCOs9A/WvXx0mwGGYrXIEJV4zlQo8vmGTq'."', 'admin', '20', '1257'), 
   ('bob', '".'$2y$10$HdGIIseolWHnE6/Zr5F8lOIAunKAvo.MXpXIxdLWuWHtTymDEPODW'."', 'normal', '15', '2165')";
   
   $mysqli = new mysqli("localhost", "sienasel_sbxusr", "Sandbox@)!&", "sienasel_sandbox");			
   $mysqli->query($sql);
   $mysqli->close();
   
   ?>
   

3. Replace ????? with your unique table name
4. Save your file
5. Use WinSCP to connect to the server and upload the file you created to your lab7 folder on the server.
6. Type the following URL into your browser:
   http://s123456.sienasellbacks.com/lab7/add_users.php?key=XXX
   where "s123456" is your userid and XXX is the secret code you used.
7. This manually inserts the users alice and bob into your table. We will verify that it worked in the next step.

---

Part 4: Show Data

1. In Notepad++, create a new file called show_data.php and be sure it is saved in your lab7 folder.
2. Add the following code to the file:

   
   <?
   
   $mysqli = new mysqli("localhost", "sienasel_sbxusr", "Sandbox@)!&", "sienasel_sandbox");			
   
   $result = $mysqli->query("SHOW COLUMNS FROM ?????");
   
   echo '<table>';
   echo '<tr>';
   while ($row = $result->fetch_row()) {
   	echo '<th>'.$row[0]."</th>";
   }
   echo '</tr>';
   
   $result->close();
   
   $result = $mysqli->query("SELECT * FROM ?????");
   
   while ($row = $result->fetch_row()) {
   	echo '<tr>';
   	foreach ($row as $value) {
   		echo '<td>'.$value.'</td>';
   	}
   	echo '</tr>';
   }
   echo '</table>';
   
   $result->close();
   
   $mysqli->close();
   
   ?>
   

3. Replace ????? with your unique table name
4. Save your file
5. Use WinSCP to connect to the server and upload the file you created to your lab7 folder on the server.
6. Type the following URL into your browser:
   http://s123456.sienasellbacks.com/lab7/show_data.php
   where "s123456" is your userid.
7. Verify that the two users are now in the table.

---

Part 5: Login

1. Right-click on the following link, select "Save Link As.." and save it as login.php
   login.txt
2. Replace ????? with your unique table name
3. Save your file
4. Use WinSCP to connect to the server and upload the file you created to your lab7 folder on the server.
5. Type the following URL into your browser:
   http://s123456.sienasellbacks.com/lab7/login.php
   where "s123456" is your userid.
6. Try to login. Ask you instructor for the passwords for alice and bob. Note that they are stored in the database using an encryption hash and the password_verify function hashes the submitted password before doing the comparison.
7. Your instructor will explain how password encryption works
8. Be sure to ask your instructor to explain why it is so important to check if submitted passwords are null.

---

Part 6: Verify Login

1. Right-click on the following link, select "Save Link As.." and save it as verify_login.php
   verify_login.txt
2. Save your file
3. Use WinSCP to connect to the server and upload the file you created to your lab7 folder on the server.
4. Type the following URL into your browser:
   http://s123456.sienasellbacks.com/lab7/verify_login.php
   where "s123456" is your userid.
5. Verify that this page is only displayed if you properly logged in using the login script, otherwise you will get an access denied message.

---

Part 7: Logout

1. In Notepad++, create a new file called logout.php and be sure it is saved in your lab7 folder.
2. Add the following code to the file:

   
   <?
   
   session_start();
   
   session_destroy();
   
   unset($_SESSION);
   
   die("Session Destroyed");
   
   
   ?>
   

3. Save your file
4. Use WinSCP to connect to the server and upload the file you created to your lab7 folder on the server.
5. Type the following URL into your browser:
   http://s123456.sienasellbacks.com/lab7/logout.php
   where "s123456" is your userid.
6. Note that an application just needs to hyperlink to this script to logout. And, typically, a logout script will then redirect the user to the login page.
7. Add the code above and test your login, logout and verfify scripts to be sure your login is working.
8. Hints:
   • Here is the PHP function to redirect to your login page:
     header("Location: http://s123456.sienasellbacks.com/lab7/login.php"); where "s123456" is your userid.
     
   • Redirects, i.e., the PHP header function should always be the 2nd to last line of code. The last line should be the die function.

---

Part 8: Insert Users

1. Right-click on the following link, select "Save Link As.." and save it as insert_user.php
   insert_user.txt
2. Save your file
3. Use WinSCP to connect to the server and upload the file you created to your lab7 folder on the server.
4. Type the following URL into your browser:
   http://s123456.sienasellbacks.com/lab7/insert_user.php
   where "s123456" is your userid.
5. This page should only be displayed if you properly logged in using the login script, otherwise you will get an access denied message.
6. Use the insert user script and the show data script to verify that you are actually inserting users to your table.
7. Notice that this script only adds the username and password.
8. Notice that the password is encrypted using a hash function and the PASSWORD_BCRYPT function.
9. Modify this script so that it also adds the usertype, games and points.
10. Hints:
    • Add three new input type="text" tags to the form
    • Add PHP code retrieve the values for the usertype, games and points using the $_POST variable.
    • Modify the query to also insert usertype, games and points.

---

Part 9: Delete User

1. Right-click on the following link, select "Save Link As.." and save it as delete_user.php
   delete_user.txt
2. Save your file
3. Use WinSCP to connect to the server and upload the file you created to your lab7 folder on the server.
4. Type the following URL into your browser:
   http://s123456.sienasellbacks.com/lab7/delete_user.php
   where "s123456" is your userid.
5. This page should only be displayed if you properly logged in using the login script, otherwise you will get an access denied message.
6. Use the delete user script and the show data script to verify that you are actually deleting users to your table.
7. Modify this script so you do not have to type the username. Instead, generate select and option tags, so the username can be selected from a drop-down menu.
8. Hints:
   • You will replace the username <input type="text" name="username"> tag with a block of PHP code that will dynamically generate the HTML tags for a drop-down menu.
   • Your block of PHP code must reconnect to the server and execute a simply query (SELECT username FROM ?????) to get all the usernames.
   • Use show_data.php as a model. Generating a drop-down menu is similar to generating the column headers but instead of creating tr and th tags you will generate select and option where $row[0] is the inner value of the option tag.
   • select tag the attribute name="username"

---

Part 10: Integration - the fun part