Web Programming Step by Step
Homework Assignment 9: Kevin Bacon

This assignment asks you to interact with relational databases in PHP using SQL, as well as tying together the concepts taught throughout this course. You will write the following pages:

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 has a form where the user can type an actor's name. When the form is submitted, the results are shown on search.php. You will turn in the following files:

Since this is the last assignment of the quarter, 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.

If you prefer, you may use another actor of your choice rather than Kevin Bacon as the center point, so long as that actor is in our database and has a large number of connections to other actors.

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.

NOTE: With so much in common between the two pages, it is important to find ways to avoid redundancy. You should 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. Some names match no one in the database.

To address this problem, you can instead contact an intermediate instructor-provided web service to find out which actor matches a given name. You don't need to use our web service if you'd rather write that functionality yourself. But either way, your page needs to map a name to a single actor.

Actor ID Web Service, actorid.php:

Parameter nameValue
first_name actor's first name (or partial first name) as a string, such as "William"
last_name actor's last name (or partial first name) as a string, such as "Shatner"

The provided actorid.php maps names to IDs. For example, to search for partial names "ad" and "pitt": 

https://webster.cs.washington.edu/actorid.php?first_name=ad&last_name=pitt

This query would return the following XML output. Notice that it contains the actor id: 

<actor id="376249" first_name="Brad" last_name="Pitt" appearances="59" />

The service issues a 404 error if no actor is found. By default, the service queries imdb_small. To make it query the full imdb, pass an additional query parameter named imdb set to any value. If you want to return all the actors that matched a given first/last name, pass a query parameter named all set to any value. 

https://webster.cs.washington.edu/actorid.php?first_name=ad&last_name=pitt&imdb=true

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 UW NetID as your user name, and the MySQL password that was emailed to you. (If you lost your password, email us.)

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.

Extra Feature:

Add at least one extra feature not listed among the requirements here. The following are suggestions:

  1. Select actor from list: Once the user types the first/last name, if it matches more than one actor, let the user choose which one they meant. Use the all parameter to the web service previously described and present a list of possibilities, and let the user pick.
  2. Ajax search.php results: Change search.php into a service that spits out a partial HTML page containing just the two tables described previously. Contact it using Ajax rather than by a normal form submission, and when it sends back its HTML response, inject this into the main mymdb.php page. If you do this feature, a few of the earlier Appearance bullets from ("same title/links", "link back to mymdb.php") no longer apply to search.php and should be ignored.
  3. Actor career stats: Report information about the actor's career, such as their number of years they've been working in film (difference between first and most recent film), their average number of years between films, the percentage of their films in which they co-star with Kevin Bacon, and so on.
  4. Extended movie info: In addition to the movie info listed previously, also show the genre and/or director name of each movie in which the actor appeared. You'll have to modify your SQL queries.
  5. Bling: Add several Scriptaculous effects to your page to make it more awesome.

Developing Your Program:

Because the database is large and shared by all students, a bad query can hurt performance for everyone. We have a smaller database imdb_small with fewer records. While testing, please use that database and not the full imdb. When you think your code works, switch your PHP and/or JavaScript code to refer to imdb. We will post a "wall of shame" on the course web site. If you clog the server, you're going on the wall!

SSH

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.

Grading:

Your HTML (including PHP output) should pass the W3C XHTML validator. Your CSS code should pass the W3C CSS validator and should avoid redundant or poorly written rules. Show proper separation of content, presentation, and behavior between HTML, CSS, and JavaScript/PHP.

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. Place descriptive comments at the top of each file, each function, and on complex code sections.

Your JavaScript code should follow similar style guidelines and also should pass the JSLint validator. Your JS code should properly utilize XHTML DOM objects and Ajax requests for manipulating page contents.

Our search.php contains roughly 95 lines and our bacon.js is roughly 50 lines. Please do not place a solution to this assignment online on a publicly accessible (un-passworded) web site.