0

I'm trying to output data returned by an MS SQL query to an Excel or CSV file with PHP. I've used the script in this answer and can output the file OK. Without the header lines (at the bottom of my code) it saves in my server's folder structure rather than outputs as a download to the browser.

If I add the header lines, it ouputs to a CSV file but writes the page's HTML to the file rather than the extract from the database! Am I missing a setting somewhere? I tried running the code on a page with no HTML in it (PHP and SQL code only), but it still happens.

        // Give the file a suitable name:
            $FileName= $PartNumber.".csv";
            $fp = fopen($FileName, 'w');
        // Connect to MS SQL server; the actual database is chosen in the form
        // ConnSQL defined in inc/dbconn/config.php
            ConnSQL($idDatabase);

        // the query is a biggie; here it is:
            require 'inc_sql.php';

        // run it through the SQL server
            $rstBOM = sqlsrv_query($GLOBALS['ConnSQL'], $sqlBOM);

        while ($export= sqlsrv_fetch_array($rstBOM, SQLSRV_FETCH_ASSOC)) {
            if (!isset($headings))
                {
                    $headings = array_keys($export);
                    fputcsv($fp, $headings, ',', '"');
                }
                fputcsv($fp, $export, ',', '"');
            }

        // force download csv - exports HTML to CSV!
            header("Content-type: application/force-download"); 
            header('Content-Disposition: inline; filename="'.$FileName.'"'); 
            header("Content-Transfer-Encoding: Binary"); 
            header("Content-length: ". filesize($FileName)); 
            header('Content-Type: application/excel'); 
            header('Content-Disposition: attachment; filename="'.$FileName.'"');

            fclose($fp);

Any ideas where I'm going wrong please?

Calum Morrison
  • 15
  • 5

1 Answers1

0

You need to output your csv file to the browser simply by putting

readfile($FileName);

At the end of your code after the fclose($fp); function.

Otherwise, browser receives the headers for files, but no content in sent from your PHP code.

You could also generate your csv file on the fly and just echo $csvFileContents; instead. This would prevent server from creating and writing data to file, which could lead to security breaches.

Good luck!

naffiq
  • 1,030
  • 1
  • 9
  • 19
  • thanks for getting back to me, but that's still just outputting the page html to the csv file. If I take the header files out of the script with that line in place, the CSV info (from the query) is written to the html page. – Calum Morrison Feb 19 '19 at 16:58