Using Mysql And Php To Present Excel Spreadsheets

Posted on 19 May 2002

in Site Development

by Jay Blanchard (Jay Blanchard)

Rated 4.34 (Ratings: 11)

Want more?

More articles in Site Development

Jay Blanchard

Member info

User since: 21 Mar 2001

Articles written: 6

"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;

	Water Pistols	Balloons	Party Packs	Streamers	Total Shipped Today
2002-03-01	270	352	368	360	1350
2002-03-02	250	212	374	310	1146
2002-03-03	167	208	421	311	1107
2002-03-04	165	223	404	297	1089
2002-03-05	115	214	409	301	1039
Total By Items	967	1209	1976	1279

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.


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

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.


<?php
$qx = "SELECT DateShipped, ";
$qx .= "SUM(if(Toy = 'Water Pistol', NumberShipped, 0)) AS "Water Pistols", ";
$qx .= "SUM(if(Toy = 'Balloon', NumberShipped, 0)) AS "Balloons", ";
$qx .= "SUM(if(Toy = 'Party Pack', NumberShipped, 0)) AS "Party Packs", ";
$qx .= "SUM(if(Toy = 'Streamer', NumberShipped, 0)) AS "Streamers", ";
$qx .= "SUM(NumberShipped) AS "Total Shipped Today" ";
$qx .= "FROM tblProductToy ";
$qx .= "GROUP BY DateShipped ";
?>

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.


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

$dbtoy = mysql_query($qtoy, $dbconnect);

//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 ";
?>

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!
");
	exit();
	}
if(!mysql_select_db("test", $dbconnect)){
	print("Failed to select database!
");
	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() . "
");
	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() . "
");
	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>
");
	}
//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>
");
?>
</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;

	Balloon	Party Pack	Streamer	Water Pistol	Total By Date
2002-03-01	72			159	231
2002-03-02		14	14	28	56
2002-03-03	17	17	17	22	73
2002-03-04	13	49	85	36	183
2002-03-06		12	12	12	36
Total By Record	=sum(b2:b6)	=sum(c2:c6)	=sum(d2:d6)	=sum(e2:e6)	=sum(f2:f6)

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.


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>
");

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;


<a href="http://yourservername/crosstab.php/crosstab.xls">

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"

A long time code-jockey Jay enjoys music (especially horn bands like Tower of Power, Chicago and Here Come The Mummies), furniture building, physics, motorcycle riding and philosophy. His latest projects include several business-specific web-based tools and widgets.

Jay lives in Central Texas, but has never forgotten his South Louisiana heritage. His daughters, Kaitlyn and Brittany, are his inspiration!

Start of page header

Other Fine Evolt.org Sites

Navigation Starts

Submit

Article Categories

Highest rated articles

Help Support evolt.org

Main Page Content

Using Mysql And Php To Present Excel Spreadsheets

Want more?

Jay Blanchard

Set Up Some Test Data

Do It At The Data Level

PHP To The Rescue

But It's Not A Spreadsheet!

Etcetera...