php代码以透视表格式显示数据 - php code to display data in pivot table format

php code to display data in pivot table format

我的数据库有如下数据。。

surveyorname       bookingdate       receiptno
  raj              20-03-2015         56   
  raj              20-03-2015         701 
  angel            21-03-2015         55    
  raj              22-03-2015         700

现在我的代码显示如下格式的数据

surveyorname         20-03-2015
   raj                  2
  21-03-2015            --
   angel                1
  22-03-2015            --
   raj                  1

我需要显示如下的报告格式

 surveyorname    20-03-2015        21-03-2015        22-03-2015  
    raj             2                                    1
    angel                            1

我的查询正确地获取了数据，但唯一的问题是我不明白如何以上述格式显示。。。

   <?php
$book = $database->getRows("SELECT surveyor_name as srv, bookingdate as bd, COUNT(DISTINCT receipt_no) as num  FROM receipt_entry  GROUP BY surveyor_name, bookingdate");
$days=array();
$line=array();
foreach ($book as $b) {
   $days[$d['bd']]=1;
   if (!is_array($line[$d['srv']])) {
      $line[$d['srv']]=array();
   }
   $line[$d['srv']][$d['bd']]=$d['num'];
}
print "<table><tr><th></th>'n";
foreach ($days as $d=>$n) {
      print "<th>$d</th>'n";
}
print "</tr>'n";
foreach ($line as $srv=>$l) {
    print "<tr><td>$srv</td>";
    foreach ($days as $d=>$n) {
        print "<td>" . $l[$d] . "</td>'n";
    }
    print "</tr>'n";
} 
print "</table>'n";
?>

可以试试这个

SELECT count(*) as TotalNum, surveyorname, bookingdate FROM your_table group by bookingdate,surveyorname;

$book = $database->getRows("SELECT surveyor_name as srv
   , booking_date as bd
   , COUNT(*) as num 
   FROM receipt_entry 
   GROUP BY surveyor_name, booking_date 
   ORDER BY booking_date");
$days=array();
$line=array();
foreach ($book as $b) {
   $days[$d['bd']]=1;
   if (!is_array($line[$d['srv']])) {
      $line[$d['srv']]=array();
   }
   $line[$d['srv']][$d['bd']]=$d['num'];
}
print "<table><tr><th></th>'n";
foreach ($days as $d=>$n) {
      print "<th>$d</th>'n";
}
print "</tr>'n";
foreach ($line as $srv=>$l) {
    print "<tr><td>$srv</td>";
    foreach ($days as $d=>$n) {
        print "<td>" . $l[$d] . "</td>'n";
    }
    print "</tr>'n";
} 
print "</table>'n";