Month: March 2021

Submission

Create assignment10 directory on itpwebdev.com server within public_html. Upload all assignment files to that new directory, then link the assignment on your Student Page. Do not modify any assignment files after the deadline. The last modified timestamp serves as your submission time.

You can also optionally upload this on Blackboard as a backup if upload fails for partial credit.

*Important:* After the completed files are uploaded to the server and you test them, you might not notice some changes show up immediately. For example, add a new DVD and search for it. If the new DVD does not show up in the search_results.php, check the database first that the new DVD has been inserted in to the database. If the DVD still does not show up in search_results.php, it’s likely that the browser is showing a cached version of your search_results.php and it is not updating to show your new DVD. In this case, try to hard refresh the page by pressing shift in your keyboard and click the refresh icon in the browser. This will force the browser to show the latest version of search_results.php.

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

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.
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. 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.

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 drop-down option,
   8. Include Select One (empty/no selection) as first option in each drop-down.
4. Award Text Area.
5. Release Date Date Input (<input type=’date’…>).

Add Form

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 to see them.

Delete & Edit Functionality

1. Users need to be able to edit 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 DVD record in the database.
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.

To-do for today’s class

• Watch this separate lecture recording on how to do the “delete” feature: https://usc.zoom.us/rec/share/OgxwtAhGVbBVJcvvz-ZN6V4I9jZUpeRWWMNEre8vAxUGEtdWQ42g3na95L3emMDE.SCl-gDv6qbhjQUjR?startTime=1617336761000
• Download starter file: lect19-update-delete/ (Dropbox)
• Refer to song database diagram:
  

Helpful Readings

• Prepared Statements (php.net)
• PHP MySQLi Prepared Statements Tutorial (Website Beaver)

Slides

Link to download PDF version.

Completed files

• https://github.com/nayeonk/itp303-spring-2021-12pm/tree/main/lect19-update-delete/
• https://github.com/nayeonk/itp303-spring-2021-4pm/tree/main/lect19-update-delete/

To-do for today’s class

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

Slides

Link to download PDF version.

Completed files

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

To-do for today’s class

• Save this Dropbox folder in the itp104 folder that you are saving all the lecture material in:
  https://www.dropbox.com/sh/rw8vv2vmge86lct/AAD7BRfIEpYZxo7-GUq_Xgfra?dl=0
  Click Download -> Direct Download on top left to get a .zip of this folder.

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-2021/tree/master/lect10-css-transitions/

Overview

For this lab you will practice PHP and MySQLi workflow by creating search & search results pages for Football Schedule DB.

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/lab_06/search_form.php

Requirements

Part 1 – Football Schedule Database Import

1. Download football_schedule.sql file.
2. Create a new database in cPanel → MySQL Databases.
   1. Name it username_football_schedule_db (cPanel will prefix your username for you automatically).
   2. Add the database user created in class to your Football Schedule database.
      1. cPanel → MySQL Databases → Add User To Database → Select your user & database then press “Add” → Check All Privileges → Make Changes.
3. Import football_schedule.sql to football_schedule_db.
   1. cPanel → PHPMyAdmin → [Click on username_dvd_db Database] → Import.
4. Verify all tables exist and are populated with data using phpMyAdmin.
5. All tables and relationships should match the figures below. You don’t need to create this diagram. Simply use phpMyAdmin to ensure that all tables look like the tables below.Football Schedule Database

Part 2 – PHP MySQLi

1. Download starter file: lab09/
   1. Starter files have comment blocks and ‘TODO’ as hints. You only need to add code in those sections.
2. Search Form (search_form.php):
   1. All three (3) drop-downs need to be dynamically populated.
      1. Do not hard-code dropdown options.
   2. The form submits to search_results.php using GET method.
   3. On top of the file:
      1. 1. Establish database connection (create mysqli object).
         2. Check and output any DB connection errors.
         3. For each dropdown (team, venue, day):
            1. Create a SQL statement to get all columns and rows from those tables,
            2. Submit SQL statement to the DB (query the DB),
            3. Check and output any SQL errors.
         4. Close MySQLi connection.
   4. For each drop-down in HTML section (team, venue, day):
      1. Loop through corresponding results,
      2. Output <option> tags with value attribute set to primary key and set option (text visible to users) as team, venue, or days.
3. Search Results (search_results.php):
   1. Output search results based on user criteria from search form.
   2. Show 5 columns:
      1. Date,
      2. Day (name, not ID),
      3. Home Team (name, not ID),
      4. Away Team (name, not ID),
      5. Venue (name, not ID)
   3. On top of the file:
      1. Establish database connection (create mysqli object).
      2. Check and output any DB connection errors.
      3. Create a SQL statement that will retrieve all necessary columns and rows for output.
         1. Hints:
            1. Start with showing ALL records first, without adding any search criteria.
            2. You can alias table names in JOIN statements — useful for joining the same table multiple times
            3. Once the search works, you can go back and add search criteria one by one.
      4. Submit SQL statement to the DB (query the DB).
      5. Check and output any SQL errors.
      6. Close MySQLi connection.
   4. For the table in the HTML section:
      1. Loop through and output the results row by row.
4. Submission:
   1. When completed, you will need to upload this page to the itpwebdev server via FileZilla (like you have done for labs/assignments earlier in the semester).
   2. Connect to the itpwebdev server on FileZilla. Create a folder named lab09 and upload all files to this folder.
   3. In your browser, go to http://303.itpwebdev.com/~yourusername/lab09/search_form.php to check that the assignment file has been uploaded correctly.
   4. One last thing. In your computer, open up student_page.html that you created in Lab 1. Add a link to this assignment to student_page.html under the heading “Labs” so that the TAs can easily access your completed assignment.
   5. Upload the updated student_page.html to the itpwebdev server via FileZilla inside the public_html folder. If it asks you want to overwrite the previous file, click Yes.

To-do for today’s class

• Save this Dropbox folder in the itp104 folder that you are saving all the lecture material in:
  https://www.dropbox.com/sh/hbx374enamkotzc/AAAxhU5-wgV6AwNswS5WTorca?dl=0
  Click Download -> Direct Download on top left to get a .zip of this folder.

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-2021/tree/master/lect09-iframe-media/

To-do for today’s class

• Download starter file: lect17-php-mysqli/ (Dropbox)
• 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

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

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.

Requirements

DVD Search Form Page

1. Download starter files: a09-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.

Submission

Using FileZilla, create assignment08 directory on itpwebdev.com server within public_html. Upload all assignment files to that new directory, then link the assignment on your student_page.html. Do not modify any assignment files after the deadline. The last modified timestamp serves as your submission time.

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:
  

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-search-detail/search_form.php

 Overview

This lab helps you practice more SQL statements that manipulate the database, such as insert, update, and deleting records.

Submit your SQL .sql file on Blackboard.

Requirements

1. Create a view mpeg_tracks that displays all tracks with MPEG audio file format. Display track name (track_name) artist name (artist_name), composer, album title (album_title), and media type (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 20 Flight Rock by BackBeat from the database.
5. Display how many tracks there are for each album. Show album ID, album title (album_title), and track count (track_count).
   1. Note: some of your counts might be different due to added or deleted records during in-class exercises.
      

      

To-do for today’s class

• Download starter file: lect16-intro-php/ (Dropbox)
• Download MAMP (for both Mac and Windows): https://www.mamp.info/en/downloads/
  • • Select PHP version 7.3.* by going to Preferences -> PHP
  • Turn on debug statements for PHP by going to php.ini, which can be found in:
    • Mac: /Applications/MAMP/bin/php/php{version_num}/conf/php.ini
    • Windows: C://MAMP/conf/php{version_num}/php.ini
    • In the php.ini file, search for display_errors. You will see a few results. Look for the one around line 472. Set it to display_errors = On. You will need to restart MAMP servers for this to come into effect.
  • Go back to MAMP and go to Preferences -> Web Server and change the Document Root folder to the folder that contains all your ITP 303 files (a parent folder is best).
• 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-2021-12pm/tree/main/lect16-intro-php/
• 4pm: https://github.com/nayeonk/itp303-spring-2021-4pm/tree/main/lect16-intro-php/