2

I'm having a hard time making my CSV export function work.
I've found around plenty of examples using mysqli_* functions, but I'm actually using PDOs so I had to adapt some questions/answers to my needs.
From what I can see, I'm properly parsing and writing data to the *.csv file, but at the end I don't simply get any "Download" modal from the browser.
Again, looking around, I've understood I may have some kind of problem with my headers, so I'm asking for your help.

This is my PHP function snippet:

function downloadAsCSV($dsn, $dbUser, $dbPsw, $options) {
    // New PDO connection.
    $pdo = pdoConnect($dsn, $dbUser, $dbPsw, $options);

    $query = ... // Not going to write it down.

    // Let's query the database, ...
    $stmt = $pdo->prepare($query);

    // Setting up the query variables here...
    [...]
    // Executing the statement...
    $stmt->execute();

    // Headers.
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header('Content-Description: File Transfer');
    header("Content-Type: text/csv");
    header("Content-Disposition: attachment; filename='export.csv'");
    header("Pragma: no-cache");
    header("Expires: 0");

    // Let's open tbe "*.csv" file where we'll save the results.
    $fp = fopen("php://output", "w");

    if ($fp) {
        $first_row = $stmt->fetch(PDO::FETCH_ASSOC);
        $headers = array_keys($first_row);
        fputcsv($fp, array_values($first_row));
        fputcsv($fp, $headers);

        // ... fetch the results...
        $workpiecesArray = $stmt->fetchAll();

        // ... and, finally, export them.
        foreach ($workpiecesArray as $workpiece) {
            fputcsv($fp, array_values($workpiece));
        }
    }

    fclose($fp);

    // Closing the connection.
    $stmt = null;
    $pdo = null;
}

function mysqli_field_name($result, $field_offset) {
    $properties = mysqli_fetch_field_direct($result, $field_offset);

    return is_object($properties) ? $properties->name : null;
}

I've taken inspiration to write the table column titles from this answer.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Davide3i
  • 1,035
  • 3
  • 15
  • 35

2 Answers2

3

What you need to do is to print the file, which you're not doing right now. See this example using readfile: http://php.net/manual/en/function.header.php#example-5554

Simply put:

1.Replace 

$fp = fopen("php://output", "w");

with

$tmpfname = tempnam("/", "");
$fp = fopen($tmpfname, "w");

2.Replace 

fclose($fp);

with

fclose($fp);
readfile($tmpfname);
unlink($tmpfname);

and this will work

Jan Myszkier
  • 2,714
  • 1
  • 16
  • 23
  • I'm not reading a file, but getting the data from a MySQL DB using PDOs. I'm sorry, but I probably didn't get what you were meaning. – Davide3i May 21 '18 at 09:43
  • @Davide3i see my updated answer with step-by-step on what to do with your code – Jan Myszkier May 21 '18 at 10:33
  • Hi, thanks. Unfortunately it still not works. The only unusual thing I'm seeing is that the file pointer is like this: "resource id='6' type='stream'". – Davide3i May 21 '18 at 11:35
  • please recheck the variable names, I checked the code locally and it does exactly what you described – Jan Myszkier May 21 '18 at 11:37
  • For what I can see, my *.tmp file is being created (containing the data got from the DB), but at the end it just get deleted and I can't obtain any *.csv file: it should put it in the same directory, right? – Davide3i May 21 '18 at 11:44
  • If you need the CSV saved on disk, simply remove unlink from the code. that takes care of file removal. if you need the file to be saved in somewhere else than /tmp, you can modify `$tmpfname = tempnam("/", "");` and use something like `$tmpfname = "/my/absolute/path/to/file.csv";` – Jan Myszkier May 21 '18 at 12:01
  • I don't really know why, but the *.tmp file gets correctly created, but even after removing `unlink($tmpName);`, I can't simply get any *.csv file (the browser is not prompting me to download it) and the *.tmp file disappears. I can't get what I'm doing wrong. Thank you for your help. – Davide3i May 21 '18 at 12:04
  • 1
    Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/171465/discussion-between-janmyszkier-and-davide3i). – Jan Myszkier May 21 '18 at 12:08
1
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="export.csv"');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize('export.csv'));
readfile($csvFile);
exit;
`put this after` fclose($fp);
Azhar
  • 101
  • 2
  • 7