SQL: A Simple PHP/MySQL Master Detail Report

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.

Wine Sale MySQL database

The Master Page

  1. Create a new webpage named master.php with a table including a row of header information – branch, salesperson, total sales. Mysql page 11
  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:sql query
  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.dreamweaver live view
  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:sql results
  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.sql query
  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.get array in php
  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.

3 comments for “SQL: A Simple PHP/MySQL Master Detail Report

  1. May 17, 2011 at 4:49 am

    Thanks. Very useful tutorial

  2. Brandon G
    May 25, 2011 at 7:18 pm

    Am I suppose to add data to the tables via mySQL? If so, I add the data only in the sales table. I’ve tried this and got an error. I think it might do with the date column, whats the right format for timestamp? This is very frustrating, I’ve been moving along so slow.

  3. Brandon G
    May 25, 2011 at 7:21 pm

    this is the error, PLEASE HELP

    ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Schmoe, Frank Pank, ”, Merlot)’ at line 1
    SQL Statement:
    INSERT INTO `bgiovann`.`sales` (`price`, `ton`, `idsalesperson`, `idcustomer`, `date`, `idVintage`) VALUES (59999, 235, Joe Schmoe, Frank Pank, ”, Merlot)

Comments are closed.