With PHP, it is extremely simple to create a file that can be interpreted by speadsheet programs, such as Excel or OpenOffice.org.
When you think about what this really means, it should be clear how simple it is. We’ll do this with by created a CSV file. CSV stands for Comma-Separated Values. So basically, we’ll print out a text file with a csv MIME type, and some separated values. The MIME type can either be “text/csv” or “text/x-csv”. The standard, per the name, is to separated the values with a comma, and all values that contain a comma should be wrapped in double quotes. For example:
we,are,separated
"And here, ",we have a, comma in a value
Another way, which may be the more common practice, per my version of OpenOffice.org, is to separate the values with a semicolon:
One;Two;Three
I’ll use the semicolon method. Here is a quick script that will create a small CSV file, which can be read by a spreadsheet program:
header("Content-type: text/csv");
$data = array(
array(
"a-1",
"a-2",
"a-3",
),
array(
"b-1",
"b-2",
"b-3",
),
array(
"c-1",
"c-2",
"c-3",
),
);
foreach($data as $row) {
echo implode(";", $row) . "\n\r";
}
Now, this will prompt the user for a download, which will end in a .php filename. If you have access, and are using Apache, you can solve this with a little .htaccess. Simply putting a .htaccess file in the directory, with this in it, should do the trick.
AddType application/x-httpd-php .csv
With that, working properly, you should be able to create PHP scripts that use a .csv extension. Then, when you run the above script, save it as “mydata.csv” and the downloader should see no connection to PHP.
Now, how can we use this in a little more realistic scenario? Well, we can use this to create CSV dumps of SQL result sets.
$query = @mysql_query($sql);
$data = array();
while($row = @mysql_fetch_assoc($query)) {
//first time through, grab the keys
if(!sizeof($data)) {
$data[] = array_keys($row);
}
$tmp = array();
foreach($row as $key=>$value) {
$tmp[] = $value;
}
$data[] = $tmp;
}
foreach($data as $row) {
echo implode(";", $row) . "\n\r";
}
And there you have it! That script should be capable of turning your queries into a spreadsheet readable file. Fun, right?
