Web Dev Blog

Background

PHP is not a strictly typed language, and many programmers often overlook problems that can be caused with not paying attention to numeric types. The problem is that PHP does not alert you when you have gone outside of the bounds of what an integer can hold. Instead, it silently converts your value to a float (floating point number.)

Why should you care? Mathematic expressions with floats are not always exact – sometimes they can be way off. PHP, like most programming languages, uses shortcuts to estimate the result of floating point mathematic expressions. The rule of thumb is that the larger the numbers you are working with, the greater amount of estimations involved in finding a result. If you were programming a PHP-based financial application, this could be a huge liability.

The best way to handle large numbers in PHP is to be aware of exactly what type of number you are dealing by knowing the PHP bounds of integers. PHP supplies a constant PHP_INT_MAX which will tell you what the maximum integer is for your instance of PHP. Not all computers/operating systems/versions of PHP are the same, so it is good to find the PHP_INT_MAX for each setup. Note that the PHP_INT_MAX also determines the smallest negative number – just multiply it by -1 to get the PHP integer minimum.

PHP Integer Testing Script

Below is a testing script to help you determine the PHP_INT_MAX:

<?php
$intMax = PHP_INT_MAX;
 
echo "$intMax<br />";
 
if (is_int($intMax)) {
	echo "$intMax is an Int<br />";
}
else {
	echo "$intMax is NOT an Int<br />";
}
 
if (is_int($intMax + 1)) {
	echo "$intMax+1 is an Int<br />";
}
else {
	echo "$intMax+1 is NOT an Int<br />";
}
 
if (is_int($intMax * (-1))) {
	echo "$intMax*(-1) is an Int<br />";
}
else {
	echo "$intMax*(-1) is NOT an Int<br />";
}
?>

The result of this script:

2147483647
2147483647 is an Int
2147483647+1 is NOT an Int
2147483647*(-1) is an Int

PHP Float Testing Script

Now that you know the bounds of integers, what do you do? Truth be told, there is not a whole lot you can do to guarantee the integrity of your floating point numbers. It may be more important to know which figures are the result of math involving floating point numbers, so you can disclose this to your audience, and act accordingly. See the simple example below that shows how poorly PHP can do math involving floating point numbers:

<?php
//TEST 1
$number = 216897510871;
$original_number = $number;
 
$number *= 11123.74;
$number /= 11123.74;
 
if ($original_number == $number) {
	echo "Test 1: Are Equal<br />";
}
else {
	echo "Test 1: Are NOT Equal<br />";
}
 
//TEST 2
$number = 216897510871;
$original_number = $number;
 
$number *= 11123.75;
$number /= 11123.75;
 
if ($original_number == $number) {
	echo "Test 2: Are Equal<br />";
}
else {
	echo "Test 2: Are NOT Equal<br />";
}
?>

The result of this script:

Test 1: Are Equal
Test 2: Are NOT Equal

Background

Previously we have seen how to make a native Excel spreadsheet through the use of the PEAR extension. This article is about generating comma (.csv.) and tab delimited spreadsheets that are readable in many popular programs, and useful in a multitude of websites and applications. You need no external packages or extensions to generate these files - PHP does this natively.

Differences

Comma separated value files known by their .csv extension are the most popular alternative to the native Excel .xls spreadsheet file. One problem is that .csv files are actually text files with column values separated with commas, and this can cause problems when your data also has commas in it. The additional code necessary to solve this problem is often not worth it, so many people generate tab delimited files with PHP instead. When creating tab delimited files, you can use both the native Excel .xls extension or the .tsv (tab separated values) extension. However, a user’s OS may not be setup to recognize the .tsv extension and may try to open it in a regular text editor. The better option is to use the Excel .xls extension for your tab delimited files to guarantee that the user will be able to open it successfully with a myriad of browser, OS, and software combinations - I have found that many other non Microsoft spreadsheet programs support the .xls extension. In the below script, I have opted below for an .xls tab delimited file, but I have also included the necessary changes in the comments to generate .csv files as well.

PHP Code

• Execute the below script and generate an .xls tab delimited file.
• Execute the below script and generate a .tsv tab separated values file.

<?php
/* Data to be inserted into excel in an array of arrays
 * Each array is the avg monthly temp of a different city */
$data = array ( array ( "City" => "Seattle",
	"Jan" => 46, "Feb" => 50, "Mar" => 53, "Apr" => 58,
	"May" => 64, "Jun" => 70, "Jul" => 75, "Aug" => 76,
	"Sep" => 70, "Oct" => 60, "Nov" => 51, "Dec" => 46 ),
	array ( "City" => "New York",
	"Jan" => 39, "Feb" => 42, "Mar" => 50, "Apr" => 60,
	"May" => 71, "Jun" => 79, "Jul" => 85, "Aug" => 83,
	"Sep" => 76, "Oct" => 64, "Nov" => 54, "Dec" => 44 ),
	array ( "City" => "San Francisco",
	"Jan" => 56, "Feb" => 59, "Mar" => 61, "Apr" => 64,
	"May" => 67, "Jun" => 70, "Jul" => 71, "Aug" => 72,
	"Sep" => 73, "Oct" => 70, "Nov" => 62, "Dec" => 56 )
);
 
$csvTitle = "Monthly Average High Temperature By City";
 
/* We know the keys of each sub-array are the same, so
 * extract them from the first sub-array and set them
 * to be our column titles */
$titleArray = array_keys($data[0]);
 
/* Set your desired delimiter. You can make this a true
 * .csv and set $delimiter = ","; but I find that tabs
 * work better as commas can also be present in your data.
 * Note that you must use the .tsv or .xls file extension for Excel
 * to correctly interpret tabs. Otherwise if you are using commas
 * for your delimiter, use .csv for your file extension. */
$delimiter = "\t";
 
//Set target filename - see above comment on file extension.
$filename="Search_Export.xls";
 
//Send headers
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");
 
//print the title to the first cell
print $csvTitle . "\r\n";
 
//Separate each column title name with the delimiter
$titleString = implode($delimiter, $titleArray);
print $titleString . "\r\n";
 
//Loop through each subarray, which are our data sets
foreach ($data as $subArrayKey => $subArray) {
	//Separate each datapoint in the row with the delimiter
	$dataRowString = implode($delimiter, $subArray);
	print $dataRowString . "\r\n";
}
?>

Background

Generating spreadsheets in PHP is a powerful tool. Natively, PHP can generate .csv spreadsheets with little fuss by sending a header to the browser to generate csv output. This article is not about .csv files, but rather generating Excel Spreadsheets with PHP.

Benefits of using actual Excel format (.xls) files:

• Standard spreadsheet format for most offices
• Rich formatting can be saved in the file
• Less confusion for casual computer users

PEAR Setup

To generate Excel files we will be using the PEAR package Spreadsheet_Excel_Writer. PEAR stands for the PHP Extension and Application Repository and contains useful tools and functions that you can use in your PHP scripts. PEAR is closely tied to PHP development, and PHP can be configured to use PEAR easily. For installation instructions see: http://pear.php.net/manual/en/installation.php.

If you are like me and you have your site running on a shared host where you do not have the luxury to install and change whatever you want, you can still use PEAR in your site. There are instructions in the above installation page for installing it on a shared host.

For testing purposes, I have bundled the necessary Spreadsheet_Excel_Writer and OLE package PEAR files into a zip file so you can extract it to your web root and create Excel spreadsheets easily without the full installation of PEAR. I changed all include() paths so that they will work with a different directory structure than the default PEAR installation. Please note that this is not an official PEAR code release and should only be used for testing purposes. Download the testing package here.

Creating the Excel file with PHP

Using this package is relatively easy, once you understand the flow of how it works. The package provides many, many formatting functions. See the list of formatting releated functions in the package’s official documentation: http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.php.

Below is a code example that generates a fairly simple spreadsheet. Click here to execute the code.

<?php
/*This require call will be different depending on how your PEAR
 *is installed. The below call works with the zipped test PEAR
 *files I have provided if your script is in the same folder as
 *the zipped PEAR folder.
 *Normally, If you have PEAR installed correctly, you would
 *use the call: require_once('Spreadsheet/Excel/Writer.php'); */
require_once('PEAR/Spreadsheet_Excel_Writer/Writer.php');
 
/* Data to be inserted into excel in an array of arrays
 * Each array is the avg monthly temp of a different city */
$data = array ( array ( "City" => "Seattle",
	"Jan" => 46, "Feb" => 50, "Mar" => 53, "Apr" => 58,
	"May" => 64, "Jun" => 70, "Jul" => 75, "Aug" => 76,
	"Sep" => 70, "Oct" => 60, "Nov" => 51, "Dec" => 46 ),
	array ( "City" => "New York",
	"Jan" => 39, "Feb" => 42, "Mar" => 50, "Apr" => 60,
	"May" => 71, "Jun" => 79, "Jul" => 85, "Aug" => 83,
	"Sep" => 76, "Oct" => 64, "Nov" => 54, "Dec" => 44 ),
	array ( "City" => "San Francisco",
	"Jan" => 56, "Feb" => 59, "Mar" => 61, "Apr" => 64,
	"May" => 67, "Jun" => 70, "Jul" => 71, "Aug" => 72,
	"Sep" => 73, "Oct" => 70, "Nov" => 62, "Dec" => 56 )
);
 
$sheetTitle = "Monthly Average High Temperature By City";
 
/*We know the keys of each sub-array are the same, so
 *extract them from the first sub-array and set them
 *to be our column titles */
$columnTitles = array_keys($data[0]);
$numColumns = count($columnTitles);
 
//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
 
//Sending HTTP headers
$workbook->send('Search_Export.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Export');
$worksheet->setLandscape();
 
//set all columns same width
$columnWidth = 10;
$worksheet->setColumn (0, $numColumns, $columnWidth);
 
//Setup different styles
$sheetTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'size'=>10));
$columnTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'top'=>1,
  'bottom'=>1 ,
  'size'=>9));
$regularFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'textWrap'=>1));
 
/*Speadsheet writer is in format y,x (row, column)
 *  column1  |  column2 |  column3
 *   (0,0)      (0,1)      (0,2) */
 
$column = 0;
$row    = 0;
 
//Write sheet title in upper left cell
$worksheet->write($row, $column, $sheetTitle, $sheetTitleFormat);
 
 
//Write column titles two rows beneath sheet title
$row += 2;
foreach ($columnTitles as $title) {
  $worksheet->write($row, $column, $title, $columnTitleFormat);
  $column++;
}
 
//Write each datapoint to the sheet starting one row beneath
$row++;
foreach ($data as $subArrayKey => $subArray) {
  //Loop through each row
  $column = 0;
  foreach ($subArray as $value) {
    //Loop through each row's columns
    $worksheet->write($row, $column, $value, $regularFormat);
    $column++;
  }
  $row++;
}
$workbook->close();
?>

Notes

There has not been any further development on this PEAR package since 2006, and there is a long user-submitted bug list on their site. Most of the bugs are related to the more complicated functionality possible with this package. To be careful, I would not make any generated spreadsheet too complicated in a production level application. I have also heard that very large spreadsheets (2mb+) can crash your script, so you may want to set a limit on the number of rows you are going to write.

If you’re a PHP programmer, but you’ve never used register_shutdown_function(), you are not alone. Buried in the Function Handling Functions section of the PHP manual, this function is seldom in PHP programming books or tutorials. However it is very useful in many situations where the integrity of your data is essential.

The function register_shutdown_function() takes the name of a user-defined function as an argument, and then calls that function when the script terminates. As long as you call register_shutdown_function() at the top of your script, your function will be executed even if there is a PHP error or the script times out. The function will also be called if the script executes normally, with no errors. 

Because of the guarantee that your shutdown function will be called, you can execute essential commands in your function and be certain of their result. Common uses include updating session variables or updating values in a database.

For example, say you have a script that loops through transactions, processing each one, and then updates a database with the total number of transactions processed. A good use of reigster_shutdown_function() would be to perform the database update of the number of transactions, because you can be assured that it will be executed. See example below:

<?php
register_shutdown_function('safe_update');
$done_processing=false;
$number_processed=0;
 
while($done_processing==false)
{
  //PROCESS A TRANSACTION...
  $number_processed++;
}
 
function safe_update()
{
  global $number_processed;
  //UPDATE DATABASE WITH $number_processed
}
?>