php - 使用 PHP 从 MySQL DB 更改 CSV 格式

Question

我发现了类似的问题，但由于缺乏信息而没有解决方案。我有下面的代码，它将数据从连接的 MySQL DB 输出到以下格式的格式化 CSV 文件...

First Name:Surname:GCNumber:Dept:Start Date:Introduction:Theory:Fire Safety:Governance:John:Smith:123456:HR:03/08/2013:Pass:Pass:Fail:Pass:Jane:Watson:123445:IT:03/08/2013:Pass:Fail:Pass:Pass:Mark:Byron:123442:IT:03/08/2013:Fail:Fail:Not Done:Not Done:

: = used just to show each column

It just outputs all the rows and columns that are in the database in the same structure, and renaming the column headers to be more friendly when imported into excel.What I need is to change to format of this output to the following…

First Name:Surname:GCNumber:Dept:Email:Start Date:Module:Status:John:Smith:123456:HR:03/08/2013:Introduction:Pass:John:Smith:123456:HR:03/08/2013:Theory:Pass:John:Smith:123456:HR:03/08/2013:Fire Safety:Fail:John:Smith:123456:HR:03/08/2013:Governance:Pass:Jane:Watson:123445:IT:03/08/2013:Introduction:Pass:Jane:Watson:123445:IT:03/08/2013:Theory:Fail:Jane:Watson:123445:IT:03/08/2013:Fire Safety:Pass:Jane:Watson:123445:IT:03/08/2013:Governance:Pass:Mark:Byron:123442:IT:03/08/2013:Introduction:Fail:Mark:Byron:123442:IT:03/08/2013:Theory:Fail:Mark:Byron:123442:IT:03/08/2013:Fire Safety:Not Done:Mark:Byron:123442:IT:03/08/2013:Governance:Not Done:

: = used just to show each column

So Rather than one entry for each person, and results of each module they have done I need it to be a separate entry for each module that person has done.In total there are more fields to this DB and 35 modules but I've cut this down for illustration purposes here.

Being a bit of a newbie with PHP etc I'm struggling to get my head around how to do this.Is this possible or would it be easier to try and change the structure of the DB to be in the desired format?

Any help or pointers in the right direction would be great.Tony

<?php

function exportMysqlToCsv($table,$filename = 'db-snapshot.csv')
{

    $sql_query = "select fldFirstname as 'First Name',
     fldSurname as 'Surname',
     fldGMCNumber as 'GCNumber', 
     fldDestDept as 'Dept',
     fldStartDate as 'Start Date',
     fldModule1 as 'Introduction',
     fldModule2 as 'Theory',
     fldModule3 as 'Fire Safety',
     fldModule4 as 'Governance'
     from $table";

    // Gets the data from the database
    $result = mysql_query($sql_query);

    $f = fopen('php://temp', 'wt');
    $first = true;
    while ($row = mysql_fetch_assoc($result)) {
        if ($first) {
            fputcsv($f, array_keys($row));
            $first = false;
        }
        fputcsv($f, $row);
    } // end while

    $size = ftell($f);
    rewind($f);

    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: $size");

 // Output to browser with appropriate mime type, you choose ;)
    header("Content-type: text/x-csv");
    // header("Content-type: text/csv");
    // header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
    fpassthru($f);
    exit;
}

?>

Answer 1

您可以使用 UNIONS 来实现此目的:

SELECT fldFirstname as 'First Name', fldSurname as 'Surname', 
  fldGMCNumber as 'GCNumber', fldDestDept as 'Dept', 
  fldStartDate as 'Start Date', fldEndDate as 'End Date', 
  'Introduction' as 'Module', fldModule1 as 'Status' FROM records
UNION
SELECT fldFirstname,fldSurname,fldGMCNumber,fldDestDept,fldStartDate,fldEndDate,
  'Theory',fldModule2 FROM records
UNION
SELECT fldFirstname,fldSurname,fldGMCNumber,fldDestDept,fldStartDate,fldEndDate,
  'Fire Safety',fldModule3 FROM records
UNION
SELECT fldFirstname,fldSurname,fldGMCNumber,fldDestDept,fldStartDate,fldEndDate,
  'Governance',fldModule4 FROM records
ORDER BY Surname, `First Name`, Module;

SQL Fiddle example