Using MySQL and PHP to Present Excel Spreadsheets

"Collinstwerp!"

"That's Collinsworth, sir," you say, meakly.

"Whatever Collinearth, I need to see some trending data on sales...NOW!"

"It's Collinsworth sir," you repeat

"Can I get that in my speadsheet thingie Collinsyurp?!?", he continues to yell.

"I'll get on it right away! mumbling*you pig-headed freak*mumbling," as you head for your cube.

Number crunchers just love spreadsheets, the numbers, the colors, the graphs, and all of the functions for bending and shaping the numbers to do their bidding. And they like them a certain way, usually as a pivot table where the columns represent groups of data, the rows represent groups of data, and at the intersection of a row and column is the number that they are looking for, as in this example;

You need to make it as easy as possible, a one-click link to the data. What is a developer to do?

Set Up Some Test Data

Let's create a table, and some data to work with:

CREATE TABLE `tblProductToy` (
  `ID` int(11) NOT NULL auto_increment,
  `Toy` varchar(32) default NULL,
  `DateShipped` varchar(10) default NULL,
  `NumberShipped` int(11) default NULL,
  PRIMARY KEY  (`ID`)
)

insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-01', '36');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-01', '12');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-01', '11');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-01', '14');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Balloon', '2002-03-01', '36');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-01', '36');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-03', '11');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-02', '14');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-04', '36');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-06', '12');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Balloon', '2002-03-03', '17');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Balloon', '2002-03-04', '13');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Party Pack', '2002-03-02', '14');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Party Pack', '2002-03-04', '36');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Party Pack', '2002-03-06', '12');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Party Pack', '2002-03-03', '17');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Party Pack', '2002-03-04', '13');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-01', '14');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Balloon', '2002-03-01', '36');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-01', '36');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-03', '11');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Water Pistol', '2002-03-02', '14');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Streamer', '2002-03-04', '36');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Streamer', '2002-03-06', '12');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Streamer', '2002-03-03', '17');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Streamer', '2002-03-04', '13');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Streamer', '2002-03-02', '14');
insert into tblProductToy (Toy, DateShipped, NumberShipped) values ('Streamer', '2002-03-04', '36');

If you select all of the data in the table it will look something like this;

mysql> select * from tblProductToy;
+----+--------------+-------------+---------------+
| ID | Toy          | DateShipped | NumberShipped |
+----+--------------+-------------+---------------+
|  1 | Water Pistol | 2002-03-01  |            36 |
|  2 | Water Pistol | 2002-03-01  |            12 |
|  3 | Water Pistol | 2002-03-01  |            11 |
|  4 | Water Pistol | 2002-03-01  |            14 |
|  5 | Balloon      | 2002-03-01  |            36 |
|  6 | Water Pistol | 2002-03-01  |            36 |
|  7 | Water Pistol | 2002-03-03  |            11 |
|  8 | Water Pistol | 2002-03-02  |            14 |
|  9 | Water Pistol | 2002-03-04  |            36 |
| 10 | Water Pistol | 2002-03-06  |            12 |
| 11 | Balloon      | 2002-03-03  |            17 |
| 12 | Balloon      | 2002-03-04  |            13 |
| 13 | Party Pack   | 2002-03-02  |            14 |
| 14 | Party Pack   | 2002-03-04  |            36 |
| 15 | Party Pack   | 2002-03-06  |            12 |
| 16 | Party Pack   | 2002-03-03  |            17 |
| 17 | Party Pack   | 2002-03-04  |            13 |
| 18 | Water Pistol | 2002-03-01  |            14 |
| 19 | Balloon      | 2002-03-01  |            36 |
| 20 | Water Pistol | 2002-03-01  |            36 |
| 21 | Water Pistol | 2002-03-03  |            11 |
| 22 | Water Pistol | 2002-03-02  |            14 |
| 23 | Streamer     | 2002-03-04  |            36 |
| 24 | Streamer     | 2002-03-06  |            12 |
| 25 | Streamer     | 2002-03-03  |            17 |
| 26 | Streamer     | 2002-03-04  |            13 |
| 27 | Streamer     | 2002-03-02  |            14 |
| 28 | Streamer     | 2002-03-04  |            36 |
+----+--------------+-------------+---------------+
28 rows in set (0.02 sec)

How are we going to turn this into something that looks like our example above?

Do It At The Data Level

What you need to know about first of all is a handy little thing called a crosstab query. Using a crosstab you can output the data from the database into a table format with almost everything you need to deliver a spreadsheet. Here is the full crosstab query for this table to show how many of each item were shipped on a particular date;

SELECT DateShipped,
SUM(IF(Toy = 'Water Pistol', NumberShipped, 0)) AS "Water Pistols",
SUM(IF(Toy = 'Balloon', NumberShipped, 0)) AS "Balloons",
SUM(IF(Toy = 'Party Pack', NumberShipped, 0)) AS "Party Packs",
SUM(IF(Toy = 'Streamer', NumberShipped, 0)) AS "Streamers"
FROM tblProductToy
GROUP BY DateShipped

If you run this query against the data we created above you will get the following results;

+-------------+---------------+----------+-------------+-----------+
| DateShipped | Water Pistols | Balloons | Party Packs | Streamers |
+-------------+---------------+----------+-------------+-----------+
| 2002-03-01  |           159 |       72 |           0 |         0 |
| 2002-03-02  |            28 |        0 |          14 |        14 |
| 2002-03-03  |            22 |       17 |          17 |        17 |
| 2002-03-04  |            36 |       13 |          49 |        85 |
| 2002-03-06  |            12 |        0 |          12 |        12 |
+-------------+---------------+----------+-------------+-----------+

Pretty slick, eh? It works like this, first we get the data we wish to group by, SELECT DateShipped. Then we do something that looks complicated, but in reality is quite simple. For each product in the table we write a line of SQL that looks like this, SUM(IF(Toy = 'Water Pistol', NumberShipped, 0)) AS "Water Pistols". What does this line do? Let's break it down;

• SUM( will total the information that we tell it to, in this case the values returned by the IF statement
• IF(Toy = 'Water Pistol', determines if this column value exists
•  NumberShipped, gets the value from this column if  Toy = 'Water Pistol' is true
•  0)) makes the value zero if Toy = 'Water Pistol' is false
•  AS "Water Pistols" gives the result column a name

Of course, FROM tblProductToy tells the query which table, and GROUP BY DateShipped groups the records together by date with the proper totals. If you wanted to count the shipments of each item per day change NumberShipped to 1 in each of the SUM...IF statements. There are many functions available to MySQL as well as many other database management packages that will allow you to do things like average results or use conditional expressions to generate results.

We still don't have some of the numbers we need though, such as "Total Shipped Today" so lets change our query, adding the line sum(NumberShipped) as "Total Shipped Today" ;

SELECT DateShipped,
SUM(if(Toy = 'Water Pistol', NumberShipped, 0)) AS "Water Pistols",
SUM(if(Toy = 'Balloon', NumberShipped, 0)) AS "Balloons",
SUM(if(Toy = 'Party Pack', NumberShipped, 0)) AS "Party Packs",
SUM(if(Toy = 'Streamer', NumberShipped, 0)) AS "Streamers",
SUM(NumberShipped) AS "Total Shipped Today"
FROM tblProductToy
GROUP BY DateShipped

Running this query gives us the following results;

+-------------+---------------+----------+-------------+-----------+---------------------+
| DateShipped | Water Pistols | Balloons | Party Packs | Streamers | Total Shipped Today |
+-------------+---------------+----------+-------------+-----------+---------------------+
| 2002-03-01  |           159 |       72 |           0 |         0 |                 231 |
| 2002-03-02  |            28 |        0 |          14 |        14 |                  56 |
| 2002-03-03  |            22 |       17 |          17 |        17 |                  73 |
| 2002-03-04  |            36 |       13 |          49 |        85 |                 183 |
| 2002-03-06  |            12 |        0 |          12 |        12 |                  36 |
+-------------+---------------+----------+-------------+-----------+---------------------+

Very handy. Again, if we were counting total shipments we could replace SUM(NumberShipped) as "Total Shipped Today" with COUNT(*). Try making the replacements and see what happens.

Crosstabs enable you to get pretty complete information from your database where this kind of table is concerned, but it may not give you all you need, at least not without running another query like this one, which totals each item for the complete range of records and provides a total number of items shipped;

SELECT SUM(IF(Toy = 'Water Pistol', NumberShipped, 0)) AS "Total Water Pistols",
SUM(IF(Toy = 'Balloon', NumberShipped, 0)) AS "Total Balloons",
SUM(IF(Toy = 'Party Pack', NumberShipped, 0)) AS "Total Party Packs",
SUM(IF(Toy = 'Streamer', NumberShipped, 0)) AS "Total Streamers",
SUM(NumberShipped) AS "Total Shipped"
FROM tblProductToy

Which returns;

+---------------------+----------------+-------------------+-----------------+---------------+
| Total Water Pistols | Total Balloons | Total Party Packs | Total Streamers | Total Shipped |
+---------------------+----------------+-------------------+-----------------+---------------+
|                 257 |            102 |                92 |             128 |           579 |
+---------------------+----------------+-------------------+-----------------+---------------+

Or we can "turn the table";

SELECT Toy,
SUM(IF(DateShipped = '2002-03-01', NumberShipped, 0)) AS "March 1, 2002",
SUM(IF(DateShipped = '2002-03-02', NumberShipped, 0)) AS "March 2, 2002",
SUM(IF(DateShipped = '2002-03-03', NumberShipped, 0)) AS "March 3, 2002",
SUM(IF(DateShipped = '2002-03-04', NumberShipped, 0)) AS "March 4, 2002",
SUM(IF(DateShipped = '2002-03-05', NumberShipped, 0)) AS "March 5, 2002",
SUM(IF(DateShipped = '2002-03-06', NumberShipped, 0)) AS "March 6, 2002",
SUM(NumberShipped) AS "Total Shipped"
FROM tblProductToy
GROUP BY Toy;

Getting these results;

+--------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+
| Toy          | March 1, 2002 | March 2, 2002 | March 3, 2002 | March 4, 2002 | March 5, 2002 | March 6, 2002 | Total Shipped |
+--------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+
| Balloon      |            72 |             0 |            17 |            13 |             0 |             0 |           102 |
| Party Pack   |             0 |            14 |            17 |            49 |             0 |            12 |            92 |
| Streamer     |             0 |            14 |            17 |            85 |             0 |            12 |           128 |
| Water Pistol |           159 |            28 |            22 |            36 |             0 |            12 |           257 |
+--------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+

This doesn't give us a total shipped each day though and we only want to run one query to the database for these results. That means that we will have to do some calculations in our one-click link to the spreadsheet. We'll handle this in PHP, with a couple of "Stupid IE Tricks" thrown in for good measure.

PHP To The Rescue

Within our PHP code we can place raw HTTP headers (see PHP Header Function, Hypertext Transfer Protocol - HTTP/1.1, and other associated links) prior to any output from the PHP in order that the output might be utilized by applications, such as Microsoft® Excel.

The first header line sets up the application type, in this case Microsoft® Excel. The second header line essentially tells the browser not to cache the information. The third header line makes sure that subsequent calls to the server are revalidated/authenticated. For further detail about HTTP headers I will refer you to the above links.

Once we have connected to the MySQL server and selected the database we wish to use we can issue our crosstab query.

Which is great if we know that out list of toys is static. What if they are not? We can do two queries in PHP, using the results of one query to complete the second query. This will always give us all of the products available to analyze.

One of the convenient things about doing this little project is the fact that Excel "understands" tables formatted in HTML, which is how we are going to output the table. You can output the information as comma seperated values (CSV) or other formats as well. Sending the output in HTML format also gives us the chance to easily apply style information, either from CSS or inline.

Here is the entire code for crosstab.php;

<?php
header("Content-Type:  application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

//connect to and select database
if(!($dbconnect = mysql_pconnect("127.0.0.1", "user", "password"))){
	print("Failed to connect to database!\n");
	exit();
	}
if(!mysql_select_db("test", $dbconnect)){
	print("Failed to select database!\n");
	exit();
	}

//query for toy list
$qtoy = "SELECT DISTINCT Toy ";
$qtoy .= "FROM tblProductToy ";
$qtoy .= "ORDER BY Toy ";

if(!($dbtoy = mysql_query($qtoy, $dbconnect))){
	print("MySQL reports: " . mysql_error() . "\n");
	exit();
	}

//query for crosstab
$qx = "SELECT DateShipped ";
while($rowx = mysql_fetch_object($dbtoy)){
		$qx .= ", SUM(IF(Toy = '$rowx->Toy', NumberShipped, 0)) AS Toy ";
	}
$qx .= ", SUM(NumberShipped) AS \"Total Shipped\" ";
$qx .= "FROM tblProductToy ";
$qx .= "GROUP BY DateShipped ";

//print($qx);

if(!($dbx = mysql_query($qx, $dbconnect))){
	print("MySQL reports: " . mysql_error() . "\n");
	exit();
	}
?>
<table border="1">
<tr>
<td bgcolor="#FFFFCC"></td>
<? php
mysql_data_seek($dbtoy, 0);
	while($rowx = mysql_fetch_object($dbtoy)){
		print("<td bgcolor=\"#FFFFCC\">");
		print("$rowx->Toy");
		print("</td>");
		}
?>
<td bgcolor="#00FFFF"><strong>Total By Date</strong></td>
</tr>
<?php
while($dbrow = mysql_fetch_row($dbx)){
	print("<tr>");
	$col_num = 0;
	foreach($dbrow as $key=>$value){
		if($dbrow[$col_num] > 0){
			print("<td>$dbrow[$col_num]</td>");
			}
		else {
			print("<td> </td>");
			}
		$col_num++;
		}
	print("</tr>\n");
	}
//total the columns
print("<tr bgcolor=\"#CCCCCC\">");
print("<td><strong>Total By Record</strong></td>");
	$alpha = b;
	$numeric = 2;
	$rows = mysql_num_rows($dbx)+1;
	for($i=1; $i < mysql_num_fields($dbx); $i++){
		print("<td><strong>=sum($alpha$numeric:$alpha$rows)</strong></td>");
		$alpha++;
		}
print("</tr>\n");
?>
</table>

For those of you who jumped ahead, cut-n-pasted the code (you know who you are!) things came out differently than you had planned. You actually got the HTML table;

This is where the "Stupid IE Trick" comes in, but more about that later. Let's look at the code that sets up the ability to be able to total columns of data within the spreadsheet.

Each calculation in an Excel spreadsheet using relative positioning of the cells requires an alphanumeric combination within the function to specify the cell information (i.e. =sum(f2:f6)). Since we are dynamically generating our query we cannot be sure how many rows or columns may be involved, therfore we have to find a way to calculate these alphanumeric combinations.

First we set some variables, $alpha = b; and $numeric = 2;. Why these values? Because the first column and row (A and 1) of the spreadsheet haave data in them other than quantities of items. Next we get the number of rows returned by the query and add 1 to it to make up for the row where the toy names are presented. Next we get the number of fields (from left-to-right) in the query, and starting at 1 (because of our offset for the column with the date in it), we loop through each field and increment the letter in the alphanumeric combo. It is all put together in this line;

print("<td><strong>=sum($alpha$numeric:$alpha$rows)</strong></td>");

that gives Excel the information that it needs to properly calculate each column in the query. As you can see, being able to use functions available to Excel can be quite a powerful tool.

But It's Not A Spreadsheet!

Now, for that "Stupid IE Trick"! *drum roll* ....
There is a bug in Internet Explorer that causes the browser to fail on recognition of some HTTP headers, including Excel. But we are still looking for that "one-click pony", so how do we overcome this problem? Thanks to input from thelist a solution was gained through the use of a phantom link, a link that fools Internet Explorer about the content type of the information. The URL to the spreadsheet needs to be like this;

By linking this way the PHP will get processed properly and the phantom crosstab.xls will say to Internet Explorer that Excel type information is coming.

Etcetera...

Once a user has gotten his spreadsheet he can save it, modify it, manipulate the numbers, do graphs, or do any number of other things (without changing data in the database) that will satisfy any number-cruncher's heart.

This article has just barely scratched the surface of the flexibility and power of being able to present data in a pivot table format within Microsoft® Excel. There may be ways to streamline and refine this code as well, offering more possibilities for using spreadsheets generated from live data. You can use an HTML form to accept input (such as date ranges or product types) to generate more specific spreadsheets. It is possible to use macros within the spreadsheet to present the data in different formats. And I suspect that it is possible to generate data for other spreadsheet programs.

"Good work Collinsgirth"

"It's Collinsworth, sir"