What is Master/Detail Reporting?
Most databases are relational, which simply means they have 2 ore more tables that are related by common fields. For example, a Customer table might have an ID field that is also used in the Purchases table to identify each purchase that a specific customer has made. This setup is called a one-to-many relationship.
Individual records are uniquely identified by a key (the ID field in this example) in the master table (the Customer table).One or more related records are linked to customers in the detail table (the Purchases table). This relationship is called the master/detail model.
That's great and all, but how do I use this method to do what I want?
That's a fairly simple, yet complex question to answer. In order to do this type of processing of information from out table, we must first figure out how it actually works.
The best way I have found, is to just show you an example and then explain the steps afterwards.
Example 1 : Creating a master/detail report using the Customer and Purchases tables.
Customers Table
Code: Select all
+---------+-------------------+----------------------+
| CustID | CustomerName | CustomerAddress |
+---------+-------------------+----------------------+
| 1 | Jane Dobson | 15 Billy Bob Rd |
| 2 | Henry Redneck | 33 Chopbottom Rd |
| 3 | Charley Boy | 88 Copperhead Rd |
+---------+-------------------+----------------------+Code: Select all
+--------+-----------+-----------+
| CustID | Date | ItemNum |
+--------+-----------+-----------+
| 1 | 12/12/03 | 3234214 |
| 1 | 12/13/03 | 4234561 |
| 3 | 11/05/03 | 1134005 |
| 2 | 12/15/03 | 1456446 |
| 1 | 12/18/03 | 1002203 |
+--------+-----------+-----------+Code: Select all
<?php
mysql_connect('localhost','username','password');
mysql_select_db('my_database');
$CustName=array();
$Date=array();
$ItemNum=array();
$sql= "SELECT Customers.CustID, Customers.CustomerName, Purchases.Date, Purchases.ItemNum FROM Customers, Purchases WHERE Purchases.CustID = Customers.CustID ORDER BY CustID";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)
{
$CustName[]=$row['Customers.CustomerName'];
$Date=$row['Purchases.Date'];
$ItemNum=$row['Purchases.ItemNum'];
}
echo '<html><head><title>Reports</title></head><body>
<table>
<tr>
<td>Customer Name</td><td>Date</td><td>Item Number</td>
</tr>';
for($i=0; $i<=count($CustomerName); $i++)
{
echo '<tr><td>'.$CustomerName[$i].'</td><td>'.$Date.'</td><td>'.$ItemNum.'</td></tr>';
}
echo '</table></body></html>';
?>Code: Select all
+------------------+------------+----------------+
| CustomerName | Date | Item Number |
+------------------+------------+----------------+
| Jane Dobson | 12/18/03 | 1002203 |
| Jane Dobson | 12/12/03 | 3234214 |
| Jane Dobson | 12/13/03 | 4234561 |
| Henry Redneck | 12/15/03 | 1456446 |
| Charlie Boy | 11/05/03 | 1134005 |
+------------------+------------+----------------+"What exactly did you just do?!" might be the common question from some of the newer php developers. Never ph33r, for with a good example comes a good explanation.
First, let's look at the beginning of the php script:
Code: Select all
<?php
mysql_connect('localhost','username','password');
mysql_select_db('my_database');Next, we declared our arrays :
Code: Select all
$CustName=array();
$Date=array();
$ItemNum=array();If you are asking "What is an array?!", just refer to http://www.php.net/array. Since the subject of array is so indepth, there is no way I could fully explain it. Since this subject is well documented in php's manual, I see no reason to go into it. However, for the lazy person that just wants an answer here you go :
An array is a multiple fields of data placed into a variable. In other words, think of an array as a miniture table in one of your databases. You could have master/detail fields withing the array ( known as multi-dimensional arrays ), or you could just keep all kinds of data within one variable, letting the variable be the field of a table that you are wanting to pull from.
Again, this is a complex function to describe, and since this is a tutorial for master/detail reports for databases, we will stop the explanation here and expect the reader to do some more research on this broad subject themself.
Ok, on with the show!
The SQL Query
Next, we will look at our sql query that we performed:
Code: Select all
$sql= "SELECT Customers.CustD, Customers.CustomerName, Purchases.Date, Purchases.ItemNum FROM Customers, Purchases WHERE Purchases.CustID = Customers.CustID ORDER BY CustID";This allows us to create a Master Field ( CustID ) to the table in which the values we are wanting to see are located ( the Purchases Table ).
Once we get this information, we need to be able to report it. I chose the following method so that we could recall the values not only for reporting for display, but also for whatever other reason the user needs ( formulating, including in PIE chart reports, etc.. ).
Code: Select all
$result=mysql_query($sql); // query the sql statement..
while($row=mysql_fetch_array($result)
{
//.........The operations we perform are these :
Code: Select all
$CustName[]=$row['Customers.CustomerName'];
$Date=$row['Purchases.Date'];
$ItemNum=$row['Purchases.ItemNum'];
}The first array ( $CustName[] = $row['CustomerName']; ) should now be self-explanatory. It's saying "Assign the value of CustomerName we have found to the first array available in the $CustName array".
We do this for every record we have selected to pull data in the tables. The arrays will continue to be filled until the While loop has finished, and then it will go on with the operations that are left to perform.
Code: Select all
// First, we ready the table for values to be added..
echo '<html><head><title>Reports</title></head><body>
<table>
<tr>
<td>Customer Name</td><td>Date</td><td>Item Number</td>
</tr>';
for($i=0; $i<=count($CustName); $i++)
{
echo '<tr><td>'.$CustName[$i].'</td><td>'.$Date.'</td><td>'.$ItemNum.'</td></tr>';
}
echo '</table></body></html>'; // end of table.
MySQL_Close();
?>So, since we wnat to display the values in our arrays, we need a way to do this by having the script determine how many actual arrays ( since this will vary from report to report ) we have. The easiest way to do this is to just put the variable $i in place of the array #, and display the data with it.
So, the very first loop will display all the values of $CustName[0], $Date[0], and $ItemNum[0]. then, if the total number of results in our array is still not equal to $i, the values of $CustName[1], $Date[1], $ItemNum[1], and so on..
When $i finally is equal to the number of fields found in the array of $CustName, the loop will exit, and execute our last statment to close the table, body, and html. Finally, we ensure the MySQL connection is closed, and exit.
As I said, this was merely an introductory tutorial for the beginning phper. There are a lot of advanced methods of doing this, but I merely wanted to show the user the basics of using foreign sql concepts and php concepts to display data in table formats. Comments, Suggestions, and criticism is welcomed as it only will help developers as they grow with us.
I hope this tutorial has helped you, the reader, in one way or another.
R e s o u r c e s
http://www.php.net/array - Arrays function covered in the PHP Manual.
http://www.php.net/mysql - MySQL functions covered in the PHP Manual.
http://www.onlamp.com/pub/a/onlamp/exce ... tml?page=1 - AWSOME tutorial on these features covered here.
http://www.mysql.com - Online manual is located on this site for anything you could possible want to learn of MySQL Queries.
http://www.google.com - Yours and My friend for everything else.
Since i'm sure there may be a minor detail error here and there, i'd appreciate any corrections as this was a pretty lengthy documentation to correct word for word...
Cheers.