PHP -转置数据

Question

我很难理解如何实现以下结果：

​

​

基本上我能做的，目前，是输出一个CSV文件与转置的数据，如上图左侧部分所示。我的问题是尝试获取列名，这些列名也已被转置，并且与结果查询数据在同一行上(我想要实现的是在上图右侧演示的)。

我的代码如下：

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

$headers = array();    
$rows = array();
$row_names = array();

while($result = $stmt->fetch(PDO::FETCH_ASSOC)) {

    if(empty($headers)) {
        $headers = array_keys($result);

        // Transpose column names
        foreach($headers as $key=>$val)
        {
            $row_names[$key][] = $val;
        }
    }

    // Transpose column data
    foreach($result as $key=>$val)
    {
        $rows[$key][] = $val;
    }
}

// Prints the row names
foreach($row_names as $name)
{
   fputcsv ($output, $name); 
}

// Prints the row data
foreach($rows as $row)
{
   fputcsv ($output, $row); 
}


fclose($output);

exit;

我知道我可能不得不以某种方式将$rows与$row_names结合起来，然后使用fputcsv执行一次foreach，但我不确定如何实现这一点……

以下是$result的输出：

Array
(
[Tail #] => N6232D
[Aircraft Configuration] => IR
[Flight Date] => 2015-11-05
[Pilot (Last Name)] => Mars
[Airport] => GA03
[City] => Ringgold
[State/Province] => GA
[Pre-Hobbs Time] => 41242
[Post-Hobbs Time] => 23423
[Hobbs Time] => -17819
[Pre-Tach Time] => 
[Post-Tach Time] => 
[Tach Time] => 
[Window Start Time] => 00:00:00
[Window Close Time] => 00:00:00
[Total Image Window] => 00:00:00
[On Station (Revenue)] => 00:00:00
[Weather] => 00:00:00
[ATC] => 00:00:00
[Pictometry Equip Failure/Delay] => 00:00:00
[Unscheduled Maintenance Time] => 00:00:00
[Scheduled Maintenance Time] => 00:00:00
[Pilot Delay] => 00:00:00
[Ground Support Delay] => 00:00:00
[Ferry] => 01:00:00
[Fueling/Lunch] => 00:00:00
[Plane - No Pilot] => 00:00:00
[Nothing Assigned] => 00:00:00
[Other/Miscellaneous] => 02:15:00
[Pilot Reported Times] => 03:15:00
[Completed Flight Plans (Include Project Name)] => 
[Partial Flight Plans] => 
[Date of Last Control Field] => 2015-10-05
[Drives Shipped Tracking Number] => 
[Spare Drive Sets] => -1
[Remarks] => 
[Status] => APPROVED
)

以下是$row_names输出的一个片段：

Array
(
[0] => Array
    (
        [0] => Tail #
    )

[1] => Array
    (
        [0] => Aircraft Configuration
    )

[2] => Array
    (
        [0] => Flight Date
    )

[3] => Array
    (
        [0] => Pilot (Last Name)
    )

[4] => Array
    (
        [0] => Airport
    )

[5] => Array
    (
        [0] => City
    )

[6] => Array
    (
        [0] => State/Province
    )

[7] => Array
    (
        [0] => Pre-Hobbs Time
    )

[8] => Array
    (
        [0] => Post-Hobbs Time
    )

[9] => Array
    (
        [0] => Hobbs Time
    )

[10] => Array
    (
        [0] => Pre-Tach Time
    )

[11] => Array
    (
        [0] => Post-Tach Time
    )

[12] => Array
    (
        [0] => Tach Time
    )

[13] => Array
    (
        [0] => Window Start Time
    )

[14] => Array
    (
        [0] => Window Close Time
    )

[15] => Array
    (
        [0] => Total Image Window
    )

[16] => Array
    (
        [0] => On Station (Revenue)
    )

[17] => Array
    (
        [0] => Weather
    )

[18] => Array
    (
        [0] => ATC
    )

[19] => Array
    (
        [0] => Pictometry Equip Failure/Delay
    )

[20] => Array
    (
        [0] => Unscheduled Maintenance Time
    )

[21] => Array
    (
        [0] => Scheduled Maintenance Time
    )

[22] => Array
    (
        [0] => Pilot Delay
    )

[23] => Array
    (
        [0] => Ground Support Delay
    )

[24] => Array
    (
        [0] => Ferry
    )

[25] => Array
    (
        [0] => Fueling/Lunch
    )

[26] => Array
    (
        [0] => Plane - No Pilot
    )

[27] => Array
    (
        [0] => Nothing Assigned
    )

[28] => Array
    (
        [0] => Other/Miscellaneous
    )

[29] => Array
    (
        [0] => Pilot Reported Times
    )

[30] => Array
    (
        [0] => Completed Flight Plans (Include Project Name)
    )

[31] => Array
    (
        [0] => Partial Flight Plans
    )

[32] => Array
    (
        [0] => Date of Last Control Field
    )

[33] => Array
    (
        [0] => Drives Shipped Tracking Number
    )

[34] => Array
    (
        [0] => Spare Drive Sets
    )

[35] => Array
    (
        [0] => Remarks
    )

[36] => Array
    (
        [0] => Status
    )

以下是$rows输出的一个片段：

Array
(
[Tail #] => Array
    (
        [0] => N6232D
        [1] => N6232D
        [2] => N6232D
        [3] => N6232D
        [4] => N6232D
    )

[Aircraft Configuration] => Array
    (
        [0] => IR
        [1] => IR
        [2] => IR
        [3] => IR
        [4] => IR
    )

[Flight Date] => Array
    (
        [0] => 2015-11-01
        [1] => 2015-11-05
        [2] => 2015-11-06
        [3] => 2015-11-08
        [4] => 2015-11-09
    )

[Pilot (Last Name)] => Array
    (
        [0] => Mars
        [1] => Mars
        [2] => Mars
        [3] => Mars
        [4] => Mars
    )

[Airport] => Array
    (
        [0] => KROC
        [1] => GA03
        [2] => C37
        [3] => GA03
        [4] => PHNL
    )

[City] => Array
    (
        [0] => Rochester
        [1] => Ringgold
        [2] => Brodhead
        [3] => Ringgold
        [4] => Honolulu
    )

[State/Province] => Array
    (
        [0] => NY
        [1] => GA
        [2] => WI
        [3] => GA
        [4] => HI
    )

[Pre-Hobbs Time] => Array
    (
        [0] => 12500
        [1] => 41242
        [2] => 15666.8
        [3] => 15690
        [4] => 15000
    )

[Post-Hobbs Time] => Array
    (
        [0] => 12501.9
        [1] => 23423
        [2] => 15669.8
        [3] => 15699.5
        [4] => 15000.9
    )

[Hobbs Time] => Array
    (
        [0] => 1.89999999999964
        [1] => -17819
        [2] => 3
        [3] => 9.5
        [4] => 0.899999999999636
    )

[Pre-Tach Time] => Array
    (
        [0] => 
        [1] => 
        [2] => 
        [3] => 
        [4] => 
    )

[Post-Tach Time] => Array
    (
        [0] => 
        [1] => 
        [2] => 
        [3] => 
        [4] => 
    )

[Tach Time] => Array
    (
        [0] => 
        [1] => 
        [2] => 
        [3] => 
        [4] => 
    )

[Window Start Time] => Array
    (
        [0] => 12:35:00
        [1] => 00:00:00
        [2] => 15:30:00
        [3] => 15:30:00
        [4] => 12:30:00
    )

[Window Close Time] => Array
    (
        [0] => 15:40:00
        [1] => 00:00:00
        [2] => 17:15:00
        [3] => 17:15:00
        [4] => 15:45:00
    )

[Total Image Window] => Array
    (
        [0] => 03:05:00
        [1] => 00:00:00
        [2] => 01:45:00
        [3] => 01:45:00
        [4] => 03:15:00
    )

[On Station (Revenue)] => Array
    (
        [0] => 
        [1] => 00:00:00
        [2] => 00:50:00
        [3] => 00:00:00
        [4] => 00:00:00
    )

Answer 1

假设两个数组大小相同，那么使用一个带有索引计数器的简单for循环会怎样呢？并使用array_merge将标题名称和值合并到一行中。

// Prints the row names and the values in one row.
$count_rows = count($rows);
for($i=0; $i<$count_rows; $i++)
{
   fputcsv ($output, array_merge($row_names[$i], $rows[$i])); 
}

**编辑**

根据新信息，您可以更改代码的开头，如下所示

while($result = $stmt->fetch(PDO::FETCH_ASSOC)) {

    if(empty($headers)) {
        $headers = array_keys($result);

        // Transpose column names
        foreach($headers as $key=>$val)
        {
            $rows[$key][] = $val;
        }
    }

    // Transpose column data
    foreach($result as $key=>$val)
    {
        $rows[$key][] = $val;
    }
}

请注意，标头名称和值现在都存储在同一数组中，但因为标头总是在前面，所以它总是成为每行的第一个值，这正是您想要的。

现在，我们可以在行上只使用一个foreach。

// Prints the row data
foreach($rows as $row)
{
   fputcsv ($output, $row); 
}

Answer 2

似乎我每次转置时都需要对$rows数组进行偏移：

while($result = $stmt->fetch(PDO::FETCH_ASSOC)) {

    if(empty($headers)) {
        $headers = array_keys($result);

        // Transpose column names
        foreach($headers as $key=>$val)
        {
            $rows[$key][] = $val;
        }

    }

    // Offset for data (by one column)
    $i = 0;

    // Transpose column data
    foreach($result as $key=>$val)
    {
        $rows[$key+$i][] = $val;
        $i++;
    }
}

// Prints the row data
foreach($rows as $row)
{
    fputcsv($output, $row);
}