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.
In this assignment, 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 (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
search.php, the search results page
common.php, any common code that is shared between pages ("optional")
Appearance Constraints (both pages):
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.
- A "favorites icon". If you like, you may use the provided
- 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.phpthis 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.phpthis area contains the actor's movies and all movies in which the actor starred with Kevin Bacon.
search.phppage should contain a link back to
mymdb.phpif 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.
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
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 page performs two queries on the
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
table connects actors to movies.)
Your page should perform the following two queries. For each query, you will need to use a join between several tables of the database.
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
actorstable has been in at least one movie.
Hint: You will need to join the
rolestables, 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
FROMclause and contains one condition in its
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
FROMclause and contains 3 conditions in its
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:
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.
The administrator has installed both databases and given read only (SELECT) permission access globally. So essentially to the anonymous user at localhost, anyone with an account on betaweb can perform select queries on either imdb_small or imdb with no username or password.
Specifically, to access the database by php code, you just need:
From the commandline just:
mysql -u localhost use imdb_small (or) use imdb
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
imdb_small with fewer records. While
testing, please use that database and not the full
to refer to
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
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
include files to break up code. Avoid global
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.
As usual, there are a number of entertaining and challenging extra credit options to choose from for this assignment.
- 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.
- 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.
- 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.
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.