Web Programming

CSC 210, Spring 2012

(Monday and Wednesdays, 2:00-3:15pm in CSB 209)

Submit At my.rochester.edu

Assignment #5

This assignment asks you to interact with relational databases in PHP using SQL, as well as tying together the concepts taught throughout this course. Please secure it using .htaccess as you did for the previous assignments - unless you turned it off, your existing .htaccess file should still be protecting it.

Contact Assignment

Anna Loparev!

Assignment Requirements

In this assignment, you will write the following pages:

expected output    expected output
(click for a full-resolution view)

The Six Degrees of Kevin Bacon is a game based upon the theory that every actor can be connected to actor Kevin Bacon by a chain of movies no more than 6 in length. (Most, but not all, can reach him in 6 steps. 12% of all actors cannot reach him at all.)

Your task for this assignment is to write the HTML and PHP code for a web site called MyMDb that mimics part of the popular IMDb movie database site. Your site will show the movies in which another actor has appeared with Kevin Bacon. The site will also show a list of all movies in which the other actor has appeared. The front search page mymdb.php (as the left screenshot above) has a form where the user can type an actor's name. When the form is submitted, the results are shown on search.php (as the right screenshot above).

You will turn in the following files:

  • mymdb.php, the front signup page (partial skeleton provided)
  • bacon.css, the CSS styles for both pages
  • bacon.js the JavaScript code for both pages
  • search.php, the search results page
  • common.php, any common code that is shared between pages ("optional")

Since this is the last assignment, one of its themes is to tie together much of the material you have learned. You will write some HTML/CSS and JavaScript and a large amount of PHP and SQL code.

Appearance Constraints (both pages):

Your mymdb.php and search.php must both match certain appearance criteria listed below. Beyond these, any other aspects of the page are up to you, so long as it does not conflict with what is required.

  • The same title, and links to the same CSS and JavaScript resources.
  • A "favorites icon". If you like, you may use the provided mymdb_icon.gif.
  • A prominently displayed MyMDB logo; if you like, you may use the provided mymdb_logo.png. Background image is here: background.png
  • A common stylistic theme, and an overall non-trivial number of styles, such as fonts, colors, borders, and layout. As much as possible, set up your styles to look correct on a variety of systems.
  • A form to type an actor's first/last name and search for matching actors in the database.
  • A central area of results/info. In mymdb.php this area contains content of your choice, including at least one image of Kevin Bacon (or your central actor) and some text about the site. Be creative! In search.php this area contains the actor's movies and all movies in which the actor starred with Kevin Bacon.
  • The search.php page should contain a link back to mymdb.php if the user wants to start over.

NOTE: With so much in common between the two pages, you'd better find ways to avoid redundancy. You could use the PHP include function with a shared common file included by both pages.

Front Page, mymdb.php:

The initial page, mymdb.php, allows the user to search for actors to match against Kevin Bacon. A skeleton is on the course web site; you may modify it in any way you like, subject to the constraints in this document.

The form on the page must contain two text boxes that allow the user to search for an actor by first/last name. The end goal is to submit to search.php, but it is possible that the name the user types (such as "Will Smith") will match more than one actor. In this case, you can just show the first matched actor. You page needs to map a name to a single actor.

Movie Search Page, search.php:

The search.php page performs two queries on the imdb database to show a given actor's movies. Query the database using PHP's mysql_ functions. Connect to the database using your ID and the MySQL password.

The database has the following relevant tables. (The roles table connects actors to movies.)

table columns
actors id, first_name, last_name, gender
movies id, name, year
roles actor_id, movie_id, role

Your page should perform the following two queries. For each query, you will need to use a join between several tables of the database.

  1. A query is to find a complete list of movies in which the actor has performed, displaying them in an HTML table. You may assume that any actor in the actors table has been in at least one movie.

    Hint: You will need to join the actors, movies, and roles tables, and only retain rows where the various IDs from the tables (actor ID, movie ID) match each other and the name or ID of your actor. Our solution's query joins 3 tables in the FROM clause and contains one condition in its WHERE clause.

  2. A query to find all movies in which the actor performed with Kevin Bacon. These movies should be displayed as a second HTML table, with the same styling as the first. This is the hard query and should be done last. If the actor has not been in any movies with Kevin Bacon, don't show a table, and instead show a message such as, Borat Sagdiyev wasn't in any films with Kevin Bacon.

    This query is bigger and tougher because you must link a pair of performances, one by the submitted actor and one by Kevin Bacon, that occurred in the same film. Do not directly write any actor's ID number anywhere in your PHP code, not even Kevin Bacon's.

    Hint: You will need to join a pair of actors (yours and Kevin Bacon), a corresponding pair of roles that match those actors, and a related movie that matches those two roles. Our query joins 5 tables in the FROM clause and contains 3 conditions in its WHERE clause.

The data in both tables should be sorted in descending order by year, breaking ties by movie title. The tables have three columns: A number for each movie, starting at 1; the title; and the year. The columns must have styled headings, such as bolded. The rows of the table must have alternating background colors, sometimes called "zebra striping." (Use PHP to apply styles to alternating rows.) For example:

table

It is not acceptable to perform query filtering in PHP. Your SQL queries must filter the data down to only the relevant rows and columns. For example, a bad algorithm would be to query all of the actor's movies, then query all of Bacon's movies, then use PHP to loop over the two looking for matches. Each of the two queries above should be done with a single SQL query to the database.

Databases:

To get started, you'll need to get MySQL up and running on AWS, and add the tables that we'll be using.

The first step is to make sure that mysql is installed and running on your instance AWS instance. We installed and set up a root account for mysql in the very first assignment. You'll want to make sure that the service is still running with the following command:


sudo service mysqld start

Next, we need to download the table information and then put it into MySQL by creating a new database and adding the tables to it:


curl http://www.cs.rochester.edu/courses/210/spring2012/assignments/5/imdb_small.sql > imdb_small.sql
curl http://www.cs.rochester.edu/courses/210/spring2012/assignments/5/imdb.sql > imdb.sql
mysql -u root -p
<enter your password created during setup>

CREATE DATABASE imdb_small;
use imdb_small;
SOURCE imdb_small.sql;

CREATE DATABASE imdb;
use imdb;
SOURCE imdb.sql;  # this will take a while

In your PHP use:
mysql_connect("localhost");
mysql_select_db("imdb_small");
(or)
mysql_select_db("imdb");

From the commandline just:

mysql -u root -p
use imdb_small
(or)
use imdb

Developing Your Program:

Because the database is large, you may not want to test on the big one first. The smaller database imdb_small with fewer records will allow for more efficient testing. When you think your code works, switch your PHP and/or JavaScript code to refer to imdb.

Use the MySQL console to develop your queries before writing PHP SQL code. Example tests are 376249 (Brad Pitt) or 770247 (Julia Roberts). If your query takes too long, press Ctrl-C to abort it.

Test the results returned by all PHP mysql_ functions to spot query mistakes. Print out your SQL queries while debugging, so you can see the actual query your code is making. Many PHP SQL bugs come from improper SQL query syntax, such as missing quotes, improperly inserted variables, etc.

Your code should follow style guidelines similar to those on our past homework specs. Minimize redundant HTML/PHP code. Use functions and include files to break up code. Avoid global variables.

After you finished your pages and before submission, don't forget to change the database from "imdb_small" (for test) to "imdb". To check your work, you can search "Kevin Spacey" in your page. The output should be: 75 movies by Kevin Spacey, and 2 movies by Kevin Spacey and Kevin Bacon together.

Please do not place a solution to this assignment online on a publicly accessible (un-passworded) web site.

Extra Credit

As usual, there are a number of entertaining and challenging extra credit options to choose from for this assignment.

Props

This document's contents are (C) Copyright 2009
Marty Stepp / Jessica Miller and licensed under Creative Commons Attribution 2.5 License.
It's been changed and adapted to the UofR by Jeffrey Bigham and Zhuan Chen.

Please email Jeffrey P. Bigham at jbigham@cs.rochester.edu with questions.

Valid XHTML 1.0 Transitional Valid CSS!