Updating the Drop List Download Application

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.