---

Use this little script to export a mysql table into a csv. Output the headers for downloading the csv as an attachment.
<?php

$user = 'jayjohnston';
$pass = 'j@yj0hn$ton';
$dsn  = 'localhost:1972';
$db   = 'dbjuju';
$link = mysql_connect($dsn,$user,$pass);
mysql_select_db($db);

// place the list of fieldnames from the mysql table here:
$flds  = 'id,firstname,lastname,company,city,state,zip';

// create the query to pull the table data
$query = 'select '.$flds.' from regs';

$query_resource = mysql_query($query,$link);
$rows = array();
$i = 0;
while($ary = mysql_fetch_assoc($query_resource)) {
  while (list($key,$value) = each($ary)) {
    $rows[$i][$key] = $v;
  }
  $i++;
}

// specify the type of line ending we want
// other options might be "nr" or "r" or chr(13), etc
$end = "n";

// these are the content type headers to send in php that
// 1) force the type to be CSV
header("Content-type: application/csv");
// 2) force the link to send something for download
header("Content-Disposition: attachment; filename=export.csv");
// 3) attempt to prevent caching
header("Pragma: no-cache");
header("Expires: 0");

// here we output the column headers:
echo $flds.$end;

// switch the comma delimited list of fields into an array
$flds = explode(',',$flds);

// loop over the array of table rows
foreach ($rows as &$row) {
  $cols = array();

  // loop over each of the fields in the flds array
  foreach($flds as &$fld) {

    // prepare the text for csv formatting
    $v = str_replace('"','""',trim($row[$fld]));

    // create an array of columns 
    $cols[] = '"'.$v.'"';
  }

  // for each row, implode to a csv line the $cols
  echo implode(',',$cols).$end;
}



PHP - MySQL Table dump to CSV

Connect to the database

Connecting to the database is elementary, this snippet orginally used my favorite db abstraction layer, but I expanded it to be the raw calls for snippet integrity.

Query the MySQL database

Using an sql database abstraction layer will allow a much simpler usage. There is no reason to use the following in every line of code:





Instead, use a class that does the work for you:


class db {
    function __construct($dsn,$user,$pass,$db) {
    $this->connection = mysql_connect($dsn, $user, $pass);
    if (!mysql_select_db($db, $this->connection)) {
      die('<div>Could not connect to the database...'.
          '<br />Please notify the system administrator'.
          ' and/or try again later.</div>');
    } else {
      return $this->connection;
    }
  }

  function select($query) {
    $result = mysql_query($query, $this->connection);
    $select_array = Array();
    if ($result===false) return $select_array;
    $i = 0;
    while ($ary = mysql_fetch_assoc($result)) {
      while (list($key, $value) = each($ary)) {
        $select_array[$i][$key] = $value;
      }
      $i++;
    }
    return $select_array;
  }
}


Then we can just get our rows this way:


$rows = $db->select($query);


Set the content type headers for CSV

PHP uses the header() function to send raw http headers to the browser. The Content-type tells the browser what type of file is being sent so that the appropriate, operating system specific application handler (like Excel, for instance) can open the file.

Loop over the MySQL table rows and output a CSV

All the hard work is done, the ducks are in a row, and we need simply create comma separated, double quoted lines: one for each row in the table.


...
   // create an array of columns 
    $cols[] = '"'.$v.'"';
  }

  // for each row, implode to a csv line the $cols
  echo implode(',',$cols).$end;
...


It should be of particular interest that concatenation of the CSV lines requires us to know when we've reached the final column, whereas imploding an array of column values removes that oddity in the concatenation process as well as providing a more flexible entity that could also be used in other forks of business logic.

Last Updated: 2011-08-03 14:28:22