Month: March 2022

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.

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

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.

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.

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/

Recording

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

Link to recording:
https://usc.zoom.us/rec/play/xWvs-0P-v5gcbYgnM-KPuLLCpjmOsnn1MsUAMRjXpFQU7Jzb0_O9Q1mnEFWVpxhKgu8c2tZsXolOIGY.j1Fa_IxLbbgdkwhj

To-do for today’s class

• Download song.sql.
• Login to cPanel and create a new database named username_song_db
• Download and install the latest MySQL Workbench if you haven’t already: https://dev.mysql.com/downloads/workbench/.
• Refer to below EER diagram of Song Database.

Slides

Link to download PDF version of slides.

Completed Files

• 4pm: https://github.com/nayeonk/itp303-fall-2021-4pm/tree/main/lect14

To-do for today’s class

• Save this Google Drive folder in the itp104 folder that you are saving all the lecture material in:
  https://drive.google.com/drive/folders/1Ah2R9ZephsEEQuprb_jq9Zn6Oh0QBIJv?usp=sharing
  Click on folder name and then click Download to get a .zip of this folder.

Recording

This lecture was not live. Watch the recording here (from Spring 2021): https://www.youtube.com/watch?v=QSZGJEFNaR4

Note: recording refers to a dropbox link for the starter code, but this semester we have been using google drive. Use the google drive link above instead.

Slides

View Slides
Link to download PDF version.

Completed Files

Completed files are stored in a GitHub repository. Links below will take you to a list of file(s) from today’s lecture. You can view the code via the browser through GitHub’s UI or clone/download the repo to open the code from your computer.

• https://github.com/nayeonk/itp104-spring-2022/tree/master/lect08

Midterm Exam Information

When: Exam will open on Wednesday, 03/09 at 5:00pm Pacific Time and close on Friday, 03/11 at 5:00pm Pacific time.

Length: 80 minutes to complete the exam.

Where: Gradescope

Types of questions:

• True/False
• Multiple Choice
• Fill in the blank
• Short Coding

Topics: Topics covered in lecture from Week 1, Intro to HTML to Week 8. Meta Tags and Responsive Web Design

What to do BEFORE the exam:

1. Check your email for an invite to Gradescope. You will need to create a new account if you have never used Gradescope before.
2. Plan ahead to do your best to be in a quiet place during the time you choose to take the exam.
3. Take the practice exam to get familiar with taking an exam on Gradescope. The practice exam is about half the length of the actual exam.
   1. Practice exam does not count toward your grade, it is purely for practice to help you get familiar with Gradescope and the types of questions asked in this exam.
   2. Practice exam is timed, at 40 minutes (half the actual exam time). You can only submit the exam once (a limiation of Gradescope, unfortunately) so take it when you are ready to take the exam.
   3. Solutions to the practice exam can be found here.

During the exam:

1. Log in to Gradescope.
2. Click on ITP 104 Spring 2022 section and you will see the Midterm Exam during the exam period.
3. Click begin to start the exam. You will see a timer running on the top right corner.
4. If you get kicked out of the exam for whatever reason, you can log back in and continue the exam by clicking “Resubmit.” Note the timer continues to run if you leave the exam so get back to the exam asap.

Important Notes:

• This is an open book exam. You may use notes, slides, and the Internet. However, I highly recommend you still memorize and/or prepare a “cheat sheet” with common HTML tags and CSS properties to save precious time on the exam.
• The exam closes promptly at 5pm. This means you must start the exam at the latest at 3:40pm on Friday to get the full 1h and 20 minute for the exam.
• This is an individual exam – do not collaborate with others.

What you should know for the exam

Note: this is not meant to be an exhaustive list. Anything discussed in lecture can appear on the midterm exam. Refer to your lecture notes and assignments for a comprehensive review.

HTML basics

• The basic HTML skeleton
• Concept of opening and closing tags
• What is a tag and what is an attribute? And what do they look like?
• Common HTML tags and their function
  • <p>, <div>, <span>, <strong>, <em>, <h1>, <h2>, <h3>, <ul>, <li>, <ol>, <img>, <a>, <hr>, <table>, <form>, <label>, <select>, <option>, <input>, <textarea> and any other tags discussed in lecture

CSS Style Properties and Stylesheets

• What is CSS? vs HTML?
• Common CSS properties and their values
  • color, font-size, text-align, margin, padding, border, background-color, background-image, visibility, opacity, display, float, flex, position, and any other properties discussed in lecture
• Inline styles vs Stylesheets
  • syntax on inline styling vs using a stylesheet
  • class vs id
  • how to create a stylesheet – <style>
  • compound CSS selectors
• Centering things horizontally
• Fonts

Create layouts and using div tags

• How to use <div> tags along with specific style properties such as margin, padding, flex, width and height to create “boxes” that make up a page’s design and layout.
• How to use flex to create <div>s that go horiziontally next to each other
• Calculating column widths
• Basic understanding of CSS positions (e.g. relative vs absolute vs fixed)

Form objects

• HTML tags that create basic these form elements:
  • text
  • radio buttons
  • checkboxes
  • drop-down/select menu
  • textarea
• You need to know how to implement the above form elements as well as know each of their functionality
• How to “submit” a form
• You need to know how to code a form from scratch

Tables

• Basic understanding of tables
• Basic knowledge on how to style tables
• You need to know how to code a table from scratch.

Fonts

• How to add custom fonts using @font-face
• How to add fonts using google fonts

Positions

• relative, absolute, fixed positions
• using relative (parent) + absolute (child) positions

Meta Tags and Responsive Web Design

• Media queries

Some terminology that would be helpful to know

• tags vs elements
• tag attributes
• CSS selectors (ID, class, tag)
• stylesheet vs inline style
• CSS property and values
• inline vs block elements (display property)

Overview

This assignment consists of two parts: importing DVD database (needed for future assignments) and writing SELECT SQL statements against the DVD and song databases.

Requirements

Part 1 – DVD Database Import

1. Download dvd.sql file.
2. Create a new database in cPanel → MySQL Databases.
   1. Name it username_dvd_db (cPanel will prefix your username for you automatically).
   2. For example: ttrojan_dvd_db.
3. Import dvd.sqlfile to the newly created database.
   1. cPanel → PHPMyAdmin → [Click on username_dvd_db Database] → Import.
4. Verify all tables exist and are populated with data.
5. All tables and relationships should match the figure below.

Part 2 – SELECT SQL Statements

Open up MySQL Workbench and create one .sql file. In this file, you will write six SQL statements – four statements against the song database and two statements against the DVD database you just imported in Part 1. Above each statement, write a comment with the below prompt.

Important: Use JOIN statements when querying from multiple tables as taught in lecture. Do NOT select multiple tables on FROM statement. See this stackoverflow link for more info.

Song Database SELECT statements (4)

1. Display albums that have the letters “on” somewhere in the album title. Sort results in alphabetical order by album title.
   Sample results:
   
   
2. Same as #1, but only show album title and artist name (no artist_id) columns.
   Sample results:
   
   
3. Display tracks that have AAC audio file format. Only show track name (alias: track_name), composer, media type name (alias: media_type), and unit price columns.
   1. Use primary key like media_type_id column for comparison instead of strings like media type name.
      Sample results:
      
      
4. Display R&B/Soul and Jazz tracks that have a composer (not NULL). Sort results in reverse-alphabetical order by track name. Only show track ID, track name (alias: track_name), composer, milliseconds, and genre name (alias: genre_name) columns.
   1. Use primary key like genre_id column for comparison instead of strings like genre name.
      Sample results:
      
      

DVD Database SELECT statements (2)

1. Display DVDs that have the genre Drama that won awards. Sort results by year of when the DVD won an award. Show dvd title, award, genre, label, and rating.
   1. Use primary keys for comparison instead of the string names.
      Sample results:
      
2. Display R-rated Sci-Fi DVDs that have a release date (not NULL). Order results from newest to oldest released DVD. Show dvd title, release date, rating, genre, sound, and label.
   1. Use primary keys for comparison instead of the string names.
      Sample results:
      

Submission

When complete, you must upload the completed .sql file on Blackboard. Go to Assignments -> Assignment 7: SELECT Statements and upload the file. There is no need to upload this file to the server.

To-do for today’s class

• Log in to cPanel: http://303.itpwebdev.com/cpanel
• Download MySQL Workbench (version 8.x): https://dev.mysql.com/downloads/workbench/
  • Previous verison: https://downloads.mysql.com/archives/workbench/

Slides

Link to download PDF version of slides.

Completed Files

Completed files is a .mwb file that we created together in class. However, seeing it won’t be very helpful. If you are missing something, I recommend re-watching the last ~30 minutes of the lecture or looking at the Workbench Guide pdf.

• Coming soon…