phpmysqlcsvfputcsv

fputcsv Creates the file but downloads it empty


I have this piece of PHP code that's intended to retrieve data from a mySQL database, and export it to a CSV file that has to be automatically downloaded after it was created.

$connection = mysqli_connect($host, $username, $password, $dbname) or die("Connection Error " . mysqli_error($connection));

// fetch mysql table rows
$sql = "select * from users";
$result = mysqli_query($connection, $sql) or die("Selection Error " . mysqli_error($connection));

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

while($row = mysqli_fetch_assoc($result)) {
    fputcsv($fp, $row);
}

fclose($fp);

header('Content-Type: text/csv');

header('Content-Disposition: attachment; filename="users.csv"');

mysqli_close($connection);

The problem here is that it:

Thanks.


Solution

  • You're writing it to a file called users.csv, but the file you are forcing the user to download is the output of the page.

    As long as your query is correct, once the PHP script has run, there should be a file called users.csv in the same directory as the PHP file that contains the correct data.

    You need to output the data to the browser for it to be attributed to the file you're downloading.

    Try this:

    //Connect to database
    $connection = mysqli_connect($host, $username, $password, $dbname) or die("Connection Error " . mysqli_error($connection));
    
    //Fetch mysql table rows
    $sql = "select * from users";
    $result = mysqli_query($connection, $sql) or die("Selection Error " . mysqli_error($connection));
    
    //Close connection
    mysqli_close($connection);
    
    //Set $output
    $output = "";
    
    //Set header values
    $headers = array("Header 1", "Header 2", "Header 3");
    
    //Insert header values to $output
    foreach($headers as $h){
        $output .= fieldCheck($h) . ",";
    }
    $output = rtrim($output, ","). "\n";
    
    //Iterate through results
    while($row = mysqli_fetch_assoc($result)) {
        foreach($row as $cell){
            //Comma-separate each value
            $output .= fieldCheck($cell).",";
        }
        //Remove last comma of each line and add newline
        $output = rtrim($output, ",") . "\n";
    }
    
    //Set headers
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="users.csv"');
    
    //Output
    echo $output;
    
    exit;
    
    //Function in case of comma in field
    function fieldCheck($string){
        if(strpos($string, ",") !== false){
            $string = '"'.$string.'"';
        }
        return $string;
    }