如何在WordPress站点自定义API函数中实现分页功能

你需要修改现有代码，增加参数接收、分页计算、SQL语句调整三步即可实现需求：

1. 接收分页参数 #

首先在函数内获取接口请求传递的page（页码）和per_page（每页条目数）参数，设置默认值同时做合法性校验：

// 接收参数，默认第1页，每页15条
$page = isset($_REQUEST['page']) ? (int)$_REQUEST['page'] : 1;
$perPage = isset($_REQUEST['per_page']) ? (int)$_REQUEST['per_page'] : 15;
// 合法性修正，避免非法参数报错
$page = $page < 1 ? 1 : $page;
$perPage = $perPage < 1 ? 15 : $perPage;
// 计算MySQL查询偏移量
$offset = ($page - 1) * $perPage;

2. 修改SQL查询语句 #

把原来固定的LIMIT 100改成动态的偏移量+每页条数：

// 分页查询语句
$query = "SELECT * FROM coins LIMIT $offset, $perPage";

注：这里两个参数都已经强转为int类型，不存在SQL注入风险，不需要额外做转义处理。

3. （可选）补充分页元信息返回 #

建议在返回结果里增加总条目数、总页数等信息，方便安卓端做分页交互：

// 先查询总条目数
$countResult = mysqli_query($conn, "SELECT COUNT(*) as total FROM coins");
$total = mysqli_fetch_assoc($countResult)['total'];
$totalPage = ceil($total / $perPage);

// 最后返回的时候加上元信息
$json_arrd = [
    'data' => $json_arr,
    'meta' => [
        'current_page' => $page,
        'per_page' => $perPage,
        'total' => (int)$total,
        'total_page' => (int)$totalPage
    ]
];

修改后的完整代码 #

function get_all_coin()
{
    $servername = "";
    $username = "";
    $password = "";
    $dbname = "";

    // 接收分页参数
    $page = isset($_REQUEST['page']) ? (int)$_REQUEST['page'] : 1;
    $perPage = isset($_REQUEST['per_page']) ? (int)$_REQUEST['per_page'] : 15;
    $page = $page < 1 ? 1 : $page;
    $perPage = $perPage < 1 ? 15 : $perPage;
    $offset = ($page - 1) * $perPage;

    // 创建连接
    $conn = new mysqli($servername, $username, $password, $dbname);
    // 检查连接
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    // 查询总条目数
    $countResult = mysqli_query($conn, "SELECT COUNT(*) as total FROM coins");
    $total = mysqli_fetch_assoc($countResult)['total'];
    $totalPage = ceil($total / $perPage);

    // 分页查询数据
    $query = "SELECT * FROM coins LIMIT $offset, $perPage";
    $result = mysqli_query($conn, $query);

    $json_arr = [];
    if (mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_assoc($result)) {
            $json = [];
            $json['id'] = (int) $row['id'];
            $json['name'] = $row['name'];
            // ...其他字段
            $json_arr[] = $json;
        }
    }

    header('Content-type:application/json;charset=utf-8');
    $json_arrd = [
        'data' => $json_arr,
        'meta' => [
            'current_page' => $page,
            'per_page' => $perPage,
            'total' => (int)$total,
            'total_page' => (int)$totalPage
        ]
    ];
    return $json_arrd;
}

修改完成后安卓端直接通过请求链接携带参数即可，比如你的接口地址?page=2&per_page=20就能获取第2页、每页20条的数据。

内容的提问来源于stack exchange，提问作者Maduro