How Vue+ElementUI implements paging function to query mysql data

1. Problem

When there is a lot of data in the database, only a part of it must be queried at a time to relieve the pressure on the server and the page. Here we use the Pagination component of elementui and the limit statement of mysql to implement paging query of mysql data.

The following picture is the most basic paging style:

Of course, corresponding events need to be introduced to achieve page changes. Just query the database.

2. Solve

2.1 Paging component

<el-pagination
        background
        layout="prev, pager, next"
        :page-size="8"
        :total="total"
        :current-page="pageNum"
        @current-change="handleCurrentChange">
</el-pagination>

data: Initialize the total number of data items (total) is 1, pageNum means that the current page number is the first page.

2.2 Function to obtain database data: getData():

The parameters are offset, limit, request data from the backend, explained later. The qs serialization parameters are used here. You can refer to my other blog: Vue ElementUI ViewerThe picture cannot be previewed after turning the page. The problem of asynchronous communication between Vue parent and child components explains the function of qs.

getData(offset,limit){
      this.axios.post('/php/select.php', qs.stringify({
        offset: offset,
        limit: limit,
        type: '失物招领'
      }), { headers: { 'Content-Type': 'application/x-www-form-urlencoded' } }).then((res) => {
        if(res.data === 0){
          this.total = 0;
          this.list = [];
          return;
        }
        this.total = res.data.total
        this.list = res.data.data
        this.loading = false
      }).catch((err) => {
        this.$message.error(err)
      })
    }

2.3 The page is loaded and the data of the first page needs to be requested

created () {
    this.getData(0,8);
  },

Page change triggers the handleCurrentChange() function, that is, click to turn the page, where the val parameter is Current page number, use new parameters,

Call getData to query data on different pages:

handleCurrentChange(val){
      this.list = [] //清空上一页数据
      this.getData((val-1)*8,8);
    }

The following is the back-end data: php mysql<br>

There are now a total of 10 pieces of data in the data table:

Front endgetDatarequest The select.php file

select.php:

<?php
$servername = "localhost";
$username = "用户名";
$password = "密码";
$dbname = "数据库名称";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
} 

$type = $_POST[&#39;type&#39;];
//获取前端的参数 开始和结束number
if ( !isset( $_POST[&#39;offset&#39;] ) ) {
 echo 0;
 exit();
};
$offset = ( int )$_POST[&#39;offset&#39;];

if ( !isset( $_POST[&#39;limit&#39;] ) ) {
 echo 0;
 exit();
};
$limit = ( int )$_POST[&#39;limit&#39;];
//分页查询数据库
$sql = "SELECT * FROM posts where type=&#39;$type&#39;  order by id desc LIMIT $limit OFFSET $offset";
$result = $conn->query($sql);

$sqlGetCount = "SELECT COUNT(*) cnt FROM posts where type=&#39;$type&#39;";
$rescnt = $conn->query($sqlGetCount);
$rescnt = $rescnt->fetch_assoc();
$arr = array();
if ($result->num_rows > 0) {
 while ( $row = $result->fetch_assoc() ) {
    array_push( $arr, $row );
}
 //echo json_encode( $arr, JSON_UNESCAPED_UNICODE );
 echo json_encode(array_merge(array(&#39;data&#39;=>$arr),array(&#39;total&#39;=>(int)$rescnt[&#39;cnt&#39;])));
 
} else {
    echo 0;
}
mysqli_close( $conn );
?>

## of mysql is used here #limit implements querying only a part of the data at a time, and the parameters offset and limit are passed from the front end.

sql statement:

"SELECT * FROM posts where type='$type'  order by id desc LIMIT $limit OFFSET $offset"

3. Analysis of

here

LIMIT $limit OFFSET $offset means starting from the value of $offest, query $limit pieces of data.

For example $limit = 8, $offest = 0: means querying the first 8 pieces of data in the database, starting from 0 (excluding 0, the mysql index starts from 0), Query 8 items, that is, 1~8 pieces of data.
When I click on the second page: Trigger handleCurrentChange() Function:

Parameters at this time

val=2, then offest = 8, limit = 8. The 9th to 17th data will be queried. If there is no 17th data, all data after the 9th data will be returned. For example, my database currently only has 10 pieces of data, so the 9th and 10th pieces of data will be returned.

At the same time, the page in select.php returns the total number of data total:

SELECT COUNT(*) cnt FROM posts where type='$type'

The front-end page gets

total value is assigned to this.total (it is bound to the total attribute of Pagination, which is the total number of data items). PaginationAccording to the:page-size="8"property, the data will be automatically paginated. For example, if the total returned by the backend is 10, it will be divided into two pages.

4. Result

The page is loaded: Because I was querying in reverse order based on the id, I got the 3rd ~10 pieces of data (8 pieces in total).

Click the second page or page turn button: Get the 1st and 2nd data.

Note: Your limit parameter must be consistent with the page-size of Pagination The attributes are consistent, which means one page of data is queried at a time. And offset is the current page number.

The above is the detailed content of How Vue+ElementUI implements paging function to query mysql data. For more information, please follow other related articles on the PHP Chinese website!