PHP csv download is different to the copy of the file on the server

Question

I'm trying to make a PHP page that queries a database, creates a CSV in the tmp folder then sends that csv to the browser to download, but the file that downloads contain only the last echo in the PHP script, not the file that is stored on the server (that file is saved on the server is perfect).

<?php


$db_host = "localhost"; //can be "localhost" for local development
$db_username = "root";
$db_password = "";
$db_name = "seinventory";
$link = mysqli_connect($db_host,$db_username,$db_password,$db_name) or die(mysqli_error($link));


// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$softwareName = $_GET['soft'];

$result = mysqli_query($link,"SELECT * FROM `seinventory` WHERE software LIKE '%$softwareName%' or apps LIKE '%$softwareName%'");
$timeStamp = date('d.m.Y-h.i.s');
$csvFile = 'C:/xampp/htdocs/tmp/file.csv';
$new_csv = fopen($csvFile, 'w');
$headings = Array('PC Name','Software Name','Software Version');
fputcsv($new_csv, $headings);
while($row = mysqli_fetch_array($result))
{
    $pcName = $row['pcName'];
    $software = $row['software'];
    $app = $row['apps'];
    $softwareArray = explode(";", $software);
    $appArray = explode(";", $app);
    $multiArray = array_merge($softwareArray, $appArray);
    foreach ( $multiArray as $value ) {
        $singleSoftwareArray = explode(":", $value);
        $softwareItem = $singleSoftwareArray[0];
        $pcName = str_replace('.domain.local', '', $pcName);
        if (stripos($softwareItem, $softwareName)  !== false) {
            $singleArray = Array($pcName, $singleSoftwareArray[0], $singleSoftwareArray[1]);
            fputcsv($new_csv, $singleArray);
        }

}
}
fclose($new_csv);

mysqli_close($link);
    // tell the browser it's going to be a csv file
header('Content-Type: application/csv');
    // tell the browser we want to save it instead of displaying it
header('Content-Disposition: attachment; filename="file.csv";');


//unlink($csvFile);
echo "<script>window.close();</script>";

I read somewhere I'm supposed to put exit; after the fclose to stop it writing to the file, but my file on the server is perfect somehow it's being changed during the download process.

Try giving your file a unique name instead of reusing the same filename. — Sitethief, Feb 27 '20 at 13:25
Yeah I had that set first by creating a filename with a timestamp but it does the exact same thing the file on the server is perfect its 7KB with 217 rows but the file that chrome downloads when I go to the php file url is 1 row containing "" — Matt M, Feb 27 '20 at 13:32
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Feb 27 '20 at 22:42
You have a mistake. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) — Dharman, Feb 27 '20 at 22:43

Ravi Patel · Accepted Answer · 2020-02-27T14:08:24.453

You must echo the content of the CSV file to get the correct file. Remove the last echo from your code and replace it with this one.

echo file_get_contents('C:/xampp/htdocs/tmp/file.csv');

As you are storing the file locally you can also redirect the user to a file URL and it should trigger the download. You won't have to pass the content-disposition header if you do it. You have to remove lines providing Content-Type, Content-Disposition header, and last echo statement if you decide to do it this way.

header("Location: tmp/file.csv");

If you are creating the file just temporarily and removing it afterwards then I suggest you should store the data in memory and echo it afterwards.

<?php


$db_host = "localhost"; //can be "localhost" for local development
$db_username = "root";
$db_password = "";
$db_name = "seinventory";
$link = mysqli_connect($db_host,$db_username,$db_password,$db_name) or die(mysqli_error($link));


// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$softwareName = $_GET['soft'];

$result = mysqli_query($link,"SELECT * FROM `seinventory` WHERE software LIKE '%$softwareName%' or apps LIKE '%$softwareName%'");
$timeStamp = date('d.m.Y-h.i.s');
$new_csv = fopen('php://memory', 'w+');
$headings = Array('PC Name','Software Name','Software Version');
fputcsv($new_csv, $headings);
while($row = mysqli_fetch_array($result))
{
    $pcName = $row['pcName'];
    $software = $row['software'];
    $app = $row['apps'];
    $softwareArray = explode(";", $software);
    $appArray = explode(";", $app);
    $multiArray = array_merge($softwareArray, $appArray);
    foreach ( $multiArray as $value ) {
        $singleSoftwareArray = explode(":", $value);
        $softwareItem = $singleSoftwareArray[0];
        $pcName = str_replace('.domain.local', '', $pcName);
        if (stripos($softwareItem, $softwareName)  !== false) {
            $singleArray = Array($pcName, $singleSoftwareArray[0], $singleSoftwareArray[1]);
            fputcsv($new_csv, $singleArray);
        }

    }
}

mysqli_close($link);
    // tell the browser it's going to be a csv file
header('Content-Type: application/csv');
    // tell the browser we want to save it instead of displaying it
header('Content-Disposition: attachment; filename="file.csv";');

// set the file pointer position back to 0
rewind($new_csv);

// echo all the contents from current file pointer position(In this case from start of the file)
echo stream_get_contents($new_csv);

Both of those work perfectly thanks, but after each one, I wanted to close the tab and delete the file on the server but it's not running those 2 lines after the solutions above? — Matt M, Feb 27 '20 at 13:40
You can store the contents of a file in a variable, remove the file using the unlink and then echo the variable but these steps won't work for the second method. — Ravi Patel, Feb 27 '20 at 13:43
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Feb 27 '20 at 22:42
You have a mistake. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) — Dharman, Feb 27 '20 at 22:42

PHP csv download is different to the copy of the file on the server

1 Answers1