PHP - Streaming CSV Files to Browser

Question

I'm having issues getting CSV files to download directly to my browser without storing the files somewhere on my server. I found a tutorial (https://www.perpetual-beta.org/weblog/php-stream-file-direct.html) but I can't seem to get it working!

This is my main function:

public function exportRecord($id) {
    $contest = (string) $this->getContest($id);
    $entries = (array) $this->getEntries($id);

    $filename = sprintf('%1$s-%2$s-%3$s', str_replace(' ', '', $contest['name']), date('Ymd'), date('His'));
    $output = fopen('php://output', 'w');

    ob_start();

    $header = array(
        'First Name',
        'Last Name',
        'Address',
        'City',
        'State',
        'Zip',
        'Phone',
        'Email',
        'Item Purchased',
        'Partner Name',
        'Partner #',
        'Date Entered',
        'Subscribe'
    );

    fputcsv($output, $header);

    foreach ($entries as $entry) {
        fputcsv($output, $entry);
    }

    $string = ob_get_clean();

    header('Pragma: public');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Cache-Control: private', false);
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="' . $filename . '.csv";');
    header('Content-Transfer-Encoding: binary');

    exit($string);
}

I am calling the following two functions, which just do queries on my database...

private function getContest($id) {
    $query = sprintf(
        'SELECT name ' .
        'FROM contests ' .
        'WHERE id = %s;',
        $id
    );

    $result = $this->mysql->query($query);
    $response = '';

    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $response = $row;
        }
    } else {
        echo $this->mysql->error;
    }

    return $response;
}

private function getEntries($id) {
    $query = sprintf(
        'SELECT firstName, ' .
            'lastName, ' .
            'CONCAT(address1, ", ", address2), ' .
            'city, ' .
            'state, ' .
            'zip, ' .
            'phone, ' .
            'email, ' .
            'itemPurchased, ' .
            'partnerName, ' .
            'partnerNum, ' .
            'dateEntered, ' .
            'subscribe ' .
        'FROM entries ' .
        'WHERE contestID = %s;',
        $id
    );

    $result = $this->mysql->query($query);
    $response = '';

    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $response[] = $row;
        }
    } else {
        echo $this->mysql->error;
    }

    return $response;
}

I appreciate any help/advice that you'll give! Here is a screenshot, just to give you an idea of what the issue is: Thank you!

Answer 1

I ended up doing this with JS.

                    } else if (action == 'export') {
                    if (result.data !== false) {
                        var data = encodeURIComponent(result.data);
                        var contest = result.contest.trim();
                        var filename = contest + '.csv';
                        var dataUri = 'data:text/csv;charset=utf-8,' + data;
                        var link = document.createElement('a');

                        link.setAttribute('href', dataUri);
                        link.setAttribute('download', filename);
                        document.body.appendChild(link);
                        link.click();

                        showMessage('Export successfully created!', 'success');
                    } else {
                        showMessage('There are no entries for this contest! Nothing to export...', 'error');
                    }
                }

Answer 2

You are missing several steps from the sample by Jonathan Hollin. You need to start output buffering, you have to write the buffer out and set the headers correctly. This may need some modification but I tried to adapt the example to what you provided in this question:

<?php
public function exportRecord($id) {
    $contest = (array) $this->getContest($id);
    $entries = (array) $this->getEntries($id);

    $filename = sprintf('%1$s-%2$s-%3$s', str_replace(' ', '', $contest['name']), date('Ymd'), date('His'));

    $header = array(
        'First Name',
        'Last Name',
        'Address',
        'City',
        'State',
        'Zip',
        'Phone',
        'Email',
        'Item Purchased',
        'Partner Name',
        'Partner #',
        'Date Entered',
        'Subscribe'
    );

    export_csv($header, $entries, $filename);
}

private function export_csv($header, $data, $filename) {
    // No point in creating the export file on the file-system. We'll stream
    // it straight to the browser. Much nicer.

    // Open the output stream
    $fh = fopen('php://output', 'w');

    // Start output buffering (to capture stream contents)
    ob_start();

    // CSV Header
    if(is_array($header)){
        fputcsv($fh, $header);
    }

    // CSV Data
    foreach ($data as $row) {
        fputcsv($fh, $row);
    }

    // Get the contents of the output buffer
    $string = ob_get_clean();

    // Output CSV-specific headers
    header('Pragma: public');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Cache-Control: private', false);
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="' . $filename . '.csv";');
    header('Content-Transfer-Encoding: binary');

    // Stream the CSV data
    exit($string);
}