Christian Little

Often times as a programmer I’m forced to use more creative solutions than are normally available. This typically applies to the case where somebody wants data from their database exported into a different program. Unfortunately, a lot of the time the standard mysql export feature isn’t an option (as they want to manipulate the data before it gets put into a file, or some other reason).

So here is the framework to do this:

$table = “sometable”;
define(”FILENAME”, “bob.csv”);
$fhandle = fopen(FILENAME, “w”);
$exportquery = “select * from $table”;
$header = “”;
$rows = “”;
$rowcounter = 0;
$exportresult = mysql_query($exportquery);
while($row = mysql_fetch_array($exportresult, MYSQL_ASSOC)) {
     $rows = “”;
     // Construct the Header row
     if(!$rowcounter) {
          $rowcounter++;
               foreach ($row as $key => $value) {
               $header .= ereg_replace(”,”, “”, $key) . “,”;
               }
          $header = rtrim($header, “,”);
          $header .= “\n”;
          fwrite($fhandle, $header);
     }

     // Construct a normal row
     foreach ($row as $key => $value) {
          $rows .= ereg_replace(”,”, “”, $value) . “,”;
     }
     $rows = rtrim($rows, “,”);
     $rows .= “\n”;
     fwrite($fhandle, $rows);
}
fclose($fhandle);

In a nutshell, this code will take all the data from any table in a MySQL database and export it to a CSV file. The cool thing about this code is that is uses associative arrays to parse the data, so it doesn’t matter what the field names are, it will always use the correct name.

If you take this code and simply change the $table variable and add your mysql_connect and mysql_select_db commands right at the top, it will dump the entire table for you in a fraction of a second to a file called bob.csv (which you can change on line 2 of the code to whatever name you want).

You can read through the code and figure it out if you want, but here is a basic logic charge for it:

• Query the table, then for every row that is returned:
  • If this is the first row, then get the field names and create a header row to put at the top of the file.
  • Parse each field into the csv file accompanied by a comma
  • When an entire row has been parsed, it chops off the extra comma on the end of the row and replaces it with a newline character

It’s very straight forward logic, and works almost identically to the built-in export/dump functions.

The other nice part about this code is that it will work with very complex queries involving multiple joins. So you could do this:

$exportquery = “select
t1.id as ID,
t1.name as Name,
t2.Email as Email,
FROM $table t1
LEFT JOIN emails t2 ON t1.ID = t2.ID
LIMIT 100″

That query will work in this code, and it will output a CSV file with 100 rows of data containing ID, Name, and Email from 2 tables in your database. If you try to do this in MySQL directly with an export or dump command, it’s not as easy and simple.

Happy coding everybody!