Lesson 6 Project Requirements CIS276DA – MySQL Database Objectives: 1. Demonstrate the need for and use of APIs. 2. Describe API choices and how Web applications can use MySQL. 3. Utilize PHP by creating Web interfaces for sample databases. (If you need help with creating script files, review the instructions from the Lesson 1 video Submitting Scripts and Query Results Demo.) 1. For your Lesson 6 assignment, you will need to follow the instructions in the three videos Loading WampServer for localhost, PHP Editors, Using an include .php file, and Using PHP – Syntax for MySQL. Be sure that you have copied each of the three examples in Section 9.2 to folders within the www folder for WampServer in the same manner as you copied the ushl folder for Section 9.1. You should also have created your own sampdb_pdo.php with your username and password and modified your php.ini file to include the folder where you have stored your individual sampdb_pdo.php file. Run them and become familiar with them by reviewing each subsection within 9.2, which discusses the PHP code. 2. To demonstrate your understanding of this lesson, you will need to create a “Lesson 6 Project” folder in which to save your assignment files. 3. Update the sampdb_pdo.php file with a comment showing that you have changed the username and password to work on your localhost. Save a copy of this file in your Lesson 6 Project folder: sampdb_pdo.php Using localhost, open the ushl folder to show the result of running index4 and then click ‘here’ to view the directory of the U.S. Historical League Members https://www.makersvault.com/tools/apache. Use “Save as” to save that file with the appropriate name and copy it to your Lesson 6 Project folder: L6_test_index4.htm The html page should look something like this: 4. Using localhost, open the gp folder to show the result of the running index and then click on “Enter or edit” to view the “Grade-Keeping Project – Score Entry” Web page. Use “Save as” to save that file with the name in your Lesson 6 Project folder: L6_test_gp_index.htm The html page should look something like this: 5. Create a copy of index5.php and name it index6.php for your Lesson 6. Edit this index6.php file to add another link similar to the dump_members.php link. Be sure to copy the tags
and
around your link and add the following just before the quiz:
You can list the presidents who were over 65 using here.
6. Make a copy of dump_members.php and name it list_over65.php. Edit the list_over65.php comments and title to match the list “Presidents over 65” and edit the MySQL information to use the command you created in Lesson 5 in the file proc_show_over_age_L5.sql, but simplify it so that you are only using the SELECT command with the age over 65. Note that in php, the continuation of the $stmt text within double quotes has a period in front of the next line, as shown in the member list SELECT, so your command needs to be very similar to this for the pres_age view: $stmt = "SELECT last_name, first_name, suffix, email," . " street, city, state, zip, phone FROM member ORDER BY last_name"; The above SELECT would be replaced with the following: SELECT first_name, last_name, birth, death FROM pres_age WHERE age > 65 ORDER BY age DESC; Test your results by renaming the index6.php file to index.php and verify your results. It should list 24 presidents. Then, save the Web page in your Lesson 6 Project Folder as the following: L6_test_over65.htm Also, put copies of the two new .php files that you tested in the Lesson 6 Project Folder: list_over65.php index6.php 7. To test out your Lesson 5 student scores view, go to the gp folder, create a new list_student_scores.php file similar to the above list_over65.php, and link to it using this command in your index.php just after the set of commands that load the score_entry.php:
List all student test and quiz scores
8. Edit the list_student_scores.php file in the gp folder to show a proper heading “Student’s scores”, and list the scores of the students by querying based on the view that you created in the Lesson 5 file student_scores_L5. HINT: vstudent is a view, and you can just SELECT * from vstudent Test your results and verify that you have listed several pages of students and their scores. Save the Web page in your Lesson 6 Project Folder as the following: L6_test_students.htm Also, put the new .php file and index from the gp folder in the Assignment 6 folder: list_student_scores.php index.php 9. To continue preparing for your At-Home Final Exam Project, update your MS Word document from Lesson 5 with any changes suggested by your instructor’s Lesson 5 feedback. Additionally, think about the process of checking out a video. You probably need another table that is linked to both the video and customer tables, including a date checked out and date returned (which would be null if not yet returned), plus the fee charged to rent the video. Update your document with notes on how you plan to link these, similar to the new pres_term table that you added for linking the election information in Lesson 4. Additionally, lay out at least one Web page with links to the functionality that your video store might require: a. Customer record: add, change, delete b. Video record: add, change, delete c. Check out/check in d. Charges for rental Save your MS Word document in your Lesson 6 Project folder as the following: Video_store_data_L6.docx (This is worth 2 points, based on completion only with feedback to improve the document. This will continue to be updated and used later in your At-Home Final Exam Project.)