php - 如何通过HTML表显示多个数据库值的数值

我有一个页面，我想在其中显示数据库中值的“多少”。我已附上我到目前为止的照片。
从目前的数量来看，我知道我有13个运输项目，2个通信项目等等，所以我想知道如何正确输出。

<?php

    $countSQL = "SELECT username, primary_function, COUNT(*)
                FROM resource
                WHERE primary_function
                IN ('transportation', 'communications', 'engineering', 'search and rescue', 'education', 'energy', 'firefighting', 'human services')
                GROUP BY username, primary_function";
    $countresult = mysqli_query($conn, $countSQL);
    $countdata= mysqli_num_rows($countresult);

        $sql = "SELECT pf_id, primary_function FROM primary_function ORDER BY pf_id;";
        $result = mysqli_query($conn, $sql);
        $queryResult = mysqli_num_rows($result);

        if ($queryResult > 0){
            while ($row = mysqli_fetch_assoc($result)) {
              echo "<tr><td>". $row['pf_id'] ."</td><td>". $row['primary_function'] ."</td><td>". $countdata."</td></td>";
            }
          }
    ?>

最佳答案

为您的COUNT(*)使用别名，即resourceCount

SELECT username, primary_function, COUNT(*) as resourceCount

然后用primary_function和resourceCount创建一个数组

$resourceCounts = array();
while ($rowCounts = mysqli_fetch_assoc($countresult)) {
    $resourceCounts['primary_function'] = $rowCounts['resourceCount'];
}

然后在表中使用$row['primary_function']获取resourceCount-> $resourceCounts[$row['primary_function']]

echo "<tr><td>". $row['pf_id'] ."</td><td>". $row['primary_function'] ."</td><td>". $resourceCounts[$row['primary_function']] ."</td></td>";

要获得最终的总计，请创建一个计数器var并将该值添加到其中-

$total = 0;
....
// add the value in the loop
$total += $resourceCounts[$row['primary_function']];
....