fputcsv with custom headers

csv, mysql, pdo, php

Solution

Here's what I came up with, it appears to work just as the original one. If you have any concerns please let me know, it works as intended and I got rid of the mysql_ functions.

<?php

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=collections.csv');

try {
$conn = removed
  }
catch(PDOException $e)
{
echo $e->getMessage();
}

$sql = "SELECT 
coll.title AS CollectionTitle, 
coll.inclusivedates AS InclusiveDates,
coll.ID AS CollectionID, 
collloc.LocationID,
collloc.Content,
collloc.RangeValue,
collloc.Section,
collloc.Shelf,
collloc.Extent,
collloc.ExtentUnitID
FROM tblCollections_Collections coll 
JOIN tblCollections_CollectionLocationIndex collloc 
ON collloc.CollectionID = coll.id 
ORDER BY coll.ID";

$results = $conn->query($sql);

// Pick a filename and destination directory for the file
// Remember that the folder where you want to write the file has to be writable
$filename = "collections.csv";

// Actually create the file
// The w+ parameter will wipe out and overwrite any existing file with the same name
$handle = fopen('php://output', 'w');

// Write the spreadsheet column titles / labels
fputcsv($handle,   array('Title','InclusiveDates','CollectionID','LocationID','Content','RangeValue','Section','Shelf','Extent','ExtentUnitID'));

// Write all the user records to the spreadsheet
foreach($results as $row)
{
fputcsv($handle, array($row['CollectionTitle'], $row['InclusiveDates'], $row['CollectionID'], $row['LocationID'], $row['Content'], $row['RangeValue'], $row['Section'], $row['Shelf'], $row['Extent'], $row['ExtentUnitID']));
}

// Finish writing the file
fclose($handle);

?>

Problem

Trying to convert the following working code to PDO, the problem I am running into are the custom headings set in the `fputcsv($output,array)`. I really want to move away from all msql_ functions so I would greatly appreciate some help. ``` <?php // output headers so that the file is downloaded rather than displayed header('Content-Type: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename=collections.csv'); // create a file pointer connected to the output stream $output = fopen('php://output', 'w'); //MySQL Connect removed // output the column headings fputcsv($output, array('Title','InclusiveDates','CollectionID','LocationID','Content','RangeValue','Section','Shelf','Extent','ExtentUnitID')); // fetch the data $rows = mysql_query('SELECT coll.title AS CollectionTitle, coll.inclusivedates AS InclusiveDates, coll.ID AS CollectionID, collloc.LocationID, collloc.Content, collloc.RangeValue, collloc.Section, collloc.Shelf, collloc.Extent, collloc.ExtentUnitID FROM tblCollections_Collections coll JOIN tblCollections_CollectionLocationIndex collloc ON collloc.CollectionID = coll.id ORDER BY coll.ID'); // loop over the rows, outputting them while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row); ?> ``` I have gotten this far, without getting it to work. The problem here is that I am not able to set custom column names in the CSV file and it also does not export to a CSV. ``` <?php $conn = removed $query = "SELECT coll.title AS CollectionTitle, coll.inclusivedates AS InclusiveDates, coll.ID AS CollectionID, collloc.LocationID, collloc.Content, collloc.RangeValue, collloc.Section, collloc.Shelf, collloc.Extent, collloc.ExtentUnitID FROM tblCollections_Collections coll JOIN tblCollections_CollectionLocationIndex collloc ON collloc.CollectionID = coll.id ORDER BY coll.ID"; $stmt = $conn->prepare($query); // Execute the statement $stmt->execute(); var_dump($stmt->fetch(PDO::FETCH_ASSOC)); $data = fopen('file.csv', 'w'); $header = array(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { if(empty($header)){ // do it only once! $header = array_keys($row); // get the columnnames fputcsv($data, $header); // put them in csv } echo "Success"; // Export every row to a file fputcsv($data, $row); } ?> ```

Original source