API Pagination

Pagination means dividing a large collection of records into multiple smaller pages.

1000 Records
     ↓
Page 1 → 20 Records
Page 2 → 20 Records
Page 3 → 20 Records
...
Page 50 → 20 Records

Suppose a student database contains 10,000 students. Returning all 10,000 students in a single API response can consume unnecessary network bandwidth and memory.

Pagination allows the application to request only a small number of students at a time.

A common pagination API uses a page query parameter.

GET /api/students.php?page=1

The value 1 means that the client wants the first page.

The limit parameter defines how many records should be returned per page.

GET /api/students.php?page=1&limit=20

This requests page 1 with up to 20 records.

MySQL uses LIMIT and OFFSET for a common pagination technique.

SELECT *
FROM students
LIMIT 20 OFFSET 0;

This returns the first 20 records.

LIMIT tells MySQL how many records to return.

LIMIT 10

This means that at most 10 records should be returned.

SELECT *
FROM students
LIMIT 10;

OFFSET tells MySQL how many records should be skipped before returning records.

LIMIT 10 OFFSET 20

This skips the first 20 records and then returns the next 10 records.

Suppose each page contains 10 records.

Page = 1
Limit = 10

Offset =
(page - 1) × limit

Offset =
(1 - 1) × 10

Offset = 0

Therefore:

LIMIT 10 OFFSET 0

Page = 2
Limit = 10

Offset =
(2 - 1) × 10

Offset = 10

Therefore:

LIMIT 10 OFFSET 10

The API skips the first 10 records and returns the next 10.

Page = 3
Limit = 10

Offset =
(3 - 1) × 10

Offset = 20

Therefore:

LIMIT 10 OFFSET 20

The basic pagination formula is:

offset = (page - 1) × limit

This formula converts a page number into the database offset.

$page = (int)(
    $_GET['page'] ?? 1
);

$limit = (int)(
    $_GET['limit'] ?? 10
);

Casting the values to integers is useful when working with numeric pagination parameters.

A page number should not be less than 1.

Page 0  → Page 1
Page -1 → Page 1
Page 1  → Page 1
Page 2  → Page 2

It is a good idea to restrict the maximum number of records that a client can request.

$limit = max(
    1,
    min($limit, 100)
);

Here the API allows between 1 and 100 records per page.

$offset =
    ($page - 1) * $limit;

For example:

Page 1, Limit 20
Offset = 0

Page 2, Limit 20
Offset = 20

Page 3, Limit 20
Offset = 40

<?php

header(
    "Content-Type: application/json"
);

require_once '../db.php';

$page = (int)(
    $_GET['page'] ?? 1
);

$limit = (int)(
    $_GET['limit'] ?? 10
);

$page = max(
    1,
    $page
);

$limit = max(
    1,
    min($limit, 100)
);

$offset =
    ($page - 1) * $limit;

try {

    $sql = "
        SELECT
            id,
            student_id,
            name,
            course,
            status
        FROM students
        ORDER BY id DESC
        LIMIT ? OFFSET ?
    ";

    $stmt = $pdo->prepare($sql);

    $stmt->bindValue(
        1,
        $limit,
        PDO::PARAM_INT
    );

    $stmt->bindValue(
        2,
        $offset,
        PDO::PARAM_INT
    );

    $stmt->execute();

    $students =
        $stmt->fetchAll(
            PDO::FETCH_ASSOC
        );

    echo json_encode([
        "success" => true,
        "page" => $page,
        "limit" => $limit,
        "data" => $students
    ]);

} catch (PDOException $e) {

    http_response_code(500);

    echo json_encode([
        "success" => false,
        "message" =>
            "Server error"
    ]);
}

?>

When using PDO with MySQL, explicitly binding pagination values as integers makes their intended type clear.

$stmt->bindValue(
    1,
    $limit,
    PDO::PARAM_INT
);

$stmt->bindValue(
    2,
    $offset,
    PDO::PARAM_INT
);

This is especially useful for LIMIT and OFFSET values.

A useful API response can include information about the current page.

{
    "success": true,
    "page": 2,
    "limit": 20,
    "data": []
}

The mobile application can use this information to manage pagination.

The API may also return the total number of records.

SELECT COUNT(*)
FROM students;

Using the total count, the application can calculate how many pages are available.

The total number of pages can be calculated using:

$totalPages = ceil(
    $totalRecords / $limit
);

For example:

Total Records = 95
Limit = 10

Total Pages =
ceil(95 / 10)

Total Pages = 10

{
    "success": true,
    "page": 2,
    "limit": 10,
    "total_records": 95,
    "total_pages": 10,
    "data": [
        {
            "id": 20,
            "name": "Rahul"
        }
    ]
}

This response gives the mobile application enough information to display pagination controls.

const page = 1;
const limit = 20;

const response = await fetch(
    "https://example.com/api/students.php"
    + "?page=" + page
    + "&limit=" + limit
);

const result =
    await response.json();

console.log(result.data);

const [page, setPage] =
    useState(1);

const [students, setStudents] =
    useState([]);

const [loading, setLoading] =
    useState(false);

The page state can be changed whenever the user requests another page.

A common mobile application pattern is to load another page when the user reaches the end of the current list.

const loadNextPage = () => {

    setPage(
        previousPage =>
            previousPage + 1
    );

};

The application can then request the next page from the API.

React Native FlatList provides onEndReached, which can be used to load additional pages.

<FlatList
    data={students}
    keyExtractor={(item) =>
        item.id.toString()
    }
    renderItem={({ item }) => (
        <Text>
            {item.name}
        </Text>
    )}
    onEndReached={loadNextPage}
    onEndReachedThreshold={0.5}
/>

When using infinite scrolling, the application should prevent multiple requests from being sent at the same time.

if (loading) {
    return;
}

setLoading(true);

After the request finishes:

setLoading(false);

Pagination should normally be combined with a consistent sort order.

SELECT *
FROM students
ORDER BY id DESC
LIMIT ? OFFSET ?;

A stable ordering helps prevent unexpected changes between pages.

Filtering and pagination can also be combined.

GET /api/students.php
?status=active
&page=1
&limit=20

The database query can apply the filter before returning the requested page.

SELECT *
FROM students
WHERE status = ?
ORDER BY id DESC
LIMIT ? OFFSET ?;

Method: GET

http://localhost/api/students.php
?page=1&limit=10

Test page 2:

http://localhost/api/students.php
?page=2&limit=10

Test page 3:

http://localhost/api/students.php
?page=3&limit=10

Compare the returned records to understand how pagination works.

API pagination divides a large collection into smaller pages. PHP receives the page and limit values, calculates the offset, and uses MySQL LIMIT and OFFSET to retrieve the required records. React Native can then display the data page by page or use infinite scrolling.

Page 1
 ↓
LIMIT 20 OFFSET 0

Page 2
 ↓
LIMIT 20 OFFSET 20

Page 3
 ↓
LIMIT 20 OFFSET 40

Pagination becomes especially useful when combined with search, filtering, sorting, and a React Native FlatList.