Web Applications with Databases (20 pts)

Background: You will create .php web pages that query information from the Heather Sweeny Designs (HSD) database.

Instructions:

Download the attached zip file called HSD. Extract the files from the zipped folder (right-click and choose Extract All). The HSD folder has four files that together make up a Web application that reads or writes data from the HSD database. The index.html file is the home page of the Web application. Open this file in a Web browser. It should look something like this:

The other files in the HSD folder are PHP and HTML files that are linked to from this homepage. Click on the link next to Example 1. Because we have not set up our Web application with a connection to the HSD database, the webpage will just display source code, like this:

The attached tutorial PDF walks through the steps of establishing a connection between the Web application and the database. For this homework, you do NOT have to complete this tutorial (it is optional but recommended). If we were to complete it successfully, the web page would load like this:

Unfortunately, you may or may not get the web page to fully function properly. Due to security settings in the campus labs and the VCL, you cannot establish a connection between the web files and the database on those machines. If you have a personal PC with Windows and SQL Server, you might get the web site to fully function by following all the steps closely. (However, depending on your computer settings, this is often not as straightforward as it seems and often requires serious troubleshooting.) Due to the complexity (as well as limited time/resources to troubleshoot errors, etc.), it is not necessary for the Web page to fully function to receive full credit on this homework assignment.

Rather, we will be working with the code inside the .php files and see how SQL is embedded in this code. Even if you are not able to establish the connection and run the Web pages correctly, you will still gain experience working with SQL embedded in HTML/PHP code. (Most data in a database are accessed through application logic (i.e., programming code), rather than users directly accessing a DMBS).

Problems to complete (5 pts each):

Create a Web page for Heather Sweeney Designs to display the EmailAddress, LastName, FirstName, and Phone of customers in the CUSTOMER table.

Create a Web page for Heather Sweeney Designs to display the EmailAddress, LastName, FirstName, and Phone of customers that live in San Antonio.

Create a Web page for Heather Sweeney Designs to display the product data for all books.

Create a Web page for Heather Sweeney Designs to display the invoice number for all invoices containing sales of books.

In order to complete each of the questions above, complete the following steps. In other words, complete the bullet points below 4 times, once for each of the above problems.

Write the appropriate SQL query to answer the question (and test it in SQL Server Management Studio if you want there are scripts attached to create and populate the HSD database). Use the attached E-R diagram to help you use the correct table and attribute names.

Make a copy of the ReadSeminar.php file in the HSD folder that you downloaded and extracted. Rename the file Problem X where X is 1, 2, 3, or 4 corresponding to the problems above.

Open the .php file using Notepad or another text editor. (Rather than double clicking on the file, open up Notepad, and then choose File, Open and find the .php file). I recommend using Notepad++ which is a free text editor that you can find online that opens code files and color codes them so that code is easier to read.

See the attached PowerPoint deck (Tips for understanding) for help in understanding the code in the .php file.

Find the part of the code where a SQL statement is written (if you are having a hard time, use the Find function and search for the words Create SQL Statement). Replace the SQL statement in the example with the SQL statement you created to answer the question.

Find the part of the code where table headers are displayed. (Search for Table headers they are preceded by and followed by tags.) Replace the table headers from the ReadSeminar query results, with the appropriate headers that would result from your query.

Right below the table headers section, find the section Table Data. Replace the attributes listed in the example with the attributes that are used in your SQL query (e.g., replace SeminarDate).

Change other formatting data in the PHP file:

Find the title of the web page between

and

tags. Replace the text between these tags with a more appropriate title from your web page

Change the heading that displays at the top of the web page. You can find this in the code under , listed between the

and

tags. Right below the main header, there is a secondary header (the word SEMINAR between the

and

tags).

If you open your .php file in Internet Explorer (right click your file and choose Open With or, alternatively, open Internet Explorer first, then click FileOpen, then find the php file), you can see how your headers changed. You just wouldnt see the query results unless you have successfully created an ODBC connection. (Completely optional: If you save the files and are able to create an ODBC connection on your home computer, you can test that the web page pulls the right data.)

Save the .php file

When you have created all of your .php files, upload them to TITANium.

Upliting Research Teaching CSUFFun Mi Cailfomia Plano VCL The Heather Sweeney Designs SEMINAR Table Welcome to the Heather Sweeney Designs Home Page | SEMINAR Seminar Time Location SeminarTitle n Antonio Convention 10-12 11:00.00 13-10-26 1600:00 30.00 Center on a Big D 13-11-02 14-03-23 1100.00 30:00 Center Center on a BigD DReadSeminar PHP Page x D Upl?ing :] Research D Teaching ]CSUF Fun MiK D calfornia Dp ano vCL The Heather Sweeney Designs SEMINAR Table SEMINAR SeminaDate SeminaTine Location SeminarTite:Table data while SRecoadSetRow odbe fetch aray (SRecordSet)) ( echo ecRecordSetRowf SeminarDaecho Upliting Research Teaching CSUFFun Mi Cailfomia Plano VCL The Heather Sweeney Designs SEMINAR Table Welcome to the Heather Sweeney Designs Home Page | SEMINAR Seminar Time Location SeminarTitle n Antonio Convention 10-12 11:00.00 13-10-26 1600:00 30.00 Center on a Big D 13-11-02 14-03-23 1100.00 30:00 Center Center on a BigD DReadSeminar PHP Page x D Upl?ing :] Research D Teaching ]CSUF Fun MiK D calfornia Dp ano vCL The Heather Sweeney Designs SEMINAR Table SEMINAR SeminaDate SeminaTine Location SeminarTite:Table data while SRecoadSetRow odbe fetch aray (SRecordSet)) ( echo ecRecordSetRowf SeminarDaecho