Assignment #7
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
Bradley Halpern!
Assignment Requirements
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 pagesbacon.js
the JavaScript code for both pagessearch.php
, the search results pagecommon.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! Insearch.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 tomymdb.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.
-
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
, androles
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 theFROM
clause and contains one condition in itsWHERE
clause. -
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 itsWHERE
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](table.png)
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:
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:
mysql_connect("localhost");
mysql_select_db("imdb_small");
(or)mysql_select_db("imdb");
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
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
.
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.
- 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.
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.