Vue+ElementUI implements paging function-mysql data

1. Problem

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

The following figure is the most basic paging style:

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

2. Solution

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 entries ( total ) to 1, pageNum , that is, the current page number, to the first page.

2.2 Function to get database data: getData():

The parameters are offset and limit , which are used to request data from the backend. I will explain this later. The qs serialization parameter is used here. You can refer to my other blog: Vue + ElementUI + Viewer The picture cannot be previewed after turning the page. Vue parent-child component asynchronous communication problem explains the function of qs.

    getData(offset,limit){
      this.axios.post('/php/select.php', qs.stringify({
        offset: offset,
        limit: limit,
        type: 'Lost and Found'
      }), { 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);
  },

The page change triggers the handleCurrentChange() function, that is, the page is turned, where the val parameter is the current page number. Use the new parameter.

Call getData to query data on different pages:

    handleCurrentChange(val){
      this.list = [] // Clear the previous page data this.getData((val-1)*8,8);
    }

Below is the backend data: php + mysql

Now there are 10 records in the data table:

The select.php file requested by the front-end getData

select.php:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "Database name";

// Create a connection $conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$type = $_POST['type'];
//Get the start and end number of the front-end parameters
if ( !isset( $_POST['offset'] ) ) {
 echo 0;
 exit();
};
$offset = ( int )$_POST['offset'];

if ( !isset( $_POST['limit'] ) ) {
 echo 0;
 exit();
};
$limit = ( int )$_POST['limit'];
//Pagination query database $sql = "SELECT * FROM posts where type='$type' order by id desc LIMIT $limit OFFSET $offset";
$result = $conn->query($sql);

$sqlGetCount = "SELECT COUNT(*) cnt FROM posts where type='$type'";
$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('data'=>$arr),array('total'=>(int)$rescnt['cnt'])));
 
} else {
    echo 0;
}
mysqli_close( $conn );
?>

Here, mysql limit is used to query 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

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

For example, $limit = 8, $offest = 0: means querying the first 8 data in the database, starting from 0 (not including 0, MySQL index starts from 0), querying 8 data, that is, 1 to 8 data.
When I click on the second page: the handleCurrentChange(） function is triggered:

At this time, the parameter 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, if there are 10 records in my database, then the 9th and 10th records will be returned.

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

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

After the front-end page obtains the total value, it assigns it to this.total (bound to Pagination的total , which is the total number of data items). Pagination will automatically page the data according to page-size="8" attribute. For example, if the total returned by the backend is 10, it is divided into two pages.

4. Results

Note: Your limit parameter must be consistent with the page-size attribute of Pagination , that is, query one page of data at a time. offset is the current page number.

This is the end of this article about Vue+ElementUI implementing paging query-mysql data. For more relevant Vue+ElementUI implementing paging query content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!