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 ofelementuiand thelimitstatement ofmysqlto 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

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

2.2 Function to obtain database data: getData():

The parameters areoffset,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 thehandleCurrentChange()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


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

Front endgetDatarequest Theselect.phpfile

select.php:

connect_error) { die("连接失败: " . $conn->connect_error); } $type = $_POST['type']; //获取前端的参数 开始和结束number if ( !isset( $_POST['offset'] ) ) { echo 0; exit(); }; $offset = ( int )$_POST['offset']; if ( !isset( $_POST['limit'] ) ) { echo 0; exit(); }; $limit = ( int )$_POST['limit']; //分页查询数据库 $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 ); ?>

## ofmysqlis used here #limitimplements querying only a part of the data at a time, and the parametersoffsetandlimitare passed from the front end.

sql statement:

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

LIMIT $limit OFFSET $offsetmeans starting from the value of$offest, query$limitpieces 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:TriggerhandleCurrentChange()Function:

val=2, thenoffest = 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'

totalvalue is assigned tothis.total(it is bound to the totalattribute ofPagination, 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.

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:Yourlimitparameter must be consistent with thepage-size ofPaginationThe attributes are consistent, which means one page of data is queried at a time. Andoffsetis 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!