Generate csv file in php

Question

Hello guys I am trying to create a php query in mysql database to generate a csv file according to the code below. The problem is that the part that would download the file is showing error, I already reviewed a few times and did not find the error I made. The file appears in the FTP folder where the index is.

I appreciate any help thank you very much.

<?php
//PDO
$pdo = new PDO('mysql:host=localhost;dbname=banco', 'root', '123456');
$stmt = $pdo->prepare('SELECT * FROM cadastro');   
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

//Criação do Arquivo csv sobre os dados obtidos de um SQL
$from = fopen("file.csv", 'wb');

foreach ($results as $result) 
{
    $results[$idx] = str_replace("\"", "\"\"", $result);        
    fwrite($from, '"'.implode('";"', $results[$idx]).'"'.PHP_EOL);
    }
   fclose($from);


   header("Content-type: application/csv");   
   header("Content-Disposition: attachment; filename=file.csv");   
   header("Content-Transfer-Encoding: binary");
   header("Pragma: no-cache");
   $path = "file.csv";
   $from = fopen($path, 'r');
   $csv = fread($from, filesize($path));   
   fclose($from);
   echo $csv;
   ?>

Answer 1

First, let’s eliminate this scam here from the code:

$results[$idx] = str_replace("\"", "\"\"", $result);        

PHP already has the right function for this, which is fputcsv, and other than that, there’s a mistake in the way that your loop.

Besides, if it’s to generate and send to the client, you don’t need create a disk file, just send it to standard output:

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

I mean, that’s all it takes:

<?php
   header( 'Content-type: application/csv' );   
   header( 'Content-Disposition: attachment; filename=file.csv' );   
   header( 'Content-Transfer-Encoding: binary' );
   header( 'Pragma: no-cache');

   $pdo = new PDO( 'mysql:host=localhost;dbname=banco', 'root', '123456' );
   $stmt = $pdo->prepare( 'SELECT * FROM cadastro' );   
   $stmt->execute();
   $results = $stmt->fetchAll( PDO::FETCH_ASSOC );

   $out = fopen( 'php://output', 'w' );
   foreach ( $results as $result ) 
   {
      fputcsv( $out, $result );
   }
   fclose( $out );
?>

If you really want to optimize, don’t even need to load everything in memory. Just instead of fetchAll, use fetch and fputcsv line by line in the loop, but then it slips a bit of the question.

Just for the record, if at any time you really need to send a file that’s on disk to the user, you don’t need this fread of your code. PHP has the function readfile() to do it all at once:

header("Content-type: application/csv");   
header("Content-Disposition: attachment; filename=file.csv");   
header("Content-Transfer-Encoding: binary");
header("Pragma: no-cache");
readfile( 'file.csv' );

http://php.net/manual/en/function.readfile.php

Answer 2

Sirs,

I know the question is about generating CSV from PHP, but, I’ve used native solutions in Dbms and liked the result (mainly with a lot of data) there is no access to data by PHP. The script that will trigger this command may even have a cache policy to not run it "every time", of course, depending on what is intended.

In Mysql you solve this problem with a command, of course, since feasible in your solution

SELECT id, description, date
FROM operations
WHERE type = 1
INTO OUTFILE '/tmp/today_ops.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

I hope I contributed.