Create CSV File by php, ajax and mysql

Question

I have the following problem:

I'm upgrading a working system, so I have to create code that fits in. I want to create a csv file on a php page and a mysql database. I'm also using Ajax to stay on the same page while running other php-Files. Here are the Code snippets:

PHP/HTML-Page with Button

<div class="btn" id="export">Export</div>

Javascript Ajax

$("#export").click(function() {exportInfos();});

function exportInfos() {
    $.ajax({
        type: "POST",
        url: "functions/exportInfos.php",
        data: { searchterm: $("#search").val(), filterbycat: $("#filterbycat").val(), filterbytype: $("#filterbytype").val()},
        success: function(response){
            console.log("success_export");
        },
    });
}

PHP-File to create csv(exportInfos.php):

<?php
include ('../../config.php');

$searchterm = $_POST["searchterm"];
$filterbycat = $_POST["filterbycat"];
$filterbytype = $_POST["filterbytype"];

    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename=export.csv');
    $output = fopen("php://output", "w");
        $query = "SELECT * FROM database";
        $result = mysqli_query($mysqli, $query);
        while($row = mysqli_fetch_assoc($result))
        {
                 fputcsv($output, $row);
        }
        fclose($output);
?>

I'm not sure where the Problem is but in the console I only see that the php script is called and the success_export text from the log, but no file is opened or downloadable. I think the problem could be with the AJAX part because thats the part Im not sure about the most.

The data values in the Ajax part are there to edit the query as soon as i get some output File. $mysqli is the connection defined in the config file.

See also [this](https://stackoverflow.com/questions/48015884/generate-and-download-csv-file-with-php-and-ajax) and [this](https://stackoverflow.com/questions/46638343/download-csv-file-as-response-on-ajax-request) — Patrick Q, Oct 17 '19 at 13:25
`success: function(response)` your file is in `response`. Getting javascript to present a save-dialog is a different issue and you'll find many questions asking how to do this. The browser does it automatically, but you've taken control away from the browser. See the duplicates for more info. — freedomn-m, Oct 17 '19 at 14:36

score 1 · Answer 1 · answered Oct 18 '19 at 11:31

I think you need the change download methodology. Maybe it can be as follows.

Static Page :

<!doctype html>
<html lang="en">
<body>
<div class="btn" id="export">Export</div>
</body>
<script src="https://code.jquery.com/jquery-3.4.1.min.js" type="text/javascript"></script>
<script type="text/javascript">
  $('#export').click(function() {exportInfos();});

  function exportInfos() {
    $.ajax({
      type: 'POST',
      url: 'functions/createCSV.php',
      data: {
        searchterm: $('#search').val(),
        filterbycat: $('#filterbycat').val(),
        filterbytype: $('#filterbytype').val(),
      },
      success: function(response) {
        if (response.filename != undefined && response.filename.length > 0) {
          window.open('functions/downloadCSV.php?filename='+response.filename);
        }
      },
    });
  }
</script>
</html>

createCSV :

<?php

include('../../config.php');

$searchterm = $_POST["searchterm"];
$filterbycat = $_POST["filterbycat"];
$filterbytype = $_POST["filterbytype"];

//header('Content-Type: text/csv');
//header('Content-Disposition: attachment; filename=export.csv');

$outputPath = '/path/to/save/outputFile.csv';

$output = fopen($outputPath, "w");
$query = "SELECT * FROM database";
$result = mysqli_query($mysqli, $query);
while ($row = mysqli_fetch_assoc($result)) {
    fputcsv($output, $row);
}
fclose($output);

header('Content-Type: application/json');
echo json_encode(
    [
        "filename" => basename($outputPath),
    ]
);

downloadCSV :

<?php

if (!empty($_GET['filename'])) {
    http_send_status(404);
}

$filepath = "/path/to/save/" . $_GET['filename'];

if (!is_file($filepath) || !is_readable($filepath)) {
    http_send_status(404);
}

header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=" . $_GET['filename']);

echo file_get_contents($filepath);

Create CSV File by php, ajax and mysql

1 Answers1