PHP foreach输出TD问题：贡献费用数据按年份正确显示的解决方法

数据库表结构与数据 #

我有一张名为CONTRIBUTION FEES的数据库表，结构及数据如下：

原PHP查询展示代码 #

我使用PHP结合mysqli编写了以下代码，用于查询数据并展示为HTML表格：

<?php  
echo '<table class="table table-bordered table-sm" id="table">
      <thead>';
          
include $_SERVER["DOCUMENT_ROOT"] . "/config.php"; 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$query = "SELECT GROUP_CONCAT(DISTINCT year) AS years FROM `CONTRIBUTION FEES` WHERE program = '$program'";
$result = $conn->query($query);
$row = $result->fetch_assoc();
$years = explode(',', $row['years']);
if($row['years'] != ''){
    echo ' <tr>
        <th>CATEGORY</th>';
    foreach ($years as $year){
        echo ' <th>YEAR '.$year.'</th>';
    }
    echo ' 
        <th>ACTIONS</th>
       </tr>';
}

echo '
      </thead>
      <tbody>';

$query = "SELECT GROUP_CONCAT(DISTINCT category) AS categories,GROUP_CONCAT(DISTINCT year) AS years,GROUP_CONCAT(amount) AS amounts FROM `CONTRIBUTION FEES` WHERE program = '$program' GROUP BY category";
$result = $conn->query($query);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()){
        $years = explode(',', $row['years']);
        $amounts = explode(',', $row['amounts']);
        $categories = explode(',', $row['categories']);
        
        foreach ($categories as $category) {
            echo '<tr>
                <td>' . $category . ' </td>';
            foreach ($years as $key => $year) {
                $amount = isset($amounts[$key]) ? $amounts[$key] : '0';
                echo '<td>' . $amount. '</td>';
            }
            echo '<td><button>Edit</button></td>
            </tr>';
        }
    }
}else{

}

echo '
   </tbody>
    </table>';
?>

当前问题现象 #

运行代码后，费用项无法显示在对应的年份列中，当前HTML表格显示效果如下：

问题点：

• EXAMINATION本该显示在YEAR 2列，却出现在YEAR 1列
• GRADUATION本该显示在YEAR 3列，却出现在YEAR 1列

期望显示效果 #

我期望的表格显示格式如下：

解决方案（参考@Khang Tran的回答） #

1. 修正查询语句 #

调整查询分类数据的SQL语句，去掉不必要的GROUP_CONCAT(DISTINCT category)，并确保年份和金额按顺序关联：

SELECT 
    category, 
    GROUP_CONCAT(year ORDER BY year) AS category_years, 
    GROUP_CONCAT(amount ORDER BY year) AS amounts 
FROM `CONTRIBUTION FEES` 
WHERE program = '$program' 
GROUP BY category

2. 修正表格行渲染代码 #

替换原循环渲染行的逻辑，通过匹配年份索引来对应金额：

while($row = $result->fetch_assoc()){
    $category = $row['category'];
    $categoryYears = explode(',', $row['category_years']);
    $amounts = explode(',', $row['amounts']);
    
    echo '<tr>
        <td>' . $category . ' </td>';
    // 遍历所有年份列
    foreach ($years as $year) {
        // 查找当前年份在分类对应年份中的位置
        $yearKey = array_search($year, $categoryYears);
        // 根据位置获取对应金额，无数据则显示空
        $amount = ($yearKey !== false && isset($amounts[$yearKey])) ? $amounts[$yearKey] : '';
        echo '<td>' . $amount . '</td>';
    }
    echo '<td><button>Edit</button></td>
    </tr>';
}

完整修正代码 #

<?php  
echo '<table class="table table-bordered table-sm" id="table">
      <thead>';
          
include $_SERVER["DOCUMENT_ROOT"] . "/config.php"; 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$query = "SELECT GROUP_CONCAT(DISTINCT year ORDER BY year) AS years FROM `CONTRIBUTION FEES` WHERE program = '$program'";
$result = $conn->query($query);
$row = $result->fetch_assoc();
$years = explode(',', $row['years']);
if($row['years'] != ''){
    echo ' <tr>
        <th>CATEGORY</th>';
    foreach ($years as $year){
        echo ' <th>YEAR '.$year.'</th>';
    }
    echo ' 
        <th>ACTIONS</th>
       </tr>';
}

echo '
      </thead>
      <tbody>';

// 修正后的查询语句
$query = "SELECT 
            category, 
            GROUP_CONCAT(year ORDER BY year) AS category_years, 
            GROUP_CONCAT(amount ORDER BY year) AS amounts 
          FROM `CONTRIBUTION FEES` 
          WHERE program = '$program' 
          GROUP BY category";
$result = $conn->query($query);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()){
        $category = $row['category'];
        $categoryYears = explode(',', $row['category_years']);
        $amounts = explode(',', $row['amounts']);
        
        echo '<tr>
            <td>' . $category . ' </td>';
        foreach ($years as $year) {
            $yearKey = array_search($year, $categoryYears);
            $amount = ($yearKey !== false && isset($amounts[$yearKey])) ? $amounts[$yearKey] : '';
            echo '<td>' . $amount . '</td>';
        }
        echo '<td><button>Edit</button></td>
        </tr>';
    }
}else{
    echo '<tr><td colspan="'.(count($years)+2).'">No data found</td></tr>';
}

echo '
   </tbody>
    </table>';
?>

内容的提问来源于stack exchange，提问作者kelvin daniel