Export Column Names to CSV

Question

I'd would like to be able to export the names of my table columns into a .csv file along with my query results. I have found a few examples using mysql_, but I was hoping someone could help me out as I am trying to use PDOs. The data from $sql exports just fine, but I can't get the column names to export as the first row.

<?php 
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=brewlog.csv");
header("Pragma: no-cache");
header("Expires: 0");

//Database Connection 
include"connect.php";

//Assign variables from POST
    $beer = $_POST['beer'];

//SQL Query for Data
    $sql = "SELECT * FROM brewlog WHERE Beer = :beer ORDER BY BrewDate DESC";

//Prepare Query, Bind Parameters, Excute Query
    $STH = $DBH->prepare($sql);
    $STH->bindParam(':beer', $beer);
    $STH->execute();

//Get Column Names for Header   
    $STMT = $DBH->query('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = \'database\' AND TABLE_NAME = \'my_table\'');
    $header = $STMT->fetchAll(PDO::FETCH_NUM);

//Export to .CSV
    $fp = fopen('php://output', 'w');
    fputcsv($fp,$header);
    while ($row = $STH->fetch(PDO::FETCH_NUM)) fputcsv($fp,$row);
    fclose($fp);
?>

This is independent from the database connector you use: you should fetch associative rows instead of numerical arrays. Then you have the (result set) column names as array keys. — arkascha, Mar 03 '14 at 23:20

score 5 · Accepted Answer · answered Mar 03 '14 at 23:20

You don't have to query the information_schema.
Just fetch the row as an associative array and output the keys before you output the data:

$row = $STH->fetch(PDO::FETCH_ASSOC);
if ($row) {
    fputcsv($fp,array_keys($row));
    while ($row) {
        fputcsv($fp,array_values($row));
        $row = $STH->fetch(PDO::FETCH_ASSOC);
    }
}

score 2 · Answer 2 · edited May 23 '17 at 11:49

2

It looks like question should actually be "How do I get an array of my table's column names using PDO?" As found in another SO question.

$result = $db->query('SELECT * FROM my_table LIMIT 0');
for ($i = 0; $i < $result->columnCount(); $i++) {
    $col = $result->getColumnMeta($i);
    $columns[] = $col['name'];
}

Then do the array to CSV.

$fp = fopen('php://output', 'w');
fputcsv($fp, $columns);

And here's another way that is not "experimental"...

$q = $db->prepare("DESCRIBE tablename");
$q->execute();
$columns = $q->fetchAll(PDO::FETCH_COLUMN);

php pdo: get the columns name of a table

edited May 23 '17 at 11:49

Community

1
1

answered Mar 03 '14 at 23:30

SacWebDeveloper

2,563
2
18
15

+1 I like this answer, but FWIW getColumnMeta() is still listed as an experimental PDO method. – Bill Karwin Mar 03 '14 at 23:32

Export Column Names to CSV

2 Answers2