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

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:

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

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

<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>

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

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:

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:

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:

• Scriptaculous effects: Add at least two Scriptaculous effects to your pages. For example, you could make the results of the actors.php search fade in.
• Rewrite your own actors.php: CSE majors can use the provided actors.php while developing and testing their pages, but in the end they should write their own version of this web service. The service should behave as specified previously, accepting two GET parameters for the actor's first and last name and outputting an HTML fragment containing a list of actors that match the name. The list should be sorted by last name, breaking ties by first name. You should find these actors by making a SQL query to the imdb database. Search for any actors whose first/last names contain the query parameter first/last names as substrings. Use the SQL LIKE keyword as shown in the slides to achieve this.

  Since it's possible for an actor name query to return tons of results, you should implement the following safeguards into your actors.php service:

  • Don't perform the SQL query if both the first and last name are blank.
  • Don't perform the query if only one of the first/last name is non-blank but is only a single letter in length.
  • Regardless of the first/last name values, set a reasonable upper limit of how many results will be shown by your service (such as 100) to avoid queries that return too many results. You can do this in your PHP code, but a better way is to use the SQL LIMIT keyword.

  You don't have to support the advanced XML mode briefly mentioned in this document.

• Immediate redirect when only one actor matches: When your page contacts actors.php with Ajax, if the results sent back include just one actor, your JavaScript code should detect this and immediately redirect the browser to search.php using that actor's id as the id query parameter. To do this, after injecting the HTML fragment returned from actors.php into the page, examine it with the DOM to see how many li elements have been inserted. If there are only one, redirect to its link target. You can set the browser's current page location in JavaScript by setting the global location.href property:

  location.href = "http://www.google.com/";
  

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.