I received an email from someone who wanted to download the output of an MySQL Query from a DropList Database, but it were to use on a windows client. The code snippet series I posted used /n instead of the /r/n, and would require the use of unix2dos, even a simple str_replace, but while looking for a solution on another project I came across Stream Filters, which can be applied in this instance quite elegantly.
I would have gone around the issue by processing the query output before producing the download. However this email weren’t asking for that, so I thought of a different solution, of which I came up with 2 or 3 different ways. I always think of Larry Wall the creator of PERL who once said “Theres always more than one way to do it”, which is nigh on a PERL Mantra but in PHP there are usually half dozen different ways to do it, then a dozen more overkill ways on top.
I hadn’t really used Stream Filters that much, and only then for handling affiliate feeds which are CSV files. This function’s code is by a chap called Torge, and can be found on this StackOverflow post Fputcvs and New Lines. Bending and fusing the function to this purpose is actually quite elegant… and I like it.
<?php
include('../con.sql.php');
$today = date("Y-m-d");
class StreamFilterNewlines extends php_user_filter {
function filter($in, $out, &$consumed, $closing) {
while ( $bucket = stream_bucket_make_writeable($in) ) {
$bucket->data = preg_replace('/([^\r])\n/', "$1\r\n", $bucket->data);
$consumed += $bucket->datalen;
stream_bucket_append($out, $bucket);
}
return PSFS_PASS_ON;
}
}
$query = "SELECT domain FROM `zonefile` WHERE `dropdate` = '$today';";
$result = mysql_query($query);
$fp = fopen('php://output', 'w');
if ($fp && $result) {
stream_filter_register("newlines", "StreamFilterNewlines");
stream_filter_append($fp, "newlines");
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="'.$today.'.txt"');
header('Pragma: no-cache');
header('Expires: 0');
while ($row = mysql_fetch_row($result)) {
fputcsv($fp, array_values($row));
}
die;
}
?>
The above fusion of Torge’s code and my code creates a windows encoded text file based on the MySQL query output and starts/prompts a download of said file. It could be neatened up, and for public deployment certain needs some bomb proofing.
How you modify the date / change the query is down to you. I would perhaps use a form with a submit via post with some sanitising, but this works for a simple download todays list script.
Extending The Drop List Application
You could even modify the download aspect and have it write to a file or email the download to you or a client.
In a similar tool, I have something like this which runs on a cron at midnight to create a downloadable file, which also becomes a historic record for the days list. This way multiple people can download the list without the server having to process the MySQL and increase the server load every time.
Before I used a Cron like above, the first user of the day ran the query and subsequent users downloaded the output. The problem were the first user could end up loading it during a critical time or it would take too long.
I used a simple if…FileName…Exist…PromptDownload…else…RunQuery type switch, ultimately the CRON method is faster and better to maintain, the code above can be modified this way.
A Manchester Based Photographer and Website Developer with interests in Strongman, Fitness and Geekery.