Bootstrap Table 后端分页

之前一直用的前端分页，在数据不是太多的情况下，前端分页还是很方便的，也可以使用自带的搜索等功能，如果数据量比较多的情况就要使用后端分页。

实现的原理：前端把offset和limit(每一页显示的数量)发送给后端，后端查询数据库返回JSON，前端根据后端返回的total展示出pagination。

返回的JSON格式

{
    "total": 25,
    "rows": [
    {},
    {},
    ...
    ]
}

前端主要设置

sidePagination: "server",
pageNumber: 1,
queryParams:queryParams,

queryParams是一个函数，

	function queryParams(params) {
	  console.log(JSON.stringify(params)) // {"order":"asc","offset":0,"limit":10}
		return {
			offset:params.offset,
			limit:params.limit
		}
	}

完整的代码

<table class="table-striped table-hasthead" id="tableTest1">
    <thead>
        <tr>
            <th data-sortable="true" data-field="id">Id</th>
            <th data-field="name">Name</th>
            <th data-sortable="true" data-field="url">Website</th>
            <th data-field="alex">Texa</th>
            <th data-field="country">Country</th>
        </tr>
    </thead>
</table>

    $(function() {
        var url = "selectBtTable.php?action=init_data_list";
        $('#tableTest1').bootstrapTable({
            height: $(window).height() - 460,
            url: url,
            method: 'POST', //默认是GET方式请求
            contentType: "application/x-www-form-urlencoded",
            search: true,
            pagination: true,
            sidePagination: "server",
            pageNumber: 1,
            queryParams: queryParams,
        });
    });

使用GET请求会把参数放在URL中发送给后端
selectBtTable.php?action=init_data_list&offset=0&limit=10

如果是POST请求一定要加上contentType: "application/x-www-form-urlencoded"

后端PHP 简单代码

<?php
$action = $_GET['action'];
//var_dump($action);
switch ($action) {
    case 'init_data_list':
        init_data_list();
        break;

    case 'data_list':
        data_list();
        break;

    case 'del_row':
        del_row();
        break;

    case 'edit_row':
        edit_row();
        break;
}

function init_data_list()
{
	$offset = $_POST['offset'];
	//$offset = intval($offset); 可以不用转换
	$limit = $_POST['limit'];
	//var_dump($offset);
    //$sql = "SELECT * FROM website"; select * from clerk limit {$sql},{$pagesize}
	$sql = "SELECT * FROM website limit {$offset},{$limit}";
	// SELECT * FROM products LIMIT 0,8;
    // SELECT * FROM products LIMIT 8 OFFSET 0; (在mysql 5以后支持这种写法)
    $query = query_sql($sql);
    while ($row = $query->fetch_assoc()) {
        $data[] = $row;
    }

	$sql = "SELECT * FROM website";
	$query=query_sql($sql);
	$total=mysqli_num_rows($query);
	$result["total"] = $total; // count($data);
	$result["rows"] = $data;
	//$result["rows"] = array_slice($data, $offset, $limit);
    echo json_encode($result);
}

function query_sql()
{
    $mysqli = new mysqli("127.0.0.1", "root", "123", "demodb");
    $sqls = func_get_args();
    foreach ($sqls as $s) {
        $query = $mysqli->query($s);
    }
    $mysqli->close();
    return $query;
}
?>

tianyawhl

Bootstrap Table 后端分页