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.
The first database table will contain the names of your favorite four movies. The second table will
be a list of actors who appear in the movies. The third table will be an associative table that describes
the relationship between actors and movies (which actors appear in which movies). Actors and
movies have a many-to-many relationship (an actor can be in multiple movies and
a movie can have multiple actors).
The samples below use HTML5 validation which is optional.
- 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:
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.
Use PhpMyAdmin to
create the table. Your initial username is your ATUS universal login and your initial
password is your ATUS universal login & "22" (i.e. if username is "jones"
password is "jones22").
Please change you password to something more secure. Since your password will be
stored in clear text it is strongly recommended that you do not use an "important"
You may occasionally get the above error. This is a browser problem.
The solution is to use a different browser or delete your cookies and
restart the browser.
After changing your password you may need to close your browser or use a different
browser to login to phpMyAdmin since your browser may cache the old password.
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.
Database connection: You will use the same database connection function for all of your scripts.
create a new file named databaseConnection.php. Add your login information. Use your ATUS username
for both username and database name. Password is the one you created for mySql.
Modify the code in mySQL.php (source) to insert, delete & display (in a table) records
from the new table. You will need to modify the include statement to point to the database
connection script that you created in the previous step.
I suggest that you start with displaying records in the database and get it working
properly before starting on the insert and delete features.
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:
$sql = "Insert into dvdtitles (asin, title, price) VALUES ('$asin', '$title',
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: $sql <br>";
Format your page properly using tables or CSS. You may use the HTML from the sample.
The src of the images is:
where XXX is the item's ASIN. The sample source code illustrates image tags.
When retrieving ASIN from the querystring remember that ASIN is a string, not a number.
Run it through the fCleanString() function rather than the fCleanNumber() function.
Once you get your php script working add the ASIN and title for four (or more) of your
favorite movies. Go to Amazon's web site to find the ASIN for each.
- DVDActors.php - Add another table to your database
named dvdActors. It should have the following structure:
||auto_increment, primary key
Copy & modify the previous example to allow adding, deleting and reading of
Add at least two actors for each of your movies.
- DVDActorsTitles.php - Create a table of relationships
between actors and movie titles. It should have the following structure:
||primary key (composite)
||primary key (composite)
Because this table uses a composite key, the delete statement must reference both
the asin and actorID fields. Click "delete" in the sample to see the syntax.
Add data that describes the relationship between your movies and actors.
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).
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
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.
- 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:
||Gwyneth Paltrow, John Hannah
||Cate Blanchett, Leonardo DeCaprio
||Aden Young, Lothaire Bluteau
||Owen Wilson, Vince Vaughn
Submission instructions: Submit assignments via
the Canvas course management system.
Submit the full URL for each exercise in the assignment, listing the URLs
in the same order that they are listed in the assignment. To minimize typos in URLs
it is strongly recommended
that you copy the URLs from the address bar of the browser
rather than trying to type them. Incorrect URLs
will not be graded and no credit will be given.
When pages are connected via navigation (as in your Bookstore project) it is only
necessary to submit the URL of the first page.