University of Washington, CSE 190 M, Spring 2008
Homework Assignment 8: Kevin Bacon

due Friday, June 6, 2008, 11:00pm electronically

This assignment asks you to use SQL and PHP, 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 other actor can be connected to actor Kevin Bacon by a chain of movies no more than 6 in length. (Though the majority of actors can, not all actors can actually reach Bacon 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 some of the functionality of the popular IMDb movie database site. The main ability of your site will be to show the movies in which other actors have appeared with Kevin Bacon. (If you prefer, you may use another actor of your choice rather than Bacon as the center point, so long as that actor is in our database and has a large number of connections to other actors.) The site will also let the user search for an actor by an approximate name, as well as displaying all movies by a given actor.

The site will consist of a front page called mymdb.php and a search results pag called search.php. The mymdb.php page contains some initial content (described later) along with a form for a user to search for information about an actor. Users can search for actors using an instructor-provided web service. Once an actor is chosen, the browser is directed to search.php, which displays the search results.

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. To this end, you will write a bit of HTML/CSS, a bit of JavaScript, and a large amount of PHP and SQL code.

Another major theme of this assignment is avoiding redundancy in its many incarnations. You should also identify common pieces of text or code and express these as separate files that are included in your pages.

Appearance Constraints (both pages):

Unlike in past assignments, the exact appearance of the pages is not specified. Certain aspects of the appearance and behavior are specified below. Beyond these, any other aspects of the page are up to you, so long as your work reflects effort, follows proper conventions and stylistic guidelines, and does not conflict with what is required. If you are unsure whether an aspect of your page is acceptable, ask the instructor or TA. The goal is to encourage you to be creative and personalize your page. You are not expected to match the screenshots in this document exactly; in fact, if you do exactly copy its appearance, you will not receive full credit for creativity.

Your mymdb.php and search.php must both have the the following common properties:

Please note that with so much in common between your two pages, it is very important to find ways to avoid redundancy. We strongly suggest that you use the PHP include function with a shared common HTML file included by both pages.

Front Page, mymdb.php:

Your assignment's initial page, mymdb.php, displays information about the web site and allows the user to search for actors to match against Kevin Bacon. The appearance of this page is largely up to you; the required aspects of its appearance were specified previously. To help you get started, a sample version of this page is provided, but you do not need to use the sample version and may modify it in any way you like, subject to the other constraints in this document.

The form on the page must contain two text boxes that allow the user to search for an actor. The end goal is to reach search.php to see the movies that actor has been in with Kevin Bacon. But this form does not directly submit to search.php, because of the issue of actor name uniqueness. It is possible that the name the user types will match more than one actor in the database, or that it will match no actors at all. (Many common actors' names, such as "Will Smith", have this problem.) To address this problem, the form on mymdb.php will instead submit the first/last name to an intermediate web service called actors.php provided by the instructor.

[Actor Name → ID] Web Service, actors.php:

The actors.php service provided by the instructor accepts query parameters representing a first and last name (complete or partial) and outputs an HTML list of actors in the database that match that first/last name. Each item in the list will contain a matching actor's name and a link to search.php with the appropriate id parameter for that actor. By injecting the contents of this list into your page and allowing the user to click on them, you enable the user to complete the search for a unique actor.

You must connect to actors.php by making an Ajax GET request to the following URL:

https://webster.cs.washington.edu/actors.php

This web service accepts the following parameters. One or the other may be blank, but not both:

The actors.php service (request type: GET)
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 actors.php service outputs a fragment of HTML containing an ordered list of links that you should inject into your page. For example, the search for a partial first name of "ad" and a partial last name of "pitt" would correspond to the following URL:

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

This query would return the following HTML output:

<p>Actors matching ad pitt:</p>

<ol>
    <li><a href="search.php?id=376249">Brad Pitt</a></li>
    <li><a href="search.php?id=755520">Adelaide Pittaluga</a></li>
    <li><a href="search.php?id=782869">Nadia Scarpitta</a></li>
</ol>

Prototype's Ajax.Updater is perfect for this kind of task, fetching HTML with Ajax and injecting it into some onscreen element. Ajax.Updater works identically to Ajax.Request but it accepts an additional first parameter for the id of the element into which to inject the retrieved HTML content. You don't need an onSuccess handler with Ajax.Updater. If you'd rather not use it, instead use the innerHTML property of an element to put HTML content into it. For example, the following JavaScript code injects a paragraph into an element with id of output:

$("output").innerHTML = "<p>Hello, world!</p>";

(Advanced: If you're interested in further customizing the way that the actors.php results are placed into the page, you can instead fetch them as XML and parse them using the XML DOM. To do this, pass a third parameter named xml to actors.php with any value. For example:)

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

Movie Search Page, search.php:

The search.php page shows information about movies of the actor the user has selected. The required aspects of this page's appearance were stated previously. Recall that this page must share a substantial amount of content with mymdb.php, such as common styles, a common page header and logo, and the common form for searching for actors by name. The search page should also contain a link back to mymdb.php if the user wants to start over.

The search.php page accepts exactly one required query parameter, representing the actor ID to examine in the database. You may assume that this ID is a valid and unique actor ID that came from the actors table of the database, and therefore that it will match exactly one record when you search for actors by id, and so on. The query parameter's name is id, and it is passed as a GET parameter in the URL query string of the page. For example, the following represents a request to show information about the actor with id of 376249, who happens to be Brad Pitt:

search.php?id=376249

Your PHP code for this page should perform three queries on the database. The first query is to look up the actor that matches the id that was submitted. You need to do this to figure out the actor's first and last name, since you need to display the actor's name in the page. You must look this up using a SQL query to the database and may not pass the actor's first/last name to search.php as query parameters.

Your page's second query is to find the movies, if any, in which the actor performed with Kevin Bacon (or the actor of your choice). This is the hardest query and should be tackled last. The movies should be displayed as an HTML table. If the actor has not been in any movies with Kevin Bacon, your page should display no table, but rather a message such as NAME wasn't in anything with Kevin Bacon.

The third query your page should perform is to find a complete list of movies in which the actor has performed. These movies should be displayed as a second HTML table, in identical styling and format as the first. You may assume that any actor in the actors database has appeared in at least one movie.

The data displayed in both tables should be sorted in descending order by year, breaking ties by movie title. Both tables have three columns: A number for each movie, starting at 1; the movie's title; and the year of release. The columns must have headings with styling, such as bolded and/or centered. The rows of the table must have alternating background colors, a practice called "zebra striping." (Use PHP code to do this, applying color styles to alternating rows.) You must also apply some non-trivial styling to your table and its cells. For example, you could give each cell some padding or a light border. For example:

table

Database and Queries:

Your search.php page reads its data from a MySQL database named imdb, located on the server webster.cs.washington.edu. Your code can query this database using PHP's various mysql_ functions. You will log in to this database using your UW NetID as your user name, and a special MySQL password that has already been emailed to you. (If you do not have your MySQL password and cannot find it in your UW email, please contact us immediately.)

The database contains the following relevant tables:

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

The actors and movies tables probably seem the most relevant, but the roles table is crucial as well, because it is needed to connect actors to the movies in which they have appeared.

To solve this program, you will need to come up with three unique SQL queries on the database. They are listed below in order from easiest to hardest. This is the order in which we recommend you develop them.

  1. A query to find all the actor name that matches the actor id submitted to the page.
  2. A query to find all movies in which an actor has performed. To find this information, you will need to use a join between several tables of the database. The roles table provides the link between a given actor's ID and the IDs of the movies he/she has been in. The movies table gives you the other information about a movie, given its ID. You should acquire all of this information with a single SQL query; it is not acceptable to perform many queries (such as one per movie) to do so.
    • Use a FROM clause with several tables listed, or a JOIN clause, to perform a join in your query. Use ORDER BY to sort your results.

      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 query joins 3 tables in the FROM clause and contains one condition in its WHERE clause.

  3. A query to find all movies in which both your actor and Kevin Bacon (or your central actor of choice) have performed together. This query is fairly similar to the preceding query but more difficult. It involves more join operations because you must link not only your actor to the movie, but also to Kevin Bacon. Do not hard-code any actor's ID 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.

Your queries should be constructed so that they effectively filter the data down to only results that are relevant, both in which columns are returned and (most importantly) in how many / which rows are returned. It is not acceptable to perform a query that returns too many results, then filter it in your PHP code. For example, an incorrect way to find all movies the actor has been in with Kevin Bacon would be to get all of the actor's movies with one query, get all of Kevin Bacon's movies with another query, and then use PHP code to merge the two. This is unacceptable because it is needlessly slow and taxing on the database server.

For CSE Majors:

CSE majors must complete the following additional features:

Development Strategy and Debugging Advice:

We suggest the following overall development strategy:

  1. Create the initial mymdb.php page and give it some basic styling.
  2. Make it so that your form can submit to actors.php, without Ajax, and see the results. If you make your page submit to actors.php using a standard form, the browser will navigate to actors.php and show only its results. This isn't the desired appearance, but at least you can use this to test that you are connecting to actors.php correctly.
  3. Modify your code to use Ajax to talk to actors.php instead of a standard form submission. Inject the actors.php HTML output into your page.
  4. Write a stub version of search.php that simply accepts the expected id parameter and displays it on an HTML page that looks much like your mymdb.php page. Take this opportunity to look at the similarities between the two pages and consolidate the redundant pieces appropriately.
  5. Use the MySQL console window and a text editor to practice SQL queries. Write the first query in this document, the one that looks up an actor's name by id. Get it working in the MySQL console window before writing any PHP code that performs SQL queries. You can test with actor ids such as 376249 (Brad Pitt) or 770247 (Julia Roberts).
  6. Modify your search.php to perform this first SQL query, looking up an actor's name by id. Display the actor's name in the page.

    Make sure to test the error result codes returned by all of the PHP mysql_ functions, to spot any mistakes in your syntax or queries! Also, please print out your SQL queries in any error print statements, so that you can see the actual query that your PHP code is making. Many common PHP-SQL bugs come from improper SQL query syntax, such as missing semicolons, quotation marks, improperly injected variable contents, and so on.

  7. Use the MySQL console to figure out the other two queries: all movies an actor has been in, and all movies the actor has been in with Kevin Bacon. Finish search.php by making it perform these queries and display their results as HTML tables.

Because the overall imdb database is so large, it can be very costly to perform a malformed query on it. This database will be used by all students, so a poorly written program can affect performance for everyone.

To address this problem, we have created a database named imdb_small that contains a smaller set of records. While you are debugging your code, please point it at that database rather than at the full imdb database. When you are reasonably sure that you have your program working, then you can switch to the full imdb dataset.

To discourage students from crashing or overloading the database server, we will post a "wall of shame" on the course web site. This wall will list the partial names of any students who break or bog down the server. (This is intended to be light-hearted; no actual punishment will occur for such mistakes.)

We also encourage you to test your queries on MySQL directly before running them as PHP code. To do this, connect to webster.cs.washington.edu using SSH, and then type the following command (substituting your own UW NetID) and press Enter:

mysql -u YOUR_UW_NETID -p

It will ask for your SQL password as sent to you by email. As you type in your password, the SSH window won't echo anything, but it is still receiving the characters you are typing. Once you're in the MySQL prompt, type the following command to connect to the database, and then issue SQL queries as normal.

USE imdb_small;

Once you think you have your query perfected, try it out on the real database:

USE imdb;

If you type a query that is taking a long time to run, press Ctrl-C to cancel it. Type quit to exit MySQL.

Submission and Grading:

Submit your assignment online from the turnin area of the course web site. For reference, our search.php contains roughly 100-130 lines of PHP code, though you do not need to match this.

Your HTML code (including HTML code produced as output from PHP) should follow valid XHTML syntax and should pass the W3C XHTML validator. You should also not place stylistic information in your HTML code when it could instead be placed into a CSS stylesheet.

Your HTML and CSS code should be W3C valid and have comment headers. (The HTML is generated by PHP in this case. You don't put your PHP source code into the validator, but rather the HTML output that it generates.) Your CSS code should avoid redundancy as much as possible. Stylistic information should be placed into CSS and not into HTML/JS.

Your PHP and JavaScript (if any) code should follow reasonable stylistic guidelines similar to those you would follow on a CSE 14x programming assignment. Minimize redundant code, decompose the problem into functions intelligently, minimize the number of global variables, utilize parameters and return values properly, correctly use indentation and spacing, avoid long lines over 100 characters in length, and place a comment header at the top of your code files and atop every function explaining that function's behavior. You should only use material that has been discussed during the first eight weeks of the course, unless given explicit permission from the instructor.

The HTML that is output by your PHP pages will be graded by the same criteria as normal HTML code, meaning that it should be valid XHTML, properly formatted and indented, and so on. Your PHP code should use the "embedded PHP" style shown in class; for full credit, it should not contain any print or echo statements. Produce all output by writing pure HTML in "HTML mode", and insert variables' values using PHP expression blocks. Your PHP code should generate no error or warning messages when run using reasonable sets of parameters.

Redundant HTML and PHP code should be minimized; make a particularly strong effort in this assignment to remove redundant code from your pages. Write functions to procedurally decompose your PHP code into smaller pieces. Place each of your three SQL queries into its own function that returns that query's results. Place any significant repeated code into functions that you can call multiple times to avoid the redundancy. Also use the include PHP function as appropriate to consolidate common pieces of HTML code.

Your JavaScript code should follow similar style guidelines to those just stated for PHP, should also pass the JSLint validator, and should correctly utilize XHTML DOM objects and Ajax requests for manipulating the page contents.

Part of your grade will come from posting your page to exactly the following URL:

https://webster.cs.washington.edu/your_UWnetID/hw8/mymdb.php

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

Except where otherwise noted, the contents of this presentation are © Copyright 2007 Marty Stepp and are licensed under the Creative Commons Attribution 2.5 License.

Valid XHTML 1.1 Valid CSS!