我有这个PHP代码：

$query_production = "SELECT uploadedby as name, sum(points) as points,
date_format(uploaddate,'%Y-%m-%d') as date FROM imsexport 
WHERE uploaddate BETWEEN '2014-01-01 00:00:00' and '2014-01-20 23:59:59' 
GROUP BY uploadedby,date";
$result_production = mysql_query($query_production);
$row_production = mysql_fetch_assoc($result_production);

HTML表输出为

  name        points      date
  John         147  2014-01-01
  Bob          79   2014-01-01
  Joe          156  2014-01-01
  Sue          116  2014-01-01
  John         117  2014-01-02
  Bob          186  2014-01-02
  Sue          74   2014-01-02
  Bob          233  2014-01-03
  John         159  2014-01-03
  Sue          162  2014-01-03
  Bob          162  2014-01-04
  Sue          38   2014-01-05

如何使用php旋转此表使其看起来像这样？我已经使用mysql完成此操作,但是代码太长.

Name |2014-01-01|2014-01-02|2014-01-03|2014-01-04|2014-01-05
Bob      79          186       233         162      0
Joe      156          0         0           0       0
John     147         117       159          0       0
Sue      116          74       162          0       38

解决方法:

这将为您提供大部分帮助.我剩下的是如何正确校正左偏移量(或日期/点列对齐；提示,您需要将日期与名称/点对保持一致,并知道它的偏移量以$cols为单位).

显然,您正在处理数据库中的行,因此会有所不同.

请参阅代码下方的键盘演示链接.

<?php

$data = "John 147 2014-01-01
Bob 79 2014-01-01
Joe 156 2014-01-01
Sue 116 2014-01-01
John 117 2014-01-02
Bob 186 2014-01-02
Sue 74 2014-01-02
Bob 233 2014-01-03
John 159 2014-01-03
Sue 162 2014-01-03
Bob 162 2014-01-04
Sue 38 2014-01-05";

$data = explode("\n", $data);

$cols = array();
$pivot = array();

while ($line = array_shift($data)) {
    list($name, $points, $date) = explode(' ', trim($line));

    if (!$pivot[$name]) $pivot[$name] = array();

    array_push($cols, $date);

    array_push($pivot[$name], array('date' => $date, 'points' => $points));
}

$cols = array_unique($cols);

print_r($pivot);

echo implode('|', $cols);

echo PHP_EOL;

foreach ($pivot as $name => $row) {
    while ($entry = array_shift($row)) {
        echo str_pad($name, 7, ' ') . str_pad($entry['points'], 3, ' ', STR_PAD_LEFT) . '|';
    }

    echo PHP_EOL;
}

?>

http://codepad.org/WqqpKwn3