Category: 20221_itp303

To-do for today’s class

• Download starter file: lect18-detail-insert/ (Google Drive)
• Refer to song database diagram:
  

  

Slides

Link to download PDF version.

Completed files

• 12pm: https://github.com/nayeonk/itp303-spring-2022-12pm/tree/master/lect18-detail-insert
• 4pm: https://github.com/nayeonk/itp303-spring-2022-4pm/tree/master/lect18-detail-insert

When & Where: Online, through Gradescope.com can start the exam between Thu, 04/07 12:00 pm to Fri, 04/08 6:00 pm.

Length & Format: 80 minutes, online exam.

Topics: Database Basics, Database Design (Lecture #13) to CRUD Part 2 (Lecture #19)

Types of Questions: 

• Multiple choice – select one answer
• Multiple choice – select all that apply
• True/False
• Fill in the blank
• Short Code

How to take the exam:

• Log in to gradescope.com. You will need to create an account if you haven’t already with your USC email address.
• Once you have logged in, you should see “Midterm: Server-side Exam”
• Have questions during the exam? Because you are allowed to take the exam at any time, we will be unable to answer questions you have during the exam like you would in a physical setting. Make your best-educated guess and for really uncertain questions make a note about it separately and email it to the instructor after the exam.

Important Notes:

• This is an open-book exam. You may use notes, slides, and online resources. The only thing you CANNOT do is collaborate with any human beings on this exam. Do not discuss with anyone about the exam. Your exam must be completed on your own, by yourself.
  • Any suspicion of collaboration will be reported to SJACS.
• I highly recommend you create one single reference sheet instead of scouring your notes/slides/etc during the exam. This is a timed test so you want to limit time wasted on looking stuff up.
• You will be asked to design a database and create an EER diagram (like the Simple Film Database lab). You can hand-draw one up, take a photo of it and upload it, or use Workbench to create one.

Tips:

• Anything that appeared on the lecture or the slides is fair game. The best way to study for this exam is to review the code from lectures and your labs and assignments and review what each line of the code does. Rewriting some of the code is very helpful.
• Review the links under Resources/Helpful Reading under each lecture notes to get a deeper understanding of topic(s) taught in that lecture.

Study Guide:

• Midterm II Study Guide

Short Coding Sample questions:

• Sample questions
• Solutions (Pro tip: try the questions without seeing the solutions first)
• There is no full practice exam for this exam.

Overview

For this assignment, you will complete a CRUD application using your DVD database.

Sample

Note: samples below don’t always meet all the requirements. Make sure to follow the requirements rather than solely relying on the samples.

http://304.itpwebdev.com/~zune/assignment_07/index.php

Caching

Browsers like to cache previous versions of your web files. After you upload the completed files to the server and test them, you might notice that your changes are not showing. For example, add a new DVD and search for it. If the new DVD does not show up in the search_results.php, your browser may be caching a previous version of this file.

If this happens, open MySQL Workbench and double-check that your database is accurate. Then, try to hard refresh the page by pressing Shift on your keyboard while clicking the refresh icon in the browser. This will force the browser to show the late version of the file.

Requirements

HTML/CSS

1. You may use the given starter HTML files (styled with Bootstrap) or create your own HTML files for practice.

CRUD Workflow

1. You can use any workflow, as long as you provide full CRUD functionality

Main Page (index.php)

1. Page title
2. Links to Search Form and Add Form

Search Form & Search Results

1. Same requirements as in Assignment 8.
2. You may re-use your Assignment 8 files.
3. Each DVD title should be a link to Detail Page (completed in Lab 10).
4. Additionally, each DVD Title on Search Results page should have a delete button where users can click to delete the title.

Detail Page

1. Same requirements as in Lab 10.
2. You may re-use your Lab 10 files.

Add Form

1. Check for any errors and missing data.
   1. Output appropriate error messages.
2. DVD Title text field (indicate as required field).
3. Following drop-downs dynamically populated with data from the database.
   1. Genre,
   2. Rating,
   3. Label,
   4. Format,
   5. Sound,
   6. Use primary keys for value attributes,
   7. Display the name (genre, rating, etc) as a drop-down option,
   8. Include Select One (empty/no selection) as the first option in each drop-down.
4. Award Text Area.
5. Release Date Date Input (<input type='date'...>).

Add Confirmation

1. All PHP & MySQL errors are handled and displayed.
   1. Including missing user input for required fields.
2. Add DVD to the database using prepared statements.
   1. Only DVD Title is a required field.
   2. Other fields should have user-selected values or NULL.
3. Display the following message if DVD is added successfully in green:
   1. [DVD Title] was successfully added.
4. Note: When searching for newly added records, you might have to do hard-refresh (Control/Command + R) to see them.

Delete & Update Functionality

1. Users need to be able to update and delete each DVD record.
   1. Show confirmation popup before actually deleting the record.

Delete Page

1. Check for any errors & missing data.
   1. Output appropriate error messages.
2. Delete DVD record from the database.
3. Display the following message if DVD is deleted successfully:
   1. [DVD Title] was successfully deleted.

Edit Form

1. Check for any errors & missing data.
   1. Output appropriate error messages.
2. DVD Title text field (indicate as a required field).
3. Following drop-downs dynamically populated with data from the database.
   1. Genre,
   2. Rating,
   3. Label,
   4. Format,
   5. Sound,
   6. User primary keys for value attributes
   7. Display the name (genre, rating, etc) as drop-down option,
   8. Include Select One as first option in each drop-down.
4. Award Text Area.
5. Release Date Date Input (<input type=”date”>…)
6. DVD Title ID hidden field with value set to current DVD’s primary key.
7. Only DVD Title is a required field for user input. All other fields are optional.
8. All form fields need to be pre-populated or have current DVD’s data pre-selected.
   1. For example:
      1. Pre-populate DVD Title text-field with DVD’s current title.
      2. Pre-select drop-downs with DVD’s current drop-down value.
9. Submit & Reset buttons.

Edit Confirmation Page

1. Check for any errors & missing data.
   1. Output appropriate error messages.
2. Update the DVD record in the database using prepared statements.
3. Display the following message if DVD is updated successfully:
   1. [DVD Title] was successfully updated.
4. Provide a link to the details page for edited DVD record.

Submission/Upload to the server

Please follow the submission requirements below carefully. You will be deducted points for not following submission requirements to the teeth.

1. Open a browser and go to https://303.itpwebdev.com/cpanel (it will redirect you to https://54-148-150-30.cprapid.com:2083/).
2. Login with your username and password (this password does not sync with your USC NETID password. You set this password in Lab 1. If you don’t remember your password, post on EdStem and a course staff member will reset it for you).
3. Scroll down to Files section and click on File Manager. You will see a list of folders and files like below.
   
4. Double click to navigate inside the public_html folder.
5. Create a new folder inside the public_html folder by clicking on the +Folder button on the top left.
   
6. Name the folder assignment09. Double click it to navigate inside this folder.
7. Click on “Upload” to upload all assignment files to this folder.
   
8. Drag and drop all assignment files to upload into this folder.
9. In your browser, go to http://303.itpwebdev.com/~yourusername/assignment09/index.php to check that the assignment file has been uploaded correctly.
10. One last thing. In your computer, open up student_page.html that you created in Lab 2. Add a link to this assignment to student_page.html under the heading “Assignments” so that the graders can easily access your completed assignment.
11. Re-upload the updated student_page.html to the itpwebdev server via cPanel -> Files -> File Manager. If it asks you want to overwrite the previous file, click Yes.
12. If all the above is completed, go to your student page at http://303.itpwebdev.com/~yourusername/student_page.html and check that the link to this assignment is there. The TAs/graders use this link to access your assignment so make sure this is working! Below is a screenshot sample of what it should look like.
    
13. (Optional) If you are having trouble uploading to the server and cannot upload on time, add all files for this assignment in a folder, compress it as a .zip file and upload it to Blackboard. On Blackboard, go to Assignments -> Assignment 9: CRUD DVD App. You will get some points deducted for not uploading correctly but at least you will not get a zero.

Overview

For this lab, you will create the detail pages using your DVD database.

Sample

Note: samples below don’t always meet all the requirements. Make sure to follow the requirements rather than solely relying on the samples.

http://303.itpwebdev.com/~nayeon/dvd-details/

Requirements

HTML/CSS

1. You may use the given starter HTML files (styled with Bootstrap) or create your own HTML files for practice.

Search Form & Search Results

1. Same requirements as in Assignment 8.
2. You may re-use your Assignment 8 files.
3. Each DVD title should be a link to Detail Page.
4. If you took an extension for A8 and do not have the search pages complete, hard code a song track result in the search_result.php and link that result to the detail page.

Detail Page

1. Check for any errors & missing data.
   1. Output appropriate error messages.
2. Display all information corresponding to the DVD.
   1. Title,
   2. Release Date,
   3. Award,
   4. Label (name, not primary ey),
   5. Sound (name, not primary key),
   6. Genre (name, not primary key),
   7. Rating (name, not primary key),
   8. Format (name, not primary key).
3. Link for users to go back to search results. OK if search query is not saved.

Submission/Upload to the server

Please follow the submission requirements below carefully. You will be deducted points for not following submission requirements to the teeth.

1. Open a browser and go to https://303.itpwebdev.com/cpanel (it will redirect you to https://54-148-150-30.cprapid.com:2083/).
2. Login with your username and password (this password does not sync with your USC NETID password. You set this password in Lab 1. If you don’t remember your password, post on EdStem and a course staff member will reset it for you).
3. Scroll down to Files section and click on File Manager. You will see a list of folders and files like below.
   
4. Double click to navigate inside the public_html folder.
5. Create a new folder inside the public_html folder by clicking on the +Folder button on the top left.
   
6. Name the folder lab10. Double click it to navigate inside this folder.
7. Click on “Upload” to upload all assignment files to this folder.
   
8. Drag and drop all assignment files to upload into this folder.
9. In your browser, go to http://303.itpwebdev.com/~yourusername/lab10/search_form.php to check that the assignment file has been uploaded correctly.
10. One last thing. In your computer, open up student_page.html that you created in Lab 2. Add a link to this assignment to student_page.html under the heading “Assignments” so that the graders can easily access your completed assignment.
11. Re-upload the updated student_page.html to the itpwebdev server via cPanel -> Files -> File Manager. If it asks you want to overwrite the previous file, click Yes.
12. If all the above is completed, go to your student page at http://303.itpwebdev.com/~yourusername/student_page.html and check that the link to this assignment is there. The TAs/graders use this link to access your assignment so make sure this is working! Below is a screenshot sample of what it should look like.
    
13. (Optional) If you are having trouble uploading to the server and cannot upload on time, add all files for this assignment in a folder, compress it as a .zip file and upload it to Blackboard. On Blackboard, go to Assignments/Labs -> Lab 10: DVD Detail Pages. You will get some points deducted for not uploading correctly but at least you will not get a zero.

To-do for today’s class

• Download starter file: lect17-php-mysqli/ (Google Drive)
• Refer to the song database diagram:
  

  

Helpful Readings

• PHP MySQLi documentation: http://php.net/manual/en/book.mysqli.php

Slides

Link to download PDF version.

Lecture Files

• 12pm: https://github.com/nayeonk/itp303-spring-2022-12pm/tree/master/lect17-php-mysqli/
• 4pm: https://github.com/nayeonk/itp303-spring-2022-4pm/tree/master/lect17-php-mysqli/

Overview

This assignment will help you get more practice with PHP syntax, form variables, and conditional statements

Sample

See working sample here: http://303.itpwebdev.com/~ta303/samples/lab-php-form/form.php

If not sure how to address certain edge cases, try out the sample and match your output to what the same outputs.

Requirements

1. Download starter file.
2. Form Page (form.php)
   1. Fill out <form> tag’s action and method attributes appropriately. 
   2. When this form is submitted, it will run submit_form.php.
3. Form Output / Submission Page (submit_form.php)
   1. Perform all validation as needed.
      1. If any of the fields are not filled out (except Flavor), display “Not provided” message in red.
      2. Tip: The provided HTML pages are using Bootstrap. Therefore, you can use CSS class text-danger for red text and the class text-success for green text.
   2. Show a message with current date & time in following format:
      1. This form was submitted on [weekday], [month] [day], [year] at [time].
         1. Example: This form was submitted on Sunday, September 17, 2017 at 03:15:14 PM.
         2. For the hour, use 12-hour format (not 24) with leading zeros.
      2. Use Los Angeles timezone.
      3. Refer to the PHP official documentation and search for the date function to see how to get the formatting exactly right.
   3. Display following values in submit_form.php after the form is submitted:
      

Submission/Upload to the server

Please follow the submission requirements below carefully. You will be deducted points for not following submission requirements to the teeth.

1. Open a browser and go to https://303.itpwebdev.com/cpanel (it will redirect you to https://54-148-150-30.cprapid.com:2083/).
2. Login with your username and password (this password does not sync with your USC NETID password. You set this password in Lab 1. If you don’t remember your password, post on EdStem and a course staff member will reset it for you).
3. Scroll down to Files section and click on File Manager. You will see a list of folders and files like below.
   
4. Double click to navigate inside the public_html folder.
5. Create a new folder inside the public_html folder by clicking on the +Folder button on the top left.
   
6. Name the folder lab09. Double click it to navigate inside this folder.
7. Click on “Upload” to upload all lab files to this folder.
   
8. Drag and drop all lab files to upload into this folder.
9. In your browser, go to http://303.itpwebdev.com/~yourusername/lab09/form.php to check that the assignment file has been uploaded correctly.
10. One last thing. In your computer, open up student_page.html that you created in Lab 2. Add a link to this assignment to student_page.html under the heading “Labs” so that the graders can easily access your completed assignment.
11. Re-upload the updated student_page.html to the itpwebdev server via cPanel -> Files -> File Manager. If it asks you want to overwrite the previous file, click Yes.
12. If all the above is completed, go to your student page at http://303.itpwebdev.com/~yourusername/student_page.html and check that the link to this assignment is there. The TAs/graders use this link to access your assignment so make sure this is working! Below is a screenshot sample of what it should look like.
    
13. (Optional) If you are having trouble uploading to the server and cannot upload on time, add all files for this assignment in a folder, compress it as a .zip file and upload it to Blackboard. On Blackboard, go to Assignments/Labs -> Lab 9: PHP Form Output. You will get some points deducted for not uploading correctly but at least you will not get a zero.

To-do for today’s class

1. Download starter file: lect16-intro-php/ (Google Drive)
2. Download MAMP (for both Mac and Windows): https://www.mamp.info/en/downloads/
   1. Both MAMP PRO and MAMP may be downloaded but we will only be using MAMP (the free version). MAMP PRO is not required for this class.
3. After opening MAMP, select PHP version 7.4.* (or any version 7) on the bottom of the main panel.
   
4. Click on the “Preferences” gear icon on the top left of the main panel.
   
5. In the “General” tab, select the dropdown next to PHP-cache and select “off”. This will prevent MAMP from caching and allow you to see instant updates.
   
6. Click on the “Server” tab.
7. Change the Document root folder to the folder that contains all your ITP 303 files. Click on “Choose” button to navigate to the folder and select it.
   
8. Locate the php.ini file, which is a configuration file that allows you to configure many aspects of PHP.
   1. Mac: /Applications/MAMP/bin/php/php{version_num}/conf/php.ini
   2. Windows: C://MAMP/conf/php{version_num}/php.ini
9. Search for display_errors. You will see a few results. Look for a statement that reads display_errors = Off, which is around line 472.
10. Change this line so that it reads display_errors = On. Note the capital “O” on On. This will make PHP show errors on the browser while you write PHP. This is super helpful during development.
11. You will need to restart the server if you already started the server. If you have never started the server before, click the “Start” icon on the top right of the main panel.
    
12. Give it a few minutes to boot a simulated web server. You will notice a browser tab open up with the URL set to localhost.
13. Refer to the official PHP documentation here: http://php.net

Slides

Link to download PDF version.

Resources

• PHP date() function: http://php.net/manual/en/function.date.php
• PHP $_SERVER: http://php.net/manual/en/reserved.variables.server.php

Completed files

• 12pm: https://github.com/nayeonk/itp303-spring-2022-12pm/tree/master/lect16-intro-php/
• 4pm: https://github.com/nayeonk/itp303-spring-2022-4pm/tree/master/lect16-intro-php/

Overview

For this assignment, you will create search form and search results page for DVD database. All data and results should be dynamic and come directly from the database.

Sample

Note: samples below don’t always meet all the requirements. Make sure to follow the requirements rather than solely relying on the samples.

http://303.itpwebdev.com/~nayeon/a08-dvd-search/search_form.php

Requirements

DVD Search Form Page

1. Download starter files: a08-dvd-search. You are welcome to write your own HTML if you wish to get some practice.
2. Check for any database errors as shown in lecture (after establishing a connection to the database and then after submitting the SQL to the server).
3. DVD Title text field.
4. Following drop-downs dynamically populated with data from the database.
   1. Genre,
   2. Rating,
   3. Label,
   4. Format,
   5. Sound,
   6. Use primary keys for value attributes
   7. Display the name (genre, rating, etc.) as drop-down options,
   8. Include All as the first option in each drop-down.
5. Award radio buttons:
   1. Any (records with or without awards),
   2. Yes (only records with awards),
   3. No (only records without awards),
6. Release date:
   1. Allow users to choose a date range (from and to)
   2. Users can fill out one, both or neither fields.
7. Submit & Reset buttons.

DVD Search Results Page

1. Remember to check for any database errors as shown in lecture.
2. Search Form should submit to this page.
3. Include “Back to Form” link for users to submit another form.
4. Perform all validation as needed.
   1. Page should still work even if any of the parameters are missing.
5. Based on user input, query the database and display correct results.
6. Show how many results search query produced.
   1. For example: Showing 7886 result(s).
7. Display following fields in tabular or table format:
   1. DVD title,
   2. Release Date,
      1. Only show DVD results that match the date range user has given.
      2. If no date fields were submitted, it is ok to show DVDs that do not have a release date.
   3. Genre (name, not primary key),
   4. Rating (name, not primary key).
8. Tip: Before writing the SQL statement to search, var_dump out the variables you are getting passed through from the Search Form. This will help you write out the SQL statement.

Hints

• GET or POST?
  • Does this form handle any sensitive data?
  • Is this data being added to the database?
• Radio buttons also use the HTML attribute value to determine which radio button was selected.
• Refer to the DVD database diagram to ensure you are using the correct columns names.
  

Submission/Upload to the server

Please follow the submission requirements below carefully. You will be deducted points for not following submission requirements to the teeth.

1. Open a browser and go to https://303.itpwebdev.com/cpanel (it will redirect you to https://54-148-150-30.cprapid.com:2083/).
2. Login with your username and password (this password does not sync with your USC NETID password. You set this password in Lab 1. If you don’t remember your password, post on EdStem and a course staff member will reset it for you).
3. Scroll down to Files section and click on File Manager. You will see a list of folders and files like below.
   
4. Double click to navigate inside the public_html folder.
5. Create a new folder inside the public_html folder by clicking on the +Folder button on the top left.
   
6. Name the folder assignment08. Double click it to navigate inside this folder.
7. Click on “Upload” to upload all assignment files to this folder.
   
8. Drag and drop all assignment files to upload into this folder.
9. In your browser, go to http://303.itpwebdev.com/~yourusername/assignment08/search_form.php to check that the assignment file has been uploaded correctly.
10. One last thing. In your computer, open up student_page.html that you created in Lab 2. Add a link to this assignment to student_page.html under the heading “Assignments” so that the graders can easily access your completed assignment.
11. Re-upload the updated student_page.html to the itpwebdev server via cPanel -> Files -> File Manager. If it asks you want to overwrite the previous file, click Yes.
12. If all the above is completed, go to your student page at http://303.itpwebdev.com/~yourusername/student_page.html and check that the link to this assignment is there. The TAs/graders use this link to access your assignment so make sure this is working! Below is a screenshot sample of what it should look like.
    
13. (Optional) If you are having trouble uploading to the server and cannot upload on time, add all files for this assignment in a folder, compress it as a .zip file and upload it to Blackboard. On Blackboard, go to Assignments -> Assignment 8: DVD Search Pages. You will get some points deducted for not uploading correctly but at least you will not get a zero.

Lecture recording

Class was not held in person on this day. See edstem announcement for details.

Zoom link: https://usc.zoom.us/rec/share/pniCYhoiOtOH_BdVwcW57NCXKV58YNOZettE5x0862zH33Ee1UBCjo92QWk7eqgw.zWJ7GOPl_9fS7yy4?startTime=1646726882000

To-do for today’s class

• Open up MySQL Workbench to write some more SQL statements.

Slides

Link to download PDF version.

Completed Files

• 12pm: https://github.com/nayeonk/itp303-spring-2022-12pm/tree/master/lect15/
• 4pm: https://github.com/nayeonk/itp303-spring-2022-4pm/tree/master/lect15/

Overview

This lab helps you practice more SQL statements that manipulate the database, such as insert, update, and deleting records. You will write five SQL statements against the Song database.

Requirements

Create one .sql file for all five SQL statements. Use comments to write the prompt above each SQL statement.

Part 1 – Song Database

1. Create a view mpeg_tracks that displays all tracks with MPEG audio file format. Display track name (alias: track_name) artist name (alias: artist_name), composer, album title (alias: album_title), and media type (alias: media_type). Sort results in alphabetical order by track name.
   

   

2. Add a track below to the database:
   1. Track Title: The Ocean
   2. Album: The Song Remains The Same (Disc 1)
   3. Artist: Led Zeppelin
   4. Media Type: MPEG audio file
   5. Genre: Rock
   6. Composer: John Bonham/John Paul Jones/Robert Plant
   7. Milliseconds: 248000
   8. Bytes: 7990000
   9. Unit Price: 0.99
3. Make the following changes to the track added above:
   New Bytes: 8998765
   New Unit Price: 1.99
4. Delete track named “20 Flight Rock” composed by Ned Fairchild from the database.
5. Display how many tracks there are for each album. Show album ID, album title (alias: album_title), and track count (alias: track_count).
   1. Note: some of your counts might be different due to added or deleted records during in-class exercises.
      

      

Submission

When complete, you must upload the completed .sql file to Blackboard. Go to Assignments/Labs -> Lab 8: More SQL Statements and upload the file.