How to implement query paging display function in php

When performing database queries, in some cases, we need to display the results in pages according to certain rules. For example, when we want each page of the client to display only a small amount of data, we need to use the query paging display function while achieving the page effect. This article will focus on the query paging function of the PHP development language and introduce its implementation principles and steps through examples.

The implementation principle of query paging display

The implementation principle of query paging display is very simple. We can sort all the queried data according to certain rules, and then limit the data displayed on each page. Quantity, display the query results in pages. We can use algorithms to calculate which record in the database needs to be displayed starting from and ending with.

The main steps are as follows:

1. Calculate the total number of records: Normally, we need to query all the records in the database, that is, the total number of records.

2. Calculate the total number of pages: By comparing the total number of records and the number of records displayed on each page, the total number of pages can be calculated. If the remainder obtained by dividing the total number of pages by the number of records displayed on each page is not zero, then the total number of pages needs to be increased by one.

3. Query the data of the current page: Calculate the start and end positions of the records to be queried on the current page, and then use SQL statements to query the data. However, the SQL statement here needs some modifications. It cannot simply query all records, but needs to add query conditions and limit the number of query results.

4. Paging display: Through the page paging component, paging display can be conveniently performed. Normally, the paging component displays information such as the total number of records, the current number of pages, and the total number of pages. At the same time, you can also query and display data on different pages by clicking on different pages.

Code implementation

In order to implement these steps, we need to use a PHP function to process paging. Normally, this function needs to receive some necessary parameters, such as the total number of records, the number of records displayed on each page, the current page number, etc. Taking the Mysql database as an example, the following is a common PHP paging function:

function get_data($page_size,$sql,$conn){
  global $start;
  if(!$page_size) $page_size = 10;
  $page = $_GET['page']?$_GET['page']:1;
  $start=($page-1)*$page_size; //这里是高亮应用,可以注释掉
  $sql_limit = $sql." limit $start,$page_size"; //组合成一条完整的sql语句
  $result = mysql_query($sql_limit,$conn);
  while($row = mysql_fetch_array($result)){
      $data[] = $row;
  }
  return $data;
}

The above function mainly implements querying and returning the data that needs to be displayed based on the number of displays per page, the total number of records, and the current number of pages. Function. Next, we will work with you to implement a PHP query page that includes paging functionality.

First, we need to construct a data source. Here we use a simple students table (its fields include id, name, gender, age):

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `gender` enum('male','female') NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then insert the data into the students table Medium:

INSERT INTO `students` (`id`, `name`, `gender`, `age`) VALUES
(1, 'Tom', 'male', 19),
(2, 'Lucy', 'female', 20),
(3, 'Lily', 'female', 18),
(4, 'Kevin', 'male', 19),
(5, 'Steve', 'male', 21),
(6, 'Tony', 'male', 20),
(7, 'Henry', 'male', 20),
(8, 'Alex', 'male', 18),
(9, 'Sara', 'female', 21),
(10, 'Lily', 'female', 20),
(11, 'Michael', 'male', 19),
(12, 'Paul', 'male', 22),
(13, 'Elena', 'female', 18),
(14, 'Jack', 'male', 21),
(15, 'Bob', 'male', 19),
(16, 'Sophie', 'female', 20),
(17, 'Lisa', 'female', 21),
(18, 'Lucas', 'male', 19),
(19, 'Laura', 'female', 18),
(20, 'John', 'male', 20);

We need to implement the following 3 files:

1. index.php. This file is the actual carrier of the paging process. The function that handles the paging process needs to be called, and Data output is performed based on the return value of the function.
2. page.class.php, this class mainly implements the paging function, including the parameters that need to be passed in when defining the paging object and the core functions related to calculating paging.
3. db.class.php, this class is mainly used to encapsulate and manage database-related operations.

The content is as follows:

index.php:

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>PHP分页查询 - 测试页面</title>
</head>

<body>
    <?php
        include("db.class.php"); //加载数据库操作类
        include("page.class.php"); //加载分页类
        $page = new page(); //初始化分页类
        $db = new db("localhost","root","","test","$conn"); //连接数据库
        $count = $db->query("SELECT count(*) FROM students"); //查询总记录数
        $page->get_page($count,10); //调用分页方法，传入总记录数和每页显示记录数
        $genders = array("male"=>"男性","female"=>"女性");
        $results = $db->query("SELECT * FROM students {$page->limit}"); //获取当前页数据
        foreach($results as $row){ //遍历数据并渲染页面
            $gender = $genders[$row["gender"]];
            echo $row["id"]." ".$row["name"]." ".$gender." ".$row["age"]."<br/>";
        }
        echo $page->display_pages(); //渲染分页组件
    ?>
</body>
</html>

page.class.php:

<?php
/**
 * PHP分页类
 */
class page {
    public $total = 0; //总记录数
    public $page_size = 10; //每页显示的记录数
    public $cur_page = 1; //当前页数
    public $cur_offset = 0; //当前偏移量
    public $total_page = 0; //总页数
    public $limit = "";//分页查询限制语句

    public function __construct(){
        $this->cur_page = isset($_GET['page'])? intval($_GET['page']):1; //获取当前页，默认为第一页
    }

    /**
     * 生成分页显示代码
     */
    public function display_pages(){
        $page_list = "";
        $url_s = $_SERVER['REQUEST_URI'] . (strpos($_SERVER['REQUEST_URI'], '?')?'':"?")."&page=";
        //上一页
        if($this->cur_page>1){
            $page_list.="<a href=&#39;{$url_s}1&#39;>首页</a>";
            $page_list.="<a href=&#39;{$url_s}".($this->cur_page-1)."'>上一页</a>";
        }
        //数字页码
        for($i=1;$i<=$this->total_page;$i++){
            if($i==$this->cur_page){
                $page_list.="<span class=&#39;current&#39;>".$i."</span>";
            } else {
                $page_list.="<a href=&#39;{$url_s}{$i}&#39;>$i</a>";
            }
        }
        //下一页
        if($this->cur_page<$this->total_page){
            $page_list.="<a href=&#39;{$url_s}".($this->cur_page+1)."'>下一页</a>";
            $page_list.="<a href=&#39;{$url_s}{$this->total_page}'>尾页</a>";
        }
        $page_list.="共{$this->total_page}页，";
        $page_list.="<form method=&#39;&#39; action=&#39;&#39;><input type=&#39;text&#39; size=&#39;1&#39; name=&#39;page&#39;/><input type=&#39;submit&#39; value=&#39;跳转&#39;/></form>";
        return $page_list;
    }

    /**
     * 计算分页相关参数
     * @param int $count 总记录数
     * @param int $page_size 每页显示记录数
     */
    public function get_page($count,$page_size){
        $this->total = intval($count); //总记录数
        $this->page_size = intval($page_size); //每页显示多少条
        $this->total_page = ceil($this->total/$this->page_size); //总页数
        $this->cur_page = min($this->total_page,max(1,intval($this->cur_page))); //当前页码
        $this->cur_offset = ($this->cur_page-1)*$this->page_size; //当前查询的偏移量
        $this->limit = " LIMIT {$this->cur_offset},{$this->page_size}"; //查询限制语句
    }
}

db.class.php:

<?php
/**
 * 数据库操作类
 */
class db {
    private $conn;

    public function __construct($db_host,$db_user,$db_pass,$db_name){
        $this->conn = new mysqli($db_host,$db_user,$db_pass,$db_name);
        if(mysqli_connect_errno()) {
            exit("连接数据库失败！");
        }
        $this->conn->query("SET NAMES 'utf8'");
    }

    /**
     * mysql查询
     */
    public function query($sql){
        $results = array();
        $query = $this->conn->query($sql);
        if(!$query) return false;
        while($row = $query->fetch_array(MYSQLI_ASSOC)){
            $results[] = $row;
        }
        return $results;
    }

    public function __destruct(){
        $this->conn->close();
    }
}

The execution steps of the paging code are as follows:

1. First, we need to call db.class.php to establish a connection to the database;
2. Query through the query() method The total number of records;
3. Call the get_page() method to pre-calculate the total number of pages to be displayed, and define the limit value in the query statement to limit the number and the starting point of the query;
4. Render the paging display component by using the rendering function display_pages().

After running the index.php file, we will see a paginated display page, which will display the number of displays per page, the page number list, and the accuracy of the display results in the way we expected.

The above is the detailed content of How to implement query paging display function in php. For more information, please follow other related articles on the PHP Chinese website!