根据数据库记录修改HTML表格td作业提交状态显示异常如何解决

核心为循环内查询提交状态的SQL关联条件错误，具体问题点如下：

• 关联字段不匹配：使用assignment表的作业ID字段a_id匹配submitted_assignments表的作业名称字段a_name，两个字段存储内容完全不同，查询逻辑不符合预期
• 字符串字段未加引号：a_name为字符串类型字段，拼接SQL时未给值加单引号，导致SQL语法错误，查询结果异常
• 存在SQL注入风险：直接拼接字段值到SQL中未做转义处理，存在安全隐患
• 冗余代码：页面开头查询submitted_assignments的status字段属于无效代码，后续循环会覆盖$stat变量，可直接删除

快速修复（改动最小） #

修改循环内的查询语句为正确关联逻辑即可，若使用作业名称关联两张表，修改后代码如下：

$shcat=mysqli_query($con,"SELECT * FROM assignment INNER JOIN subject ON assignment.as_subject=subject.s_id WHERE s_course='$cr' AND a_semester='$sems'");
$cnt=1;
while($row=mysqli_fetch_array($shcat)){
    $stat = 0;
    // 转义作业名称，避免SQL注入和语法错误
    $assignment_name = mysqli_real_escape_string($con, $row['a_name']);
    // 修正关联条件：用作业名称匹配，字符串值加单引号
    $res=mysqli_query($con,"SELECT status FROM submitted_assignments WHERE s_name='$user' AND a_name='".$assignment_name."'");
    if($res && $row2=mysqli_fetch_array($res)){
        $stat=$row2['status'];
    }
    // 后续表格渲染代码保持不变
}

若submitted_assignments表实际存储的是作业ID，仅字段名误写为a_name，将关联条件修改为a_name = ".$row['a_id']即可。

优化方案（性能更好） #

无需循环查询数据库，直接使用LEFT JOIN一次性查询所有作业的提交状态，减少数据库交互次数，代码更简洁：

<?php
session_start();
include 'connection.php';
$user = mysqli_real_escape_string($con, $_SESSION['details1']);
// 一次性查询用户的课程、学期信息
$user_query = mysqli_query($con, "SELECT course, semester FROM student WHERE username='$user' LIMIT 1");
$user_info = mysqli_fetch_array($user_query);
$cr = $user_info['course'];
$sems = $user_info['semester'];

// 用LEFT JOIN关联作业表和提交表，一次性拿到所有作业的提交状态
$shcat = mysqli_query($con, "SELECT a.*, s.s_name, IFNULL(sa.status, 0) as submit_status 
FROM assignment a
INNER JOIN subject s ON a.as_subject = s.s_id
LEFT JOIN submitted_assignments sa ON a.a_name = sa.a_name AND sa.s_name = '$user'
WHERE s.s_course='$cr' AND a.a_semester='$sems'");

$cnt=1;
while($row=mysqli_fetch_array($shcat)){
?>
<tr>
    <td class="column0"><?php echo $cnt; ?></td>
    <td class="column1"><?php echo $row['a_name'];?></td>
    <td class="column2"><?php echo $row['s_name'];?></td>
    <td class="column2"><?php echo $row['date'];?></td>
    <td class="column0"><a target = "_blank" href="../faculty/assignment/<?php echo $row['image']?>"><i class="fa fa-eye"></i></a></td>
    <?php 
    if ($row['submit_status'] == 0){ ?>
    <td class="column0"><i class="fa fa-times" title="未提交"></i></td>
    <?php } 
    else {?>
    <td class="column0"><i class="fa fa-check" title="已提交"></i></td>
    <?php }?>
</tr>
<?php $cnt++; } ?>

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