Lab 9: Football Schedule Search Pages

Posted on | by nayeon

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