PHP+MySQL实现漏访婴幼儿展示下一次预约日程的方法求助

若婴幼儿ID对应到达now()日期的预约日程已错过，需查询展示该婴幼儿的下一条预约日程。
附数据库表结构：

<?php
// Set the new timezone
date_default_timezone_set('Asia/Dhaka');
$date = date("Y-m-d");
//$date = '2022-06-09';
//echo $date;
//$tomorrow = date("Y-m-d", strtotime("+1 day"));
//echo $tomorrow;
?>
        <div class="card-body">
         <div class="table-responsive" id="dynamic_content">
                <table class="table table-striped">
                    <thead>
                    <tr>
                    <th scope="col">Infant ID</th>
                    <th scope="col">Mother Name</th>
                    <th scope="col">Child Name</th>
                    <th scope="col">DOB</th>
                    <th scope="col">Schedule Date</th>
                    <th scope="col">Visit Date</th>
                    <th scope="col">Visit Status</th>
                    <th scope="col">Serv Diag</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php
                    //$query="SELECT * FROM `infant_schedule` WHERE Visit_Status=0 AND ('".$date."' OR '".$tomorrow."' ) between Schedule_date and Visit_date"; 
                    $query="SELECT * FROM `infant_schedule` WHERE Visit_Status=0 AND '".$date."' between Schedule_date and Visit_date"; 
 
                    if($result = mysqli_query($conn, $query)){
                        if(mysqli_num_rows($result) > 0){
                             while($row = mysqli_fetch_array($result)){

                    ?>
                    <tr>
                    <form action="insert.php" method="post">
                    <td><input type="text" name="Infant_id" value="<?php echo $row['Infant_id'];?>" readonly></td>
                    <td><input type="text" name="Mother_Name" value="<?php echo $row['Mother_Name'];?>" readonly></td>
                    <td><input type="text" name="Child_Name" value="<?php echo $row['Child_Name'];?>" readonly></td>
                    <td><input type="text" name="DOB" value="<?php echo $row['DOB'];?>" readonly></td>
                    <td><input type="text" name="Schedule_date" value="<?php echo $row['Schedule_date'];?>" readonly></td>
                    <td><input type="text" name="Visit_date" value="<?php echo $row['Visit_date'];?>" readonly></td>
                    <td><input type="text" name="Visit_Status" value="<?php echo $row['Visit_Status'];?>"readonly></td>
                    <!--<td><select name="Serv_Diag" id="Serv_Diag">
                        <option value="No">Yes</option>
                        <option value="">No</option>
                        </select>
                    </td>-->
                    <td><input type="text" name="Serv_Diag" value="<?php echo $row['Serv_Diag'];?>" readonly></td>
                    <td><input type="hidden" id="nursename" name="nursename" value="<?php echo ucwords($_settings->userdata('username')) ?>"></td>
                    <td><input class="btn btn-primary edit" type="submit" value="Submit"></td>
                    </form>
                    </tr>
                    <?php 
                             }
                        }
                             } ?>
                    </tbody>
                </table>
          </div>

现有逻辑仅能查询当前日期处于Schedule_date和Visit_date区间内的未访视记录，若婴幼儿错过访视日期（例如访视日期为2022-06-05已逾期），无法自动展示该婴幼儿的下一次预约日程。

核心思路调整为：查询每个婴幼儿所有未完成访视的预约中，离当前日期最近的未来预约记录，直接替换原有SQL查询逻辑即可。

• 如果使用MySQL 8.0及以上版本，支持窗口函数，查询逻辑更简洁，同时建议改用预处理语句避免SQL注入风险：

<?php
date_default_timezone_set('Asia/Dhaka');
$date = date("Y-m-d");
// 预处理查询
$stmt = $conn->prepare("
SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY Infant_id 
            ORDER BY Schedule_date ASC
        ) as row_rank
    FROM infant_schedule
    WHERE Visit_Status = 0 
    AND Schedule_date >= ?
) temp
WHERE row_rank = 1
");
// 绑定参数
$stmt->bind_param("s", $date);
$stmt->execute();
$result = $stmt->get_result();
?>

逻辑说明：

• 按婴幼儿ID分组，将每个婴幼儿的未完成预约按预约日期从早到晚排序
• 过滤掉所有预约日期早于当前日期的过期无效预约
• 每个婴幼儿仅取排序后的第一条记录，即当前需要展示的最近一次待访视预约，覆盖逾期后自动展示下一次预约的需求
• 如果使用MySQL 5.x等不支持窗口函数的版本，可使用关联子查询实现同等效果：

<?php
date_default_timezone_set('Asia/Dhaka');
$date = date("Y-m-d");
$stmt = $conn->prepare("
SELECT s.* 
FROM infant_schedule s
INNER JOIN (
    SELECT Infant_id, MIN(Schedule_date) as next_schedule
    FROM infant_schedule
    WHERE Visit_Status = 0
    AND Schedule_date >= ?
    GROUP BY Infant_id
) t ON s.Infant_id = t.Infant_id AND s.Schedule_date = t.next_schedule
WHERE s.Visit_Status = 0
");
$stmt->bind_param("s", $date);
$stmt->execute();
$result = $stmt->get_result();
?>

• 可选优化：如果需要在页面标记逾期未访视的婴幼儿，可在查询字段中增加状态判断，结合前端样式给出醒目提示：

SELECT s.*, CASE WHEN EXISTS(
    SELECT 1 FROM infant_schedule s2 
    WHERE s2.Infant_id = s.Infant_id 
    AND s2.Visit_Status = 0 
    AND s2.Visit_date < ?
) THEN '有逾期记录' ELSE '正常' END as schedule_tag
-- 后续剩余查询逻辑和上述一致

原有页面的表格渲染、表单提交逻辑无需修改，替换查询代码段即可生效。

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