php mysql实现分页

PHP MySQL 分页实现方法

基础分页实现

分页的核心是通过 SQL 的 LIMIT 子句限制查询结果范围。基本语法如下：

SELECT * FROM table_name LIMIT offset, per_page

PHP 代码示例：

$per_page = 10; // 每页显示数量
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $per_page;

$sql = "SELECT * FROM articles LIMIT $offset, $per_page";
$result = mysqli_query($conn, $sql);

获取总记录数

计算总页数需要先获取总记录数：

$total_query = "SELECT COUNT(*) as total FROM articles";
$total_result = mysqli_query($conn, $total_query);
$total_row = mysqli_fetch_assoc($total_result);
$total_records = $total_row['total'];
$total_pages = ceil($total_records / $per_page);

分页导航生成

生成基本的分页链接：

for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='?page=$i'>$i</a> ";
}

改进的分页类

更完整的分页处理可以封装成类：

class Pagination {
    private $conn;
    private $table;
    private $per_page;

    public function __construct($conn, $table, $per_page = 10) {
        $this->conn = $conn;
        $this->table = $table;
        $this->per_page = $per_page;
    }

    public function getData($page = 1) {
        $offset = ($page - 1) * $this->per_page;
        $sql = "SELECT * FROM {$this->table} LIMIT $offset, {$this->per_page}";
        return mysqli_query($this->conn, $sql);
    }

    public function getLinks($current_page, $url = '?page=') {
        $total = $this->getTotal();
        $pages = ceil($total / $this->per_page);

        $links = '';
        for ($i = 1; $i <= $pages; $i++) {
            $active = $i == $current_page ? ' class="active"' : '';
            $links .= "<a href='{$url}{$i}'{$active}>{$i}</a> ";
        }
        return $links;
    }

    private function getTotal() {
        $sql = "SELECT COUNT(*) as total FROM {$this->table}";
        $result = mysqli_query($this->conn, $sql);
        $row = mysqli_fetch_assoc($result);
        return $row['total'];
    }
}

使用预处理语句的安全版本

为防止 SQL 注入，使用预处理语句：

$stmt = $conn->prepare("SELECT * FROM articles LIMIT ?, ?");
$stmt->bind_param("ii", $offset, $per_page);
$stmt->execute();
$result = $stmt->get_result();

带搜索的分页

当有搜索条件时，需要在分页中保留搜索参数：

$search = isset($_GET['search']) ? $_GET['search'] : '';
$sql = "SELECT * FROM articles WHERE title LIKE ? LIMIT $offset, $per_page";
$stmt = $conn->prepare($sql);
$search_param = "%$search%";
$stmt->bind_param("s", $search_param);

分页链接需要包含搜索参数：

echo "<a href='?search=$search&page=$i'>$i</a>";

性能优化建议

对于大数据表，避免使用 COUNT(*)，可以考虑缓存总记录数或使用近似值。在 WHERE 条件复杂的查询中，确保有适当的索引。