PHP按TERM_CODE分组展示学生成绩单数据的实现求助

问题描述 #

我的数据库中有如下表格：

我希望用PHP实现如下展示效果：

Transcript  
Id:555
TERM_CODE: 202210

TERM_CODE: 202220

原代码无法实现分组展示，代码如下：

<?php 
echo'  <table class="tab"  cellpadding="2" cellspacing="2" border="1"  >';
      
include 'db_conf.php';
$conn = sqlsrv_connect( $serverName, $connectionInfo); 
$tsql = "SELECT *FROM OPENQUERY (student, 'select * from INTEGRATION.TRANSCRIPT  where ID=555  ')ORDER BY TERM_DESC "; 
$stmt = sqlsrv_query( $conn, $tsql); 

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
    echo "</tr>";
    echo "<td>" .$row['TERM_CODE']."</td>";
    echo "<tr>";
           
    echo "<tr>";
    echo "<td>COURSE_TITLE</td>";
    echo "<td>COURSE_HOURS</td>";
    echo "<td>GRADE</td>";
          
    echo "</tr>";
          
    echo "<td>" .$row['COURSE_TITLE']."</td>";
    echo "<td>" .$row['COURSE_HOURS']."</td>";
    echo "<td>" .$row['GRADE']."</td>";
     
    echo "</tr>";
}

echo "</table>";
?>

修正后的代码 #

<?php
include 'db_conf.php';
$conn = sqlsrv_connect($serverName, $connectionInfo); 

// 查询数据并按学期代码排序，确保同学期记录连续
$tsql = "SELECT * FROM OPENQUERY (student, 'select * from INTEGRATION.TRANSCRIPT where ID=555 ') ORDER BY TERM_CODE"; 
$stmt = sqlsrv_query($conn, $tsql); 

// 按学期分组整理课程数据
$transcriptData = [];
$studentId = '';
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    $termCode = $row['TERM_CODE'];
    $studentId = $row['id'];
    
    $transcriptData[$termCode][] = [
        'COURSE_TITLE' => $row['COURSE_TITLE'],
        'COURSE_HOURS' => $row['COURSE_HOURS'],
        'GRADE' => $row['GRADE']
    ];
}

// 输出成绩单标题与学生ID
echo "<h3>Transcript</h3>";
echo "<p>Id: {$studentId}</p>";

// 循环输出每个学期的课程信息
foreach ($transcriptData as $termCode => $courses) {
    echo "<pre>TERM_CODE: {$termCode}</pre>";
    
    // 输出课程表格
    echo '<table class="s-table" cellpadding="2" cellspacing="2" border="1">';
    // 表头
    echo '<thead>
            <tr>
                <th>COURSE_TITLE</th>
                <th>COURSE_HOURS</th>
                <th>GRADE</th>
            </tr>
          </thead>';
    // 课程内容行
    echo '<tbody>';
    foreach ($courses as $course) {
        echo '<tr>
                <td>' . htmlspecialchars($course['COURSE_TITLE']) . '</td>
                <td>' . htmlspecialchars($course['COURSE_HOURS']) . '</td>
                <td>' . htmlspecialchars($course['GRADE']) . '</td>
              </tr>';
    }
    echo '</tbody></table><br>';
}

// 关闭数据库连接
sqlsrv_close($conn);
?>

修正说明 #

1. 数据分组：先将所有查询结果按TERM_CODE分组存储，避免原代码中每条记录重复输出学期和表头的问题
2. 逻辑分离：将数据获取、整理与页面输出逻辑分开，代码结构更清晰易维护
3. 安全优化：添加htmlspecialchars()转义输出内容，防止XSS注入风险
4. 资源优化：添加数据库连接关闭操作，合理释放资源

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