Month: October 2021

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

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

To-do for today’s class

1. Download starter file: lect16-intro-php/ (Dropbox)
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.* 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-fall-2021-12pm/tree/main/lect16-intro-php/
• 4pm: https://github.com/nayeonk/itp303-fall-2021-4pm/tree/main/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.

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.

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

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:
      
   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/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 “Assignments” 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

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

Slides

Link to download PDF version.

Completed Files

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

Overview

This assignment helps you practice more SQL statements that manipulate the database, such as insert, update, and deleting records. You will write eleven SQL statements: five for Song database and six for DVD database.

Requirements

Create one .sql file for all eleven 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 (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.
      

      

Part 2 – DVD Database

1. Create a view dramas that displays all drama DVDs with release date not set to NULL. Show DVD ID, DVD title, release date, award, format, genre, label, rating, and sound.
   
2. Add a new DVD with the below information:
   1. Title: The Godfather
   2. Release Date: March 24, 1972
   3. Award: 45th Academy Award for Best Picture
   4. Format: Fullscreen, Widescreen
   5. Genre: Drama
   6. Label: Paramount
   7. Rating: R
   8. Sound: DTS
3. Make the following changes to the DVD titled “Zero Effect.” This DVD already exists in the database.
   1. New Label: Columbia TriStar
   2. New Genre: Comedy
   3. New Format: Fullscreen
4. Delete the DVD titled “Major League 3:Back to the Minors” from the database. This DVD already exists in the database.
5. Display the number of characters for the longest and shortest title in the database. Name columns longest_title and shortest_title respectively. Use aggregate functions.
   
6. Display all genres and number of DVDs belonging to each genre as dvd_count column. Show genre ID, genre name, and DVD count. Use an aggregate function.
   

Submission

When complete, you must upload the completed .sql file to Blackboard. Go to Assignments -> Assignment 7: Full SQL Statements and upload the file.

Overview

This lab consists of two parts: importing DVD database (needed for Assignment 7) and writing SELECT SQL statements against the song database. Submit the .sql file on BlackBoard when you finish.

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 – Song Database SELECT SQL Statements

Open up MySQL Workbench and create one .sql file. In this file, you will write four SQL statements. 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 and also this piazza post.

1. Display albums that have ‘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 (use alias: track_name), composer, media type name (use alias: media_type), and unit price columns.
   1. Use media_type_id column for comparison instead of 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 (use alias: track_name), composer, milliseconds, and genre name (use alias: genre_name) columns.
   1. Use genre_id column for comparison instead of genre name.
      Sample results:
      

Submission

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

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

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

Overview

This lab helps ensure you understand this week’s material and have all software working for upcoming lectures and assignments. You will design, upload, and populate a simple film database.

Requirements

1. Create a database in cPanel -> MySQL Databases
   1. Name it yourusername_film_db (your DB will be prefixed with your username by default).
2. Open MySQL Workbench. Create a new model.
3. Use the same name from step #1 for your schema name in MySQL Workbench.
4. Design a database to store data from Highest Grossing Films table below.
5. Normalize the database.
6. Establish table relationships.
7. Use appropriate data types.
   1. Hint: Range of INT data type is too small to fit Worldwide Gross field.
      1. https://dev.mysql.com/doc/refman/5.7/en/integer-types.html
8. Column names must be lowercase and separated by underscores for consistency.
   1. Example: worldwide__gross.
9. Populate all tables with correct data.
10. Save the model (saves as a .mwb file) and upload on Blackboard.
    1. Go to Blackboard->Assignments->Lab 7: Simple Film Database Design
    2. If you are unsure of your design choice (how you normalized tables), you can add comments on Blackboard rationalizing your decision.
11. Forward Engineer your model to 303.itpwebdev.com server.
12. You can check that the database is successfully on the server by going to cPanel -> Databases -> phpMyAdmin.

Sample

No sample.

To-do for today’s class

• Log in to cPanel (same credentials as FileZilla): http://303.itpwebdev.com/cpanel
• Download MySQL Workbench (version 8.x): https://dev.mysql.com/downloads/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.

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