php - 如何计算具有品种值的列

Question

我有一个包含 3 列的表:EmployeeID、WorkPlace、WorkStatus

EmployeeID | WorkPlace | WorkStatus
     1     |    KL     |    Baru
     2     |    KK     |   Batal
     3     |    PP     |    Ubah
     4     |    KL     |   Batal

我想根据该表中的数据制作报告，并以表格形式显示在 PHP 页面中，如下所示:

 No. | WorkPlace |        WorkStatus          | Total |
     |           |  Baru |   Batal  |   Ubah  |       |
-------------------------------------------------------
  1  |    KL     |   1   |     1    |    0    |   2   |
  2  |    KK     |   0   |     1    |    0    |   1   |
  3  |    PP     |   0   |     0    |    1    |   1   |
-------------------------------------------------------  
     Total       |   1   |     2    |    1    |   4   |

这是我的 SQL 查询:

mysql_select_db($database_conn, $conn);
$query = "SELECT * FROM permohonan GROUP BY WorkPlace"; 
$Recordset1 = mysql_query($query, $conn) or die(mysql_error());

这是我显示表格的代码:

<table border="1" cellpadding="5" style="border-collapse:collapse">
<tr>
    <th rowspan="2">No.</th>
    <th rowspan="2">WorkPlacen</th>
    <th colspan="3">WorkStatus</th>
    <th rowspan="2">Total</th>
</tr>
<tr>
    <th>baru</th>
    <th>batal</th>
    <th>ubah</th>

</tr>

<?php 
$bil==0;  $sBaru=0; $sBatal=0; $sUbah=0;
while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) { 
$bil++; $cBaru=0; $cBatal=0; $cUbah=0;
        if ($row_Recordset1['WorkStatus'] == 'Baru'){
            $cBaru++;
            $sBaru++; }
        else if ($row_Recordset1['WorkStatus'] == 'Batal'){
            $cBatal++; $sBatal++;}
        else if($row_Recordset1['WorkStatus'] == 'Ubah'){
            $cUbah++; $sUbah++;}?>
<tr>
  <td><?php echo $bil."." ?></td>
  <td><?php echo $row_Recordset1['bhg_cwgn']; ?></td>
  <td><?php echo $cBaru ?></td>
  <td><?php echo $cBatal ?></td>
  <td><?php echo $cUbah ?></td>
  <td><?php echo $cBaru+$cBatal+$cUbah ?></td>
</tr>
<?php } ?>
<tr>
    <td colspan=2>Jumlah</td>
    <td><?php echo $sBaru ?></td>
    <td><?php echo $sBatal ?></td>
    <td><?php echo $sUbah ?></td>
    <td><?php echo $sBaru+$sBatal+$sUbah ?></td>

</tr>
</table>

当我使用该代码时，WorkStatus 的行 WorkPlace = KL 时的显示为 1,0,0 而不是 1,1,0

我该如何解决这个问题？

Answer 1

在您的查询中，我认为您想要这样的东西

SELECT WorkPlace, COUNT(WorkPlace) FROM permohonan GROUP BY WorkPlace