Generate csv file in php [closed]

Question

Generate csv file in php [closed]

Navigation

#1 by (7 votes)
#2 by (1 votes)

1

Hello everyone, I'm trying to create a query in php in the mysql database to generate a csv file according to the code below. The problem is that the one that would download the file is presenting an error, I have reviewed it a few times and I did not find the error that I made. The file appears in the FTP folder where the index is.

Thank you for 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;
   ?>

php mysql pdo csv

asked by anonymous 04.06.2016 / 22:12

2 answers

1

Sirs,

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

In MySQL you solve this problem with a command, of course, as long as 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 have contributed.

05.06.2016 / 03:59

How to dynamically change text color based on background color? PHP refresh on a page

score 7 · Accepted Answer

First, let's eliminate this gambiarra here from the code:

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

PHP already has the right function for this, which is fputcsv , and other than that, you have an error in the way your loop was mounted.

In addition, if it is to generate and send to the client, it does not need create a file to disk, just send it to standard output:

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

That is, this is enough:

<?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, you do not even need to load everything into memory. Just instead of fetchAll , you will use fetch and fputcsv line the line in the loop, but there you have a little of the question.

Just to state, if at any time you really need to send a file that is on disk to the user, you do not need this fread of your code. PHP has the function readfile() to do this 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' );

link