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:
while this is what I can see:
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?
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);
}