首页 文章

将列名称导出为CSV

提问于
浏览
1

我希望能够将我的表列的名称与我的查询结果一起导出到.csv文件中 . 我找到了一些使用mysql_的例子,但我希望有人可以帮助我,因为我正在尝试使用PDO . 来自$ sql导出的数据就好了,但是我无法将列名作为第一行导出 .

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

2 回答

  • 2

    您不必查询information_schema .
    只需将行作为关联数组获取并在输出数据之前输出键:

    $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);
        }
    }
    
  • 4

    看起来问题实际上应该是“如何使用PDO获取表格的列名数组?”在另一个SO问题中找到 .

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

    然后将数组转换为CSV .

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

    这是另一种不是“实验性”的方式......

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

    php pdo: get the columns name of a table

相关问题