求助：实现数据库记录的循环ID范围分页查询

我来帮你搞定这个循环分页的需求！首先得理清你当前代码的问题：你用BETWEEN '$attempt' AND '100'限制了最大ID到100，导致取不到101-105的记录，而且没有处理“到末尾后回到开头继续取”的循环逻辑。

下面分两种场景给你解决方案，你可以根据自己的数据库情况选择：

场景1：ID是连续自增的（1到105） #

如果你的ID字段是连续的自增主键，直接基于ID计算起始和结束位置的逻辑会更直观：

// 基础配置
$per_page = 10;
// 推荐从数据库动态获取总记录数，避免硬编码
$total_query = "SELECT COUNT(*) AS total FROM `tbl_quickten`";
$total_result = mysqli_query($con, $total_query);
$total_row = mysqli_fetch_assoc($total_result);
$total_records = $total_row['total'];

// 跟踪当前起始ID：可从URL参数或Session获取，第一次初始化为你原来的10
$start_id = isset($_GET['start_id']) ? (int)$_GET['start_id'] : 10;

$records = [];
$end_id = $start_id + $per_page - 1;

if ($end_id > $total_records) {
    // 分两次查询：先取到最后一条，再从开头补剩余数量
    // 1. 取当前起始ID到最后一条的记录
    $stmt1 = mysqli_prepare($con, "SELECT * FROM `tbl_quickten` WHERE `ID` BETWEEN ? AND ?");
    mysqli_stmt_bind_param($stmt1, "ii", $start_id, $total_records);
    mysqli_stmt_execute($stmt1);
    $result1 = mysqli_stmt_get_result($stmt1);
    $records = array_merge($records, mysqli_fetch_all($result1, MYSQLI_ASSOC));
    
    // 计算还需要补多少条
    $remaining = $per_page - count($records);
    if ($remaining > 0) {
        // 2. 从开头取剩余数量的记录
        $stmt2 = mysqli_prepare($con, "SELECT * FROM `tbl_quickten` WHERE `ID` BETWEEN ? AND ?");
        mysqli_stmt_bind_param($stmt2, "ii", 1, $remaining);
        mysqli_stmt_execute($stmt2);
        $result2 = mysqli_stmt_get_result($stmt2);
        $records = array_merge($records, mysqli_fetch_all($result2, MYSQLI_ASSOC));
    }
    
    // 下一次的起始ID：从开头取的最后一条ID+1
    $next_start_id = $remaining + 1;
} else {
    // 正常分页查询
    $stmt = mysqli_prepare($con, "SELECT * FROM `tbl_quickten` WHERE `ID` BETWEEN ? AND ?");
    mysqli_stmt_bind_param($stmt, "ii", $start_id, $end_id);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $records = mysqli_fetch_all($result, MYSQLI_ASSOC);
    
    // 下一次的起始ID：当前结束ID+1
    $next_start_id = $end_id + 1;
}

// 循环重置：如果下一次起始ID超过总记录数，回到1
if ($next_start_id > $total_records) {
    $next_start_id = 1;
}

// 后续可把$next_start_id传递给下一次请求，比如放在URL里：?start_id=<?= $next_start_id ?>

场景2：ID不连续（通用方案） #

如果你的ID可能有缺失（比如删除过记录），用偏移量的方式更可靠，不管ID是否连续都能正确分页：

// 基础配置
$per_page = 10;
// 动态获取总记录数
$total_query = "SELECT COUNT(*) AS total FROM `tbl_quickten`";
$total_result = mysqli_query($con, $total_query);
$total_row = mysqli_fetch_assoc($total_result);
$total_records = $total_row['total'];

// 跟踪当前偏移量：偏移量从0开始，初始值对应你原来的第10条记录（偏移量是9）
$current_offset = isset($_GET['offset']) ? (int)$_GET['offset'] : 9;

$records = [];
$remaining = $per_page;

if ($current_offset + $remaining > $total_records) {
    // 1. 先取当前偏移量到末尾的记录
    $first_part_limit = $total_records - $current_offset;
    $stmt1 = mysqli_prepare($con, "SELECT * FROM `tbl_quickten` LIMIT ?, ?");
    mysqli_stmt_bind_param($stmt1, "ii", $current_offset, $first_part_limit);
    mysqli_stmt_execute($stmt1);
    $result1 = mysqli_stmt_get_result($stmt1);
    $records = array_merge($records, mysqli_fetch_all($result1, MYSQLI_ASSOC));
    
    // 计算剩余需要取的数量
    $remaining -= $first_part_limit;
    // 2. 从开头取剩余数量的记录
    $stmt2 = mysqli_prepare($con, "SELECT * FROM `tbl_quickten` LIMIT ?, ?");
    mysqli_stmt_bind_param($stmt2, "ii", 0, $remaining);
    mysqli_stmt_execute($stmt2);
    $result2 = mysqli_stmt_get_result($stmt2);
    $records = array_merge($records, mysqli_fetch_all($result2, MYSQLI_ASSOC));
    
    // 下一次的偏移量：剩余数量
    $next_offset = $remaining;
} else {
    // 正常分页查询
    $stmt = mysqli_prepare($con, "SELECT * FROM `tbl_quickten` LIMIT ?, ?");
    mysqli_stmt_bind_param($stmt, "ii", $current_offset, $per_page);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $records = mysqli_fetch_all($result, MYSQLI_ASSOC);
    
    // 下一次的偏移量
    $next_offset = $current_offset + $per_page;
}

// 循环重置：偏移量超过总记录数时取模回到开头
if ($next_offset >= $total_records) {
    $next_offset = $next_offset % $total_records;
}

// 后续把$next_offset传递给下一次请求，比如：?offset=<?= $next_offset ?>

重要提示 #

• 代码里用了参数化查询（mysqli_prepare），这能有效防止SQL注入，比直接拼接字符串安全得多，一定要用这种方式！
• 你需要把$next_start_id或$next_offset传递给下一次查询请求，比如通过URL参数、Session或者前端存储，这样才能记住当前的分页位置。

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