Lab 10

To make progress on Project 3 and to understand...

1. PHP Sessions and their role in implementing user authentication
2. How to implement a login and logout
3. Techniques for changing menus and content depending on which user is logged in

To share PHP code, I have uploaded the scripts as .txt files. After you download these files be sure to rename them so that the file extension is .php

1. In your project3 folder, rename your functions.php to functions_old.php
2. Download this updated functions.php and and save it to your project3 folder
3. Download this new functions_database.php and save it to your project3 folder
4. Open the new functions.php in Brackets
5. Notice that it requires functions_database.php which allows us to better organize our functions based on their roles
6. Notice that there are now 3 arrays used to generate three different menus for three different types of users
7. What are the three types of users? You don't have to write down the answer, but be prepared to answer if asked
8. Notice that the select_menu function will return one of the three menus based on two session variables:
   • $_SESSION['admin'] is set to true if an administrator is logged in or it is null/false
   • $_SESSION['uid'] is set to the uid of the logged in user or it is null/false
9. Notice that select_menu is called each time a page is generated, which allows the main menu to change depending on which kind of user is logged in

Key Task: Create files for each of the PHP scripts in the three menus. Note that logout.php appears twice and that show_table_data.php and show_table_columns.php are each one script, but are linked twice with different table_names.

1. Open functions_database.php in Brackets
2. Modify the db_connect function to connect the correct database
   • breimern corresponds to breimer.net
   • sienasel corresponds to sienasellbacks.com
3. Remember you have to un-comment the correct line
4. Notice a new function called run_query which properly connects to the database, uses the die function to display an error message if the query fails, and properly closes the database connection. Using this function will help you avoid errors and will help you debug.
5. Notice there are functions that will help you create new tables and then display the table data and the column information.
6. What are the names of these functions? You don't have to write down the answer, but be prepared to answer if asked

We are going to rebuild our tables to clear out all the test data and to be sure everyone has the correct columns.

Remember to always replace userid with your own userid. Thus, you will create your own tables. Otherwise, many students will be overwriting the same tables.

Dropping Tables

1. Download this new drop_table.php and save it to your project3 folder
2. Examine the code and then use it to drop your existing userid_courses table
3. You will have to upload this script and the call it using the correct URL parameter:
   http://www.breimer.net/userid/projects/project3/drop_table.php?table_name=userid_courses or
   http://www.sienasellbacks.com/userid/projects/project3/drop_table.php?table_name=userid_courses or
   

Creating the new courses table

1. Download create_courses_table.php and save it to your project3 folder
2. Examine the code and then use it to re-create your new userid_courses table
3. You will have to upload this script and the call it using the correct URL

Creating the users table

1. Download create_users_table.php and save it to your project3 folder
2. Examine the code and then use it to re-create your new userid_users table
3. You will have to upload this script and the call it using the correct URL

Add sample rows to the tables

1. Download add_courses.php and save it to your project3 folder
2. Examine the code and then use it to add courses to your courses table.
3. You will have to upload this script and the call it using the correct URL
4. Download add_users.php and save it to your project3 folder
5. Examine the code and then use it to add users to your user table.
6. You will have to upload this script and the call it using the correct URL

The user profiles are meant to be public. New visitors can do three things: join, login or view the users' profiles.

Implement the show_users.php script as follows:

1. Generate a page by requiring "functions.php" and using the make_basic_page function.
2. Create a $content string and pass it to the make_basic_page function. The page name should be "Users" as this is the name of the menu item for this particular page.
3. Use the run_query function to get the result of the following query:
   SELECT uid, first, last, major, col FROM userid_users
4. Use the fetch_assoc method on the result pointer to get all the rows
5. Use a while loop and generate hyperlinks to user_profile.php?uid=x where x is the uid of a user
6. Use the first, last, major and col (college/university) as the hyperlink text
7. See show_users solution to see how the output can be formatted.
8. Help will be given in lab if you are lost.

The user profiles should include all the information in the users table. The user_profile.php script takes the uid as a parameter:
Example: user_profile.php?uid=3

1. Require "functions.php" and use the make_basic_page function.
2. The page name should be "Profile" as this will be the name of the menu item when a user logs in.
3. Get the uid from the URL, i.e., $uid = $_GET['uid']
4. Use the run_query function get the result of the following query:
   SELECT * FROM userid_users WHERE uid='$uid'
5. Use the fetch_assoc method on the result pointer to get the single row return by the query.
6. Store this row as a variable called user_data
7. The user_data associative array can be used to slice in any user specific data
8. Example: $content = '<h1>'.$user_data['first'].' '.$user_data['last'].'</h1>';
9. You can also use the helpful function make_card to put data inside of a card.
10. Example: $content .= make_card("Biography",$user_data['bio']);
11. See user_profile solution
12. At this point, you do not have to include the course information.

The login will create and process a basic form. It fetches a user's stored password (stored in the database) and compares it to the password submitted in the form. It will set a session variable with the user's id (uid) that the server will remember, so that when a logged in user visits other pages, we can generate content for that specific user.

1. Download this start file login.php and save it to your project3 folder
2. This file includes comments that will guide you.
3. See login solution to understand the structure of the HTML form.

Details:

1. Use method post
2. If you name the an input element "pwd" you can fetch it using $_POST['pwd]
3. You have to use the post to fetch the submitted_email and submitted_pwd
4. The following query will get the stored uid and password based on the submitted email:
   SELECT uid, pwd FROM userid_users WHERE email='$submitted_email'
5. To get data from a query, you should use fetch_assoc on the query results pointer
6. If you fetch the query result and store it as $row, you can get the stored uid as follows:
   $stored_uid = $row['uid']
7. You must also get the stored_pwd
8. To set or get a session variable, you must always call session_start()
9. You can keep track of the logged in user by using a session variable: $_SESSION['uid'] = $stored_uid;
10. You only want to set $_SESSION['uid'] if the submitted_pwd matches the stored_pwd