Exporting CSV | Weird Behaviour

Question

So, I think that I have asked this question and others related to it for more than twice. But I cannot figure out what's happening.

I'm using this PHP function to export some data from a table to a CSV table. I am testing on local host ( XAAMP ):

// Export CSV By User Level
public function CSVData($path, $level) {

    switch ($level) {
        case 0:
            $filename = $path."/standard_members_".date('Y')."_".date('m')."_".date('d').".csv";
            break;
        case 1:
            $filename = $path."/special_members_".date('Y')."_".date('m')."_".date('d').".csv";
            break;
        case 2:
            $filename = $path."/admin_members_".date('Y')."_".date('m')."_".date('d').".csv";
            break;
        default:
            break;
    }


    $sql = "SELECT user_name, user_username, user_email, user_register_date INTO OUTFILE '$filename' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM users WHERE user_level = '$level'";

    if ($stmt = $this->connect->prepare($sql)) {    
        if(!($stmt->execute())){
            print_r($stmt->error);
        }
        $stmt->close();
    } else {
        $error              = true;
        $message['error']   = true;
        $message['message'] = CANNOT_PREPARE_DATABASE_CONNECTION_MESSAGE;
        return json_encode($message);
    }


    if(!is_file($filename)) {
        die('file not found');
    } else {
        header('Content-Type: application/csv');
        header("Content-Disposition: attachment; filename=$filename");
        header('Expires: 0');
        header('Pragma: no-cache');
        readfile($filename);
    }

}

And I have a form that performs the action, I mean it submits the action to the PHP file which processes the above function, and this is the action code:

<?php

    include '../assets/class/login/loginsys.php';

    $extension = new extension; 

    if ($_GET['action'] == 0) {

        $extension->CSVData('exports', 0);

    } elseif ($_GET['action'] == 1) {

        $extension->CSVData('exports', 1);

    } elseif ($_GET['action'] == 2) {

    $extension->CSVData('exports', 2);

    }

    exit();

?>

What happens is the following:

-when I click the submit button on the form I have it sends either the value 0, 1 or 2 to the action code; -the action code gets the value and processes accordingly; -but if I haven't created the folder 'exports' on localhost if would have given me this error:

Can't create/write to file 'C:xampphtdocsloginadminexports\standard_members_2012_01_28.csv' (Errcode: 2);

-and I have created it so it can work; -now that the folder it's there the CSV file is created and the data is dumped into the CSV table; -but the file that opens in my browser ( the attachment that downloads like you'd click on a file from some website and downloaded it ) it's empty even though the file exported in 'exports' has the data in it; -and another thing is that when the file already exists in the folder 'exports' the table that is created is tells me:

File 'exports/admin_members_2012_01_29.csv' already exists

So my question is why is what I described happening ? And is there a way to make the MySql query overwrite the previous CSV file ?

This SO answer will help, http://stackoverflow.com/questions/960627/mysql-into-outfile-overide-existing-file you cant overwrite the outputted file so you would need to name the file uniquely with a microtime or move the outputted file elsewhere before creating a new backup.. — Lawrence Cherone, Jan 29 '12 at 09:19
Voting to close this as "too localized", but here's a few hints for you to consider: What have you done to track down this bug? What options have you considered and tested? Where do you output the file to the browser? Are you reading the file from the same folder as MySQL does? Why don't you check for file existence before running the SQL query? — Emil Vikström, Jan 29 '12 at 09:19
@EmilVikstrom - I don't know what to do to track down the bug. The code you see is what I tested, it exports the file as described but the file downloaded by the browser is empty. I output it in a folder on my hard drive, a separate partition, not the one my OS is, but any other file behaves as it suppose to ( if I download something from a website ). I'm not sure, but no, I'm reading the file that is downloaded, but it shouldn't be empty right ? How do I check for the file existence ? I tried `if(!is_file($filename)){ unlink($filename); }` but still the file remains there. — Roland, Jan 29 '12 at 09:40
A wild guess: maybe MySQL `INTO OUTFILE` is asynchronous operation. By the time `$stmt->execute()` returns and you do `readfile` MySQL hasn't really finished the export yet? — Sim, Jan 29 '12 at 10:01

Sim · Accepted Answer · 2012-01-29T10:23:01.140

1

You don't need that temporary file. How about this:

define('CSV_SEPARATOR', ',');

// CSV download headers
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="my.csv"');

// A file handle to PHP output stream
$fp = fopen('php://output', 'w');

// UTF-8 BOM
echo "\xEF\xBB\xBF";

// Get data from database
$data = ...;

// List of columns
$columns = array(
  'User name',
  'First name',
  'Last name'
);

foreach ($data as $key => $row) {

  // Write columns
  if ($key == 0) {
    fputcsv($fp, $columns, CSV_SEPARATOR);
  }

  // Write data
  fputcsv($fp, $row, CSV_SEPARATOR);
}

fclose($fp);

Beautiful, isn't it?

edited Jan 29 '12 at 10:23

answered Jan 29 '12 at 09:34

Sim

2,627
1
20
21

Yes, I tried this kind of method with no success, and why not use MySql query when it's much easier to do it with a query rather than using PHP. – Roland Jan 29 '12 at 09:42
I am not sure what you mean, the code works perfectly. And to answer to that why: a) no temp files are created, nothing is left behind, b) code is portable (no MySQL dependency) – Sim Jan 29 '12 at 09:48
And what would that $data be ? An array or what ? – Roland Jan 29 '12 at 09:52
I'm using this method and it does work fine actually :) I had to replace the headers as application/csv, but it works now. My question is how would I input in the csv the fields name, per say: For the user name, on the top of all the column rows to have "Username" and so on ? – Roland Jan 29 '12 at 10:17
Updated code with column output. If you find my answer helpful, you should always upvote it! Thanks :) – Sim Jan 29 '12 at 10:24
Perfect, thanks. I've been looking to do this right for a few days but nobody seemed to be understanding what I want. I appreciate it :) – Roland Jan 29 '12 at 10:33

Exporting CSV | Weird Behaviour

1 Answers1