Jay Peretz | Santa Rosa Junior College

This tutorial is based on Project 4 of the SQL class.   It is of use to students in the Advanced Dreamweaver class as well.    In fact, a summary/detail report is a very common technique in using SQL database-driven applications.    I will be using the winery database created by an SQL student to create two linked webpage reports based on dynamic results from an SQL database.    Here is the schema.  Dreamweaver students can peek in on the SQL class pages to see how we arrived at this database and what the Project 4 assignment is.

The Master Page

1. Create a new webpage named master.php with a table including a row of header information – branch, salesperson, total sales.
2. Insert a PHP script tag to start the program after the closing tr tag.   We are going to use the mysql() library. You can  follow the example on page 406 and 407 of the Learning MySQL textbook, using the mysql_connect, mysql_select_db, mysql_query and mysql_fetch_array.   You want to connect to the your mysql account and database of course, using localhost as the database server.
3. You need to write an  SQL for the summary results for your report.   In the sample database, this one works well, as see in a MySQL Workbench scratchpad:
4. At this point, your webpage should work and return the same results into the HTML table on the page.     Here is the code, color-coded by Dreamweaver for easier legibility.  I am also displaying in Live View, so Dreamweaver displays the server-processed page.
5. Notice that the mysql_fetch_array call uses the MYSQL_ASSOC result type, and that using the foreach loop lays the resulting data out neatly in the columns of the table.    By using this result type, we can refer to the resulting data elements individually and so wrap a query string link around the salesperson name.   To do so, we will decompose the result using the associative row array to get more control over laying the data into the HTML table:
6. The master.php is  complete.   Test the page through a browser and watch the link rollover to make sure the query string looks correct, although it won’t work yet.   We’re going to build that page now.

The Detail Page

1. ‘Save As’ or duplicate the master.php page and rename it as detail.php.    Change the table header accordingly to show the four columns in the detail report: customer, variety, vintage, sale total
2. Use MySQL Workbench to write the SQL query to return the detailed sales records from the sales database table.Replace the SQL statement in the mysql_query function.Thanks to this highly normalized database schema, I’ve developed a nice complex SQL query, using a sample salesperson name of Carlin just  for the test.
3. The query string in the URL creates a PHP associative array of each name/value pair called $_GET[].  We can refer to the value by passing the associative name array.   Replace the ‘hardcoded’ string Carlin in the SQL query within the mysql_query function to this code: 
   p.personName = \”{$_GET["salesperson"]}\”
   That will insert the query string from the URL into the where statement of the SQL query to select the appropriate record based on the record clicked in the master report.
4. Rearrange the code so the appropriate data elements are layed out in the table.  This should be pretty straightforward following the example in the master page.
5. Change the page title and the h1 header to dynamically display the salesperson name from the query string in the URL.
6. Add a link back the to summary master page below the table.
7. Test the master page in a browser and click on a salesperson name.   The detail sales report for that salesperson should appear.

Download the code here.