Tag Archives: CSV

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.  

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.