PHP MySQL按日期查看车辆检查记录功能异常排查求助

问题描述 #

我有一组按日期分组的车辆检查提交记录，列表展示日期及提交人。点击「View」按钮时，应跳转至对应日期的详细检查页面，展示该日期下所有检查项的名称、状态及备注。

我将列表设置为表单，点击「View」时通过POST提交至view-submitted-check.php页面，但目前功能异常：点击顶部的「03-21-2024」对应的View按钮，却显示「03-20-2024」的检查记录。

原列表页面代码 #

$checkssql = " SELECT * FROM $appname2 GROUP BY date ORDER BY date DESC";
$checksqry = mysqli_query($conn, $checkssql);

<form name="submittedchecks" id="submittedchecks" method="POST" action="view-submitted-check.php">
   <div class="table-responsive text-nowrap">   
      <table class="table" >
         <thead>
            <tr>
            <th>Date</th>
            <th>Submitted By</th>
            </tr>
         </thead>
         <tbody class="table-border-bottom-0">
            <?php while ($checks = mysqli_fetch_array($checksqry)) {{
            echo '<input type=hidden name="checkdate[]" value='. $checks['date']. '>';
            echo '<input type="text" name="appname" id="appname" readonly value="'.$appname2.'" hidden="hidden">';
            echo '<tr>';
            echo '<td> <input type="text" value="'.$checks['date'].'" name="date[]" id="date" style="border: none" readonly></td>';
            echo '<td>'.$checks['submitted_by'].'</td>';
            echo "<td> <button class='btn btn-info' type='submit' name='submitview' id='submitview'>View</button> </td> " ;}
            if($row['user_type']=='admin'){  echo "<td> <a href=delete-truck-check-script.php?id=".$checks['id']."><button class='btn btn-danger' type='button' >Delete Check</button></a> </td> " ;} 
echo'</tr>'; }?>
      </table>
   </div>
</form>

原接收页面代码 #

foreach ($_POST['checkdate'] as $index => $id){ 
    
$appname = $_POST['appname'];
$date = $_POST['date'][$index];
    
$viewlog = "SELECT * FROM $appname WHERE date = '$date' ORDER BY id";
$viewresult = mysqli_query($conn, $viewlog);
     
 }

<table class="table" >
   <thead>
      <tr>
      <th>Check Name</th>
      <th>Status</th>
      <th>Comment</th>
      </tr>
   </thead>
   <tbody class="table-border-bottom-0">
      <?php while ($checkview = mysqli_fetch_array($viewresult)) {{
      echo '<tr>';
      echo '<td>' .$checkview['check_name']. '</td>';
      if($checkview['status']=='Pass'){  echo "<td> <label class='btn rounded-pill btn-outline-success'>Pass</label> </td> " ;} 
      if($checkview['status']=='Warning'){  echo "<td> <label class='btn rounded-pill btn-outline-warning'>Warning</label> </td> " ;}
      if($checkview['status']=='Fail'){  echo "<td> <label class='btn rounded-pill btn-outline-danger'>Fail</label> </td> " ;}
      echo '<td>' .$checkview['comment']. '</td>';}
      echo'</tr>'; }?>
</table>

错误原因分析 #

1. 表单结构问题：所有行的checkdate[]、date[]都放在同一个表单内，点击任意View按钮时会提交所有日期字段，而非当前行的目标日期。
2. 循环逻辑错误：接收页面的foreach循环会遍历所有提交的日期，最后一次循环的$date会覆盖之前的变量值，导致页面始终展示最后一条日期的记录。
3. SQL注入风险：直接将POST参数拼接到SQL语句中，存在严重的安全漏洞。

修复方案 #

修复后的列表页面代码 #

$checkssql = "SELECT * FROM $appname2 GROUP BY date ORDER BY date DESC";
$checksqry = mysqli_query($conn, $checkssql);
?>
<div class="table-responsive text-nowrap">   
  <table class="table" >
    <thead>
      <tr>
        <th>Date</th>
        <th>Submitted By</th>
        <th>Actions</th>
        <?php if($row['user_type']=='admin'): ?>
          <th>Admin Actions</th>
        <?php endif; ?>
      </tr>
    </thead>
    <tbody class="table-border-bottom-0">
      <?php while ($checks = mysqli_fetch_array($checksqry)): ?>
      <tr>
        <td><?php echo $checks['date']; ?></td>
        <td><?php echo $checks['submitted_by']; ?></td>
        <td>
          <!-- 每行单独创建表单，仅提交当前行的日期 -->
          <form method="POST" action="view-submitted-check.php">
            <input type="hidden" name="checkdate" value="<?php echo $checks['date']; ?>">
            <input type="hidden" name="appname" value="<?php echo $appname2; ?>">
            <button class='btn btn-info' type='submit' name='submitview'>View</button>
          </form>
        </td>
        <?php if($row['user_type']=='admin'): ?>
          <td>
            <a href="delete-truck-check-script.php?id=<?php echo $checks['id']; ?>">
              <button class='btn btn-danger' type='button'>Delete Check</button>
            </a>
          </td>
        <?php endif; ?>
      </tr>
      <?php endwhile; ?>
    </tbody>
  </table>
</div>

修复后的接收页面代码 #

<?php
if(isset($_POST['submitview'])){
  $appname = $_POST['appname'];
  $date = $_POST['checkdate'];

  // 使用预处理语句避免SQL注入
  $viewlog = "SELECT * FROM $appname WHERE date = ? ORDER BY id";
  $stmt = mysqli_prepare($conn, $viewlog);
  mysqli_stmt_bind_param($stmt, "s", $date);
  mysqli_stmt_execute($stmt);
  $viewresult = mysqli_stmt_get_result($stmt);
}
?>
<table class="table" >
  <thead>
    <tr>
      <th>Check Name</th>
      <th>Status</th>
      <th>Comment</th>
    </tr>
  </thead>
  <tbody class="table-border-bottom-0">
    <?php if(isset($viewresult)): ?>
      <?php while ($checkview = mysqli_fetch_array($viewresult)): ?>
      <tr>
        <td><?php echo $checkview['check_name']; ?></td>
        <td>
          <?php if($checkview['status']=='Pass'): ?>
            <label class='btn rounded-pill btn-outline-success'>Pass</label>
          <?php elseif($checkview['status']=='Warning'): ?>
            <label class='btn rounded-pill btn-outline-warning'>Warning</label>
          <?php elseif($checkview['status']=='Fail'): ?>
            <label class='btn rounded-pill btn-outline-danger'>Fail</label>
          <?php endif; ?>
        </td>
        <td><?php echo $checkview['comment']; ?></td>
      </tr>
      <?php endwhile; ?>
    <?php endif; ?>
  </tbody>
</table>

关键修复点 #

• 给每行单独创建表单，确保点击View按钮时仅提交当前行的日期，避免批量提交所有日期。
• 移除接收页面的foreach循环，直接获取单个提交的日期值，解决变量覆盖问题。
• 使用MySQLi预处理语句绑定参数，彻底消除SQL注入风险。
• 优化HTML结构，将管理员操作列的判断逻辑移至表头，减少冗余代码。

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