CSV Export Class

Summary

Recently, a client asked me to add csv downloads of some data. I did some searching and looked at a few scripts but couldn't really find what I was looking for. So I wound up writing my own class to export Mambo queries to a csv file. It worked out quite well. I can export a query to a zip file with just two lines of code. It turned out to be a pretty handy class. I thought I'd share some of the methods I used.

Getting started

The first thing we need to do is decide how our text will be formatted. With that in mind, I came up with the following list:

1. Item separator - this is the character that separates the data items. A comma of course. After all, that's why the call it comma separated values (CSV).
2. Text delimiter - the character that wraps our text fields
3. Line terminator - the carriage return/line feed character(s)
4. Field name delimiter - the character that wraps the field name. I set up two of these. One for output and one for importing back into MySql. Turns out I didn't need them for MS Access. But they're there if needed.

The class constructor does two things. It loads the default configuration and does an O.S. detect to determine what carriage return/line feed to use. After I thought about it, I wondered if that was even necessary considering the person doing the download might use a different O.S. to actually import the data.

The configuration file is just a simple array stored as a php script. I thought someday I might do a small config screen for changing the defaults. It could just as easily be part of the class.

Here's the class declaration and constructor:

/**
*    Standard comma separated value conversion from mysql
*/
class mysqlToCsv {
/**
*    param string $crlf carriage return line feed
*    param string $sep field separator
*    param string $del field delimiter
*    param string $fdout field name delimiter for csv output
*    param string $fdin field name delimiter for input from csv
*    param array $_fieldList list of field objects
*/
    var $crlf = '';
    var $sep = '';
    var $del = '';
    var $fdout = '';
    var $fdin = '';
    var $_fieldList = '';

    function mysqlToCsv() {
        $this->readConfig();
        $this->getCrlf();
    }

I hope you'll do a better job of naming variables. It was late at night when I wrote this thing and I was tired of typing.

The _fieldList property will store our field information and allow us to pick the proper field delimiter. More on that later.

Working with Fields

In the next section, we'll talk about some ways to properly format the data and how to extract the information we need from MySql.

Click the Next Page link below to continue your journey.

Next Page: The Field List