Export data from an object to a csv file with PHP

Question

I am trying to export data from the database to a CSV file, but all functions I find to perform such a function present certain type of error.

I’m performing the entire search in the database with PDO and returning the data to my Control, but this is where I park, I can’t export the data.

I wonder if someone could help me?

Answer 1

PHP already has a function ready for this:

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

An example, taking advantage of the code of colleague João:

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

$fccd = fopen( 'file.csv', 'w' );
foreach ( $results as $fields ) {
    fputcsv( $fccd, $fields );
}
fclose( $fccd );

If you prefer to choose delimiter characters:

    fputcsv( $fccd, $fields, ';', '"', '\' );

So we’ll have separation by ;, fields with double quotes " and escaped by \;

Answer 2

To create a file in the format .csv with PHP, on data coming from a array associative, following example below:

<?php   

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

    //COLUNAS
    $columns = $pdo->prepare("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='dbactive' AND TABLE_NAME='tbproduct'");
    $columns->execute();
    $results_columns = $columns->fetchAll(PDO::FETCH_COLUMN,0);

    //DADOS
    $stmt = $pdo->prepare('SELECT * FROM tbproduct');   
    $stmt->execute();
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    //CRIAÇÃO DO ARQUIVO CSV
    $from = fopen("file.csv", 'wb');

    //ADICIONANDO O CABEÇALHO
    fwrite($from, '"'.implode('";"', $results_columns).'"'.PHP_EOL);

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

In the packagist has several packages that can be integrated easily to your project, follows below 3 excellent:

• mnshankar/csv

• Goodby/csv

• keboola/csv

EDITION:

The first routine generates the disk file and the disk takes this file generated on the disk and sends it to download.

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;