如何在PHP中实现基于MySQL的动态分页查询

<?php
// 假设 $db 变量已经包含了 mysqli 数据库连接对象
// 例如：
// $servername = "localhost";
// $username = "your_username";
// $password = "your_password";
// $dbname = "your_database";
// $db = new mysqli($servername, $username, $password, $dbname);
// if ($db->connect_error) {
//     die("连接失败: " . $db->connect_error);
// }
?>

<?php
// ... (数据库连接代码)

// 1. 获取总记录数
$sql_count = 'SELECT count(*) as `rows` FROM `special_infected_kills`';
$res_count = $db->query($sql_count);

if (!$res_count) {
    die("查询总记录数失败: " . $db->error);
}

$rs_count = $res_count->fetch_object();
$total_rows = $rs_count->rows; // 表中的总记录数

$pagesize = 20; // 每页显示20条记录
$total_pages = ceil($total_rows / $pagesize); // 计算总页数，向上取整

// ... (后续代码)
?>

<?php
// ... (之前的代码)

// 2. 确定当前页码
// 从URL获取页码，默认为1，并进行安全过滤
$current_page = filter_input(INPUT_GET, 'page', FILTER_SANITIZE_NUMBER_INT);
$current_page = $current_page ? (int)$current_page : 1; // 确保是整数，默认为1

// 调整页码，使其在有效范围内 (1 到 $total_pages)
if ($current_page < 1) {
    $current_page = 1;
}
if ($current_page > $total_pages) {
    $current_page = $total_pages;
}

// 计算SQL LIMIT子句的偏移量
// SQL的LIMIT OFFSET是0-indexed，而我们的$current_page是1-indexed
$offset = ($current_page - 1) * $pagesize;

// ... (后续代码)
?>

<?php
// ... (之前的代码)

// 3. 构建分页查询SQL
$sql_data = 'SELECT 
                STEAM_ID, Hunters, Smoker, Boomers, Spitters, Jockeys, Charger,
                ( Hunters + Smoker + Boomers + Spitters + Jockeys + Charger ) as `Total_Kills`
            FROM `Special_Infected_Kills` 
            ORDER BY `Total_Kills` DESC
            LIMIT ?, ?'; // 使用占位符

// 创建预处理语句
$stmt = $db->prepare($sql_data);
if (!$stmt) {
    die("预处理语句失败: " . $db->error);
}

// 绑定参数：'ii' 表示两个参数都是整数类型
// 第一个参数是偏移量 (offset)，第二个参数是每页记录数 (pagesize)
$stmt->bind_param('ii', $offset, $pagesize);

// 执行语句
$stmt->execute();

// 绑定结果到变量
$stmt->bind_result($sid, $hunters, $smoker, $boomers, $spitters, $jockeys, $charger, $kills);

// 4. 显示当前页数据
$output = '
    <table>
        <tr>
            <th>Player</th>
            <th>Total Kills</th>
            <th>Hunter</th>
            <th>Jockey</th>
            <th>Charger</th>
            <th>Smoker</th>
            <th>Boomer</th>
            <th>Spitter</th>
        </tr>';

while ($stmt->fetch()) {
    $output .= sprintf('
        <tr>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
        </tr>', 
        htmlspecialchars($sid), 
        htmlspecialchars($kills), 
        htmlspecialchars($hunters), 
        htmlspecialchars($jockeys), 
        htmlspecialchars($charger), 
        htmlspecialchars($smoker), 
        htmlspecialchars($boomers), 
        htmlspecialchars($spitters)
    );
}
$output .= '</table>';

$stmt->close(); // 关闭预处理语句

// ... (后续代码)
?>

<?php
// ... (之前的代码)

// 5. 生成分页导航链接
$footer = '
    <footer>
        <div class="center">
            <div class="pagination">';

// 生成“首页”、“上一页”、“下一页”、“末页”链接
$first_page_link = ($current_page > 1) ? '<a class="paging" href="?page=1">首页</a>' : '<span>首页</span>';
$prev_page_link = ($current_page > 1) ? sprintf('<a class="paging" href="?page=%s">上一页</a>', $current_page - 1) : '<span>上一页</span>';
$next_page_link = ($current_page < $total_pages) ? sprintf('<a class="paging" href="?page=%s">下一页</a>', $current_page + 1) : '<span>下一页</span>';
$last_page_link = ($current_page < $total_pages) ? sprintf('<a class="paging" href="?page=%s">末页</a>', $total_pages) : '<span>末页</span>';

$footer .= sprintf('<div>%s | %s | %s | %s</div>', $first_page_link, $prev_page_link, $next_page_link, $last_page_link);

// 生成数字页码链接
$footer .= sprintf('<a class="paging" href="?page=%s">&laquo;</a>', max(1, $current_page - 1)); // 稍微调整，使点击左箭头回到前一页

// 优化：只显示当前页码附近的页码，例如当前页前后各2页
$start_page = max(1, $current_page - 2);
$end_page = min($total_pages, $current_page + 2);

// 如果页码数量不足5个，则调整显示范围
if ($end_page - $start_page + 1 < 5) {
    if ($start_page == 1) {
        $end_page = min($total_pages, $start_page + 4);
    } elseif ($end_page == $total_pages) {
        $start_page = max(1, $end_page - 4);
    }
}


for ($i = $start_page; $i <= $end_page; $i++) {
    $active_class = ($current_page == $i) ? ' active' : '';
    $footer .= sprintf(' <a class="paging%2$s" href="?page=%1$d">%1$d</a> ', $i, $active_class);
}

$footer .= sprintf('<a class="paging" href="?page=%s">&raquo;</a>', min($total_pages, $current_page + 1)); // 稍微调整，使点击右箭头回到后一页

$footer .= '
            </div>
        </div>

        <div class="createdby">
            <p>Website built & designed by Blade</p>
        </div>
    </footer>';

// 渲染所有HTML
echo $output, $footer;

// 关闭数据库连接
$db->close();
?>