[php] Export to CSV via PHP

Let's say I have a database.... is there a way I can export what I have from the database to a CSV file (and text file [if possible]) via PHP?

I recommend parsecsv-for-php to get around a number any issues with nested newlines and quotes.

Just like @Dampes8N said:

$result = mysql_query($sql,$conecction);
$fp = fopen('file.csv', 'w');
while($row = mysql_fetch_assoc($result)){
    fputcsv($fp, $row);
}
fclose($fp);

Hope this helps.

Works with over 100 lines, if you specify the size of the file in the headers simple call the get() method in your own class

function setHeader($filename, $filesize)
{
    // disable caching
    $now = gmdate("D, d M Y H:i:s");
    header("Expires: Tue, 01 Jan 2001 00:00:01 GMT");
    header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
    header("Last-Modified: {$now} GMT");

    // force download  
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header('Content-Type: text/x-csv');

    // disposition / encoding on response body
    if (isset($filename) && strlen($filename) > 0)
        header("Content-Disposition: attachment;filename={$filename}");
    if (isset($filesize))
        header("Content-Length: ".$filesize);
    header("Content-Transfer-Encoding: binary");
    header("Connection: close");
}

function getSql()
{
    // return you own sql
    $sql = "SELECT id, date, params, value FROM sometable ORDER BY date;";
    return $sql;
}

function getExportData()
{
    $values = array();

    $sql = $this->getSql();
    if (strlen($sql) > 0)
    {
        $result = dbquery($sql); // opens the database and executes the sql ... make your own ;-) 
        $fromDb = mysql_fetch_assoc($result);
        if ($fromDb !== false)
        {
            while ($fromDb)
            {
                $values[] = $fromDb;
                $fromDb = mysql_fetch_assoc($result);
            }
        }
    }
    return $values;
}

function get()
{
    $values = $this->getExportData(); // values as array 
    $csv = tmpfile();

    $bFirstRowHeader = true;
    foreach ($values as $row) 
    {
        if ($bFirstRowHeader)
        {
            fputcsv($csv, array_keys($row));
            $bFirstRowHeader = false;
        }

        fputcsv($csv, array_values($row));
    }

    rewind($csv);

    $filename = "export_".date("Y-m-d").".csv";

    $fstat = fstat($csv);
    $this->setHeader($filename, $fstat['size']);

    fpassthru($csv);
    fclose($csv);
}

Just for the record, concatenation is waaaaaay faster (I mean it) than fputcsv or even implode; And the file size is smaller:

// The data from Eternal Oblivion is an object, always
$values = (array) fetchDataFromEternalOblivion($userId, $limit = 1000);

// ----- fputcsv (slow)
// The code of @Alain Tiemblo is the best implementation
ob_start();
$csv = fopen("php://output", 'w');
fputcsv($csv, array_keys(reset($values)));
foreach ($values as $row) {
    fputcsv($csv, $row);
}
fclose($csv);
return ob_get_clean();

// ----- implode (slow, but file size is smaller)
$csv = implode(",", array_keys(reset($values))) . PHP_EOL;
foreach ($values as $row) {
    $csv .= '"' . implode('","', $row) . '"' . PHP_EOL;
}
return $csv;
// ----- concatenation (fast, file size is smaller)
// We can use one implode for the headers =D
$csv = implode(",", array_keys(reset($values))) . PHP_EOL;
$i = 1;
// This is less flexible, but we have more control over the formatting
foreach ($values as $row) {
    $csv .= '"' . $row['id'] . '",';
    $csv .= '"' . $row['name'] . '",';
    $csv .= '"' . date('d-m-Y', strtotime($row['date'])) . '",';
    $csv .= '"' . ($row['pet_name'] ?: '-' ) . '",';
    $csv .= PHP_EOL;
}
return $csv;

This is the conclusion of the optimization of several reports, from ten to thousands rows. The three examples worked fine under 1000 rows, but fails when the data was bigger.

You can export the date using this command.

<?php

$list = array (
    array('aaa', 'bbb', 'ccc', 'dddd'),
    array('123', '456', '789'),
    array('"aaa"', '"bbb"')
);

$fp = fopen('file.csv', 'w');

foreach ($list as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);
?>

First you must load the data from the mysql server in to a array

pre-made code attached here. you can use it by just copying and pasting in your code:

https://gist.github.com/umairidrees/8952054#file-php-save-db-table-as-csv