Assignment 5 -- Database: Add, Display, Delete records

In this assignment you will create three MySQL database tables and write php scripts to read, insert & delete data. You will be writing several SQL statements and you may find it useful to have your "SQL in 10 minutes" books available.

You will create a database table containing the names of your favorite four movies and second table containing a list of actors who appear in the movies. Actors and movies have a many-to-many relationship (an actor can be in multiple movies and a movie can have multiple actors). You will then create a third table that describes the relationship between actors and movies (which actors appear in which movies).

1. DVDTitles.php - Create a MySQL database table named dvdtitles. You will be adding information about four (at least) of your favorite movies. It should have the following structure:

Field name	Type	Attributes
asin	varchar(15)	primary key
title	varchar(100)
price	double(5,2)

1. ASIN is an acronym for "Amazon Standard Identification Number." It is a primary key that Amazon uses for all of its products. You can find ASINs on the product description page on Amazon's web site.

2. Use PhpMyAdmin to create the table. Your initial username is your ATUS universal login and your initial password is your ATUS universal login & "password" (i.e. "jonespassword"). Please change you password to something more secure.

3. It will be helpful to have some data in the database while you are working on the php page. Add one or two records of data using phpMyAdmin.

4. Modify the code in mySQL.php (source) to insert, delete & display (in a table) records from the new table.

5. I suggest that you start with displaying records in the database and get it working properly before starting on the insert and delete features.

6. String values in SQL statements are always enclosed within single quotes. ASIN and Title are both strings and require single quotes. For instance, your insert statement will look something like this:
   
   $strSQL = "Insert into dvdtitles (asin, title, price) VALUES ('$asin', '$title', $price)";

7. SQL debugging tip: check your SQL statement before you execute it. Put the following line of code immediately before the mysql_query($strSQL) statement:
   
   echo "SQL: $strSQL <br>";
   
   Format your page properly using tables or CSS. You may use the HTML from the sample.

8. The src of the images is:
   
   http://images.amazon.com/images/P/XXX.01.MZZZZZZZ.jpg
   
   where XXX is the item's ASIN. The sample source code illustrates image tags.
   
   
   

2. DVDActors.php - Add another table to your database named dvdActors. It should have the following structure:

Field	Type	Attributes
actorID	int(5)	auto_increment, primary key
fname	varchar(20)
lname	varchar(20)

1. Copy & modify the previous example to allow adding, deleting and reading of actors.

2. Add at least two actors for each of your movies.
   

3. DVDActorsTitles.php - Create a table of relationships between actors and movie titles. It should have the following structure:

Field	Type	Attributes
asin	varchar(15)	primary key (compound)
actorID	int(5)	primary key (compound)

1. Because this table uses a compound key, the delete statement must reference both the asin and actorID fields. Click "delete" in the sample to see the syntax.

2. Add data that describes the relationship between your movies and actors.
   

4. DVDListing.php - Create a join statement that joins data from all three tables. Remember, that field names that are used in more than one table (ASIN, ActorID) must be fully qualified (i.e. dvdtitle.ASIN).
   

5. DVDListingImproved.php - The previous sql query returns a separate record for each actor. We often want to group results from several records into a single row. There are several way we can achieve this:

   • Write PHP code to combine artists from multiple database records,
   • using sql's GROUP BY clause combined with the GROUP_CONCAT() function
   • executing a separate SQL statement to retrieve the actors for each ASIN
   • create a mySQL function that returns the actors associated with a given ASIN in a single string and call the function from the SQL statement that retrieves the DVD titles.
   
   The easiest and most efficient is the GROUP BY clause combined with the GROUP_CONCAT() function. Group_Concat() is a mySQL extension to the SQL standard that concatenates all the values within each group into a single string.
   
   SQL provides several functions that work in conjunction with GROUP BY. These functions allow us to get information about the grouped records. For instance, the AVG(price) function calculates an average value for a specific field for each grouping, and COUNT(*) returns of the number of records in each group. The GROUP BY clause retrieves the text from grouped fields. For instance, suppose we had three records with the same ASIN and different values for FName (Ann, Barbara, Mary). We could use GROUP BY ASIN to combine these three records into one record and "Select GROUP_CONCAT(FName SEPARATOR ", ") as Names" to return the three names in a new field called "Names." Here is another example of GROUP_CONCAT().
   
   In this exercise we are grouping records by ASIN and using GROUP_CONCAT to list in a single field the actor names in each group. Modify the previous exercise to list all the actors for each movie title in one table cell using GROUP BY and Group_Concat.
   Steps:
   • Copy the SQL statement from the previous exercise and paste it into PHPMYAdmin (under the SQL tab).
   • At the end of the SQL statement add:
        GROUP BY DvdActorsTitles.ASIN;
   • Test the query. It should work but will return only one actor name per ASIN.
   • In the SELECT clause replace "FName, LName" with:
        GROUP_CONCAT( Concat( FName, " ", LName ) SEPARATOR ", " ) AS Name

Test the query in PHPMyAdmin. It should return results similar these:


ASIN	Title	Price	Name
6305210411	Sliding Doors	9.98	Gwyneth Paltrow, John  Hannah
B00005BKZS	Black Robe	10.99	Cate Blanchett, Leonardo DeCaprio
B00080ZG1A	The Aviator	12.99	Aden  Young, Lothaire Bluteau
B000BSM26Q	Wedding Crashers	13.99	Owen Wilson, Vince Vaughn