phppostgresqlcsvpdo

Export .csv file (with headers) from PostgreSQL table using PHP PDO, first data row missing


I'm trying to export a CSV file form a PostgreSQL table using PHP PDO. Everything is working fine a part from the missing first data row.

This is my code:

<?php

function bb()
{
$servername = "localhost";
$username = "postgres";
$password = "mypassword";
$dbname = "mydb";

$conn = new PDO("pgsql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT * FROM foundation"); 
$stmt->execute();

$filename = 'test_postgres.csv';

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename=' . $filename);
header("Content-Transfer-Encoding: UTF-8");

$head = fopen($filename, 'w');

$headers = $stmt->fetch(PDO::FETCH_ASSOC);
fputcsv($head, array_keys($headers));

fclose($head);

$data = fopen($filename, 'a');

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        fputcsv($data, $row);
    }

fclose($data);
}

bb();

?>

This is what I should have:

img1

while this is what I can see:

enter image description here

I think I'm writing the headers in place of the first data row but I cannot find a way to avoid this issue. Any idea?


Solution

  • The problem is that when you read the row for the header, that is the first row of the data, so you need to write the data from here to the data file as well...

    $headers = $stmt->fetch(PDO::FETCH_ASSOC);
    fputcsv($head, array_keys($headers));
    
    fclose($head);
    
    $data = fopen($filename, 'a');
    fputcsv($data, $headers);  // This adds the data from the header row
    
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            fputcsv($data, $row);
        }