2

Good morning all, I need to force download csv file when the user click the button "Export CSV" , i see a lot of page (here and with google) and try a lot of thing but anything is working for me. I have one main page with this button and clicking it send a POST request (with Javascript and ajax) to another page who made the csv file and theoretically do the download of the file. This is the code of the main page:

<html>
<head>
<script  type="text/javascript"  src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.1.4/Chart.min.js"></script>
</head>
<?php   
    $filter="<button id='exportCSV' >Export CSV</button><br/></div>";
    echo $filter 
?>
<script>

  $(function() {
      $("#exportCSV").click(function(){
          var query="select * from thcu_cabtemphpc order by timetag desc limit 300";
          var table="thcu_cabtemphpc";
          //alert(query+"  "+table);
          //alert(dataString);
          $.ajax({
              type: "POST",
              url: "exportCSV.php",
              data: {cvsExp: query, table:table},
              success: function(result){
                  alert("Export in progress");
                  //$("#export").html(result);

              }
          });
         });
      });
      </script>
</html>

Of course this is just a test, i have another main page where the user can select the query and the table. This is the code of ExportCSV.php

<html>
<body>
<?php

include('connection.php');
function array2csv($bd, $query, $id){
    $sql = mysql_query($query);
    $day = gmdate("d-M-Y");
    $offset   = +2 * 3600; // timezone offset for UTC-13
    $utcTime  = gmdate( 'd.m.Y H:i:s' );
    $milliseconds ="".round(microtime(true) * 1000);
    $val= substr($milliseconds,8,10);
    //echo $val;
    $valor = gmdate( 'd-m-Y_H-i-s-'.$val, time() + $offset );
    $name= $day."_".$id."data_export_".$valor.".csv";


    $fp = fopen($name, 'w');

    while ($res=mysql_fetch_row($sql)) {
        $count = 0;
        foreach ($res as $val) {
            if ($count == 0){
                $data = gmdate("Y-m-d H:i:s", substr(($val/10000000) - 12219292800, 0,10));
                $arr[$count] = $data;
                $count++;
            } else {
                $arr[$count] = $val;
            }

        }
        $delimiter=",";

        fputcsv($fp,$arr,$delimiter);
    }
    //fpassthru($fp);
    fclose($fp);        
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="' . $name . '.csv"');
    header('Content-Length: ' . filesize($name)); 
    echo readfile($name);

}

    if(isset($_POST["cvsExp"])) {
        $query=$_POST["cvsExp"];
        $id=$_POST["table"];
        //download_send_headers("data_export_".gmdate("Y-m-d").".csv");
        array2csv($bd,$query,$id);
        die();
    }
?></body>
</html>

I try the code like this and doesn't work, i try to substite application/octet whit text/csv, i try to insert echo readfile($name) or only readfile($name) but anything work. The result of this is create a file inside the webroot and it's not good, i would like the the browser download the file. Thanks a lot.

EDIT: I try to execute only the page with the download script and it's work, the problem is when i call it with ajax!!! How it's possible? Where is the error?

user3231800
  • 153
  • 1
  • 2
  • 14
  • 2
    sending query string from client side to server side is very poor and insecure design – Akshay Hegde Oct 25 '17 at 09:18
  • Try by returning the `readfile($name)` instead echoing it. Also use `exit()` instead of `die()`. Take a look at: http://php.net/manual/en/function.readfile.php – wielo Oct 25 '17 at 09:19
  • I try but nothing change. This is just a test, the "real" page is different. – user3231800 Oct 25 '17 at 09:35

5 Answers5

1

I did not check your inner code, but surely CSV cannot start with <html> tag! You need to reply only CSV content, remove all tags but <php>.

For example:

<?php

include('connection.php');
function array2csv($bd, $query, $id){
    $sql = mysql_query($query);
    $day = gmdate("d-M-Y");
    $offset   = +2 * 3600; // timezone offset for UTC-13
    $utcTime  = gmdate( 'd.m.Y H:i:s' );
    $milliseconds ="".round(microtime(true) * 1000);
    $val= substr($milliseconds,8,10);
    //echo $val;
    $valor = gmdate( 'd-m-Y_H-i-s-'.$val, time() + $offset );
    $name= $day."_".$id."data_export_".$valor.".csv";


    $fp = fopen($name, 'w');

    while ($res=mysql_fetch_row($sql)) {
        $count = 0;
        foreach ($res as $val) {
            if ($count == 0){
                $data = gmdate("Y-m-d H:i:s", substr(($val/10000000) - 12219292800, 0,10));
                $arr[$count] = $data;
                $count++;
            } else {
                $arr[$count] = $val;
            }

        }
        $delimiter=",";

        fputcsv($fp,$arr,$delimiter);
    }
    //fpassthru($fp);
    fclose($fp);        
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="' . $name . '.csv"');
    header('Content-Length: ' . filesize($name)); 
    echo readfile($name);

}

    if(isset($_POST["cvsExp"])) {
        $query=$_POST["cvsExp"];
        $id=$_POST["table"];
        //download_send_headers("data_export_".gmdate("Y-m-d").".csv");
        array2csv($bd,$query,$id);
        die();
    }
?>

I just removed heading and trailing <html><body> tags

Tobia
  • 9,165
  • 28
  • 114
  • 219
1

HTML Tags

You specified you need a way to force the download (and there are no error messages provided) so I'm going to presume you can access it fine via the browser.

You'll need to firstly remove any HTML tags you have on that page. When you echo readfile($name);, the resulting file you want downloaded will look a bit like this:

<html>
<head>
<script  type="text/javascript"  src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.1.4/Chart.min.js"></script>
</head>

(YOUR CSV FILE HERE)

</body>
</html>

Header Functions

Any header() functions used should be sent to the client before anything else in the page; it should be at the very top of your code even before your include connection.php; line.

Tenbo
  • 144
  • 1
  • 10
  • ok, i create a new function with the header and i call it before all but still doesn't work. I change die() with exit() and delete ech and still the same. – user3231800 Oct 25 '17 at 09:34
  • Could you please add this code above your `header()` functions: `ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL);`, refresh the page and reply with what it says? That code will tell PHP to output any errors it comes across. – Tenbo Oct 25 '17 at 09:41
  • Have you changed `echo readfile($name);` to `readfile($name);`? – Tenbo Oct 25 '17 at 10:06
0

Ajax

You can't download a file using Ajax (see here).

Instead, make it a GET (or POST if you like) request to that exportCSV.php file with additional parameters, ie:

<a href="exportCSV.php?table=table&query=name_of_the_query">Export CSV</a>

Since it's a file download it won't redirect you to another page, it will just start downloading the file.

Headers

If you're combining php with html, always put the logic and all the processing at the beginning of the file, so:

<?php
  header('Content-Type: application/octet-stream');
?>
<html>
</html>

That will prevent sending wrong headers to the browser. The header() function is effective only if none of the other headers were sent.

Remember that header() must be called before any actual output is sent, either by normal HTML tags, blank lines in a file, or from PHP. (source)

Function

I need to mention, that you should NEVER use POST/GET parameters to send SQL queries! Instead, send some informations like query name, and query parameters that you can then use to build the SQL query properly and safely, ie.:

function array2scv($queryName, $queryParameters) {
  $queries = [
    'example' = 'SELECT * FROM user WHERE id = ?',
  ];
  $stmt = $mysqli->prepare($queries[$queryName]);
  $stmp->bind_param('i',  $queryParameters['id']);
  $stmp->execute();
  ...
}

Then return readfile($name) in that function instead of echoing it. And exit() instead of die(). (source)

Example

Here is a working example of a simple script:

<?php
// example csv
$url = 'http://samplecsvs.s3.amazonaws.com/Sacramentorealestatetransactions.csv';
function download_file()
{
    // getting content to use in the example
    $content = file_get_contents($url);
    $filename = '/tmp/sample.csv';

    // creating temp file
    $handle = fopen($filename, 'w+');
    fwrite($handle, $content);
    fclose($handle);

    // setting headers
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="'.basename($filename).'"');
    header('Content-Length: ' . filesize($filename));
    return readfile($filename);
}

if (isset($_GET['export']) && $_GET['export'] === '1') {
    download_file();
    exit();
}

?>
<html>
<body>
<a href="?export=1">Export</a>
    </body>
</html>

I hope that this helped.

wielo
  • 67
  • 5
  • Hi, thanks for help, this page is just a test for semplify the situation. In "real" page i send only the information, i try your suggestion but still doesn't work. – user3231800 Oct 25 '17 at 09:52
  • sorry to hear that... I have updated my answer, take a look and see if you can fix your code. Also, I found one more bug in your code: `$name` already has `.csv` in it, but when you're setting the `Content-disposition` header you add again `csv` at the end. – wielo Oct 25 '17 at 21:56
0

See this is my simple code for download csv dynamically.

if(isset($_POST['somebutton']))
{
 $filename = "somefilename.csv";
 $fp = fopen('php://output', 'w');

 $query = "select * from thcu_cabtemphpc order by timetag desc limit 300";    
 $result = mysql_query($query);
 for($i=0;$i<=7;$i++)
 {
    $header[] = mysql_field_name($result, $i);
 }

 header('Content-type: application/csv');
 header('Content-Disposition: attachment; filename='.$filename);
 fputcsv($fp, $header);

 $query = "select * from thcu_cabtemphpc order by timetag desc limit 300";    
 $result12= mysql_query($query);

 while($row12 = mysql_fetch_row($result12)) {
  fputcsv($fp, $row12);
 }
exit;
}

This is my code for download simple Excel. and its working Quite Fine.put this php anywhere and you'll able to download csv.

0

First thing i would say thanks to all for lot of answers, today i find a solution and for me it work, i add in the main page this:

$(function() {
        $('#CSV').click(function(){
            $('#wait-animation').show();
            var where="<?php Print($where) ?>";
            var table="<?php Print($id) ?>";
            //var table="thcu_cabtemphpc";
            alert("Export in progress");
            alert(query);
            document.location.href = 'exportCSV.php?where='+where+'&table='+table;
            $('#wait-animation').hide();
      });
  });

and in the export page:

<?php
include('connection.php');
header("Last-Modified: " . @gmdate("D, d M Y H:i:s",$_GET['timestamp']) . " GMT");
header("Content-type: text/x-csv");
// If the file is NOT requested via AJAX, force-download
if(!isset($_SERVER['HTTP_X_REQUESTED_WITH']) || strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) != 'xmlhttprequest') {
    header("Content-Disposition: attachment; filename=search_results.csv");
}
$table=$_GET['table'];
$where=$_GET['where'];
$day = gmdate("d-M-Y");
$offset   = +2 * 3600; // timezone offset for UTC-13
$utcTime  = gmdate( 'd.m.Y H:i:s' );
$milliseconds ="".round(microtime(true) * 1000);
$val= substr($milliseconds,8,10);
$valor = gmdate( 'd-m-Y_H-i-s-'.$val, time() + $offset );
$filename= $day."_".$table."data_export_".$valor.".csv";

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

$query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='monitoring' AND TABLE_NAME='".$table."'";
$result = mysql_query($query);
while ($row = mysql_fetch_row($result)) {
    $header[] = $row[0];
}   

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
fputcsv($fp, $header);


$result = mysql_query("select * from ".$id." where ".$where);
while($row = mysql_fetch_row($result)) {
    fputcsv($fp, $row);
}
return $filename;
exit;
?>

Thanks a lot. Have a nice day.

user3231800
  • 153
  • 1
  • 2
  • 14