CODEWITHSUNDEEP
phppostgresqlcsvpdo
aragornii
aragornii

Reputation: 371

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?

Upvotes: 1

Views: 2527

Answers (2)

Dharman
Dharman

Reputation: 33403

Your first row is missing because you used it up to get the headers here:

$headers = $stmt->fetch(PDO::FETCH_ASSOC);

The problem can be avoided if instead of confusing while loop, you'd just have used a simple foreach.

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

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

foreach ($stmt as $row) {
    fputcsv($data, $row);
}

Upvotes: 0

Nigel Ren
Nigel Ren

Reputation: 57141

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);
    }

Upvotes: 3

Related Questions