Exporting MySQL Table Data to a .CSV File via PHP

[Jadranka]

When I’m exporting data from the MySQL table to a .csv file via MySQL console, everything works well and the data gets exported. But when I try to do the same on click of a button in the web application via PHP it doesn’t work.

Here’s the query that works via MySQL console:

SELECT * FROM sample_table INTO OUTFILE 'c:/tmp/sample-log.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n'

But when I try to do the same within PHP, it doesn’t work. Here’s the PHP:

<?php

$pdo = new PDO('mysql:host=localhost;dbname=sample_db', 'root', '');

$sql = "SELECT * FROM sample_table INTO OUTFILE 'C:\\tmp\\log-:dateExport\.csv' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"' LINES TERMINATED BY '\\r\\n'";

$stmt = $pdo->prepare($sql);

$stmt->bindParam(':dateExport', $_POST['dateExport'], PDO::PARAM_STR);

$stmt->execute();

?>

As you can see I’m calling this PHP script from within another file via AJAX and passing it a variable dateExport in order for the exported file to be named log-DATE.csv. Here’s the AJAX call if it helps:

var dateExport = moment().format("DD-MM-YYYY");

$.ajax({
	type: "POST",
	url: "export.php",
	data: {dateExport: dateExport},
});

I suppose I’m wrong somewhere with the query string or maybe escape characters? I tried countless combinations. Any help would be greatly appreciated. Thank you.

[oddz]

The file path can’t be dynamic. You need to embed the file path directly in the SQL without using it as bind param. Which means you are going to need to do some manual validation to assure that incoming value isn’t anything dangerous.

$sql = "SELECT * FROM sample_table INTO OUTFILE 'C:\\tmp\\log-".$_POST['dateExport']."\.csv' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"' LINES TERMINATED BY '\\r\\n'";

You will need to add validation of course but you probably get the gist.

[Jadranka]

I’m aware of the validation, this is radically simplified version of what I’m trying to do and I’m doing it for learning purposes. Thank you, it works now.

[Jadranka]

Do you maybe know how would I go about appending certain rows to a file later in the future? For example, I’ve exported my data, but would like to add a row or two at the bottom later. How would I do that? Thanks.

[oddz]

Why not just rerun the export.