Project Student API Pagination

Pagination means dividing a large collection of data into smaller pages.

Page 1 → Students 1 - 10
Page 2 → Students 11 - 20
Page 3 → Students 21 - 30
Page 4 → Students 31 - 40

The mobile application requests only the page it currently needs.

• Reduces API response size.
• Reduces database workload.
• Uses less mobile data.
• Improves application performance.
• Allows smooth scrolling through large lists.
• Works well with React Native FlatList.

Two common pagination parameters are page and limit.

?page=1&limit=10

page tells the API which page to return.

limit tells the API how many records should be returned.

GET /api/students.php?page=2&limit=10

This request asks the API for the second page with a maximum of ten students.

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

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

This provides sensible default values when the client does not send pagination parameters.

The client should not be allowed to request an extremely large number of records.

$limit = min(
    $limit,
    50
);

Here, the API allows a maximum of 50 students per request.

MySQL uses LIMIT and OFFSET for pagination.

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

For example:

Page 1, Limit 10
Offset = (1 - 1) × 10
Offset = 0

Page 2, Limit 10
Offset = (2 - 1) × 10
Offset = 10

Page 3, Limit 10
Offset = (3 - 1) × 10
Offset = 20

SELECT id, name, email,
       mobile, course
FROM students
ORDER BY id DESC
LIMIT 10 OFFSET 20;

This query returns ten records after skipping the first twenty records.

$stmt = $pdo->prepare(
    "SELECT id, name, email,
            mobile, course, address
     FROM students
     ORDER BY id DESC
     LIMIT :limit
     OFFSET :offset"
);

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

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

$stmt->execute();

Integer values should be explicitly bound when using LIMIT and OFFSET.

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

The API now has only the records belonging to the requested page.

The application also needs to know how many students exist in total.

$countStmt = $pdo->query(
    "SELECT COUNT(*) FROM students"
);

$total = (int)
    $countStmt->fetchColumn();

$totalPages = (int)ceil(
    $total / $limit
);

For example, if there are 45 students and the limit is 10:

Total = 45
Limit = 10

Total Pages = 5

The API should return metadata along with the student data.

{
    "page": 2,
    "limit": 10,
    "total": 45,
    "total_pages": 5
}

React Native can use this information to decide whether another page is available.

{
    "success": true,
    "data": [
        {
            "id": 11,
            "name": "Rahul Kumar",
            "course": "React Native"
        }
    ],
    "meta": {
        "page": 2,
        "limit": 10,
        "total": 45,
        "total_pages": 5
    }
}

Search and pagination can work together.

GET /api/students.php
    ?search=rahul
    &page=1
    &limit=10

The API first applies the search condition and then returns the requested page.

When search is active, the total count must use the same search condition.

$keyword = "%{$search}%";

$countStmt = $pdo->prepare(
    "SELECT COUNT(*)
     FROM students
     WHERE name LIKE ?
        OR email LIKE ?
        OR mobile LIKE ?
        OR course LIKE ?"
);

$countStmt->execute([
    $keyword,
    $keyword,
    $keyword,
    $keyword
]);

$total = (int)
    $countStmt->fetchColumn();

<?php

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

require_once '../config/database.php';
require_once __DIR__ .
    '/vendor/autoload.php';

use Firebase\JWT\JWT;
use Firebase\JWT\Key;

$secretKey =
    'CHANGE_THIS_TO_A_LONG_RANDOM_SECRET';

if ($_SERVER['REQUEST_METHOD'] !== 'GET') {

    http_response_code(405);

    echo json_encode([
        "success" => false,
        "message" => "Method not allowed"
    ]);

    exit;
}

$headers = getallheaders();

$authorization =
    $headers['Authorization']
    ?? '';

if (
    !preg_match(
        '/Bearer\s(\S+)/',
        $authorization,
        $matches
    )
) {

    http_response_code(401);

    echo json_encode([
        "success" => false,
        "message" =>
            "Authentication required"
    ]);

    exit;
}

$token = $matches[1];

try {

    JWT::decode(
        $token,
        new Key(
            $secretKey,
            'HS256'
        )
    );

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

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

    $limit = min(
        $limit,
        50
    );

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

    $search = trim(
        $_GET['search'] ?? ''
    );

    if ($search !== '') {

        $keyword = "%{$search}%";

        $countStmt = $pdo->prepare(
            "SELECT COUNT(*)
             FROM students
             WHERE name LIKE ?
                OR email LIKE ?
                OR mobile LIKE ?
                OR course LIKE ?"
        );

        $countStmt->execute([
            $keyword,
            $keyword,
            $keyword,
            $keyword
        ]);

        $total = (int)
            $countStmt->fetchColumn();

        $stmt = $pdo->prepare(
            "SELECT id, name, email,
                    mobile, course, address
             FROM students
             WHERE name LIKE ?
                OR email LIKE ?
                OR mobile LIKE ?
                OR course LIKE ?
             ORDER BY id DESC
             LIMIT :limit
             OFFSET :offset"
        );

        $stmt->bindValue(
            1,
            $keyword,
            PDO::PARAM_STR
        );

        $stmt->bindValue(
            2,
            $keyword,
            PDO::PARAM_STR
        );

        $stmt->bindValue(
            3,
            $keyword,
            PDO::PARAM_STR
        );

        $stmt->bindValue(
            4,
            $keyword,
            PDO::PARAM_STR
        );

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

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

        $stmt->execute();

    } else {

        $countStmt = $pdo->query(
            "SELECT COUNT(*)
             FROM students"
        );

        $total = (int)
            $countStmt->fetchColumn();

        $stmt = $pdo->prepare(
            "SELECT id, name, email,
                    mobile, course, address
             FROM students
             ORDER BY id DESC
             LIMIT :limit
             OFFSET :offset"
        );

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

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

        $stmt->execute();
    }

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

    $totalPages = (int)ceil(
        $total / $limit
    );

    http_response_code(200);

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

} catch (Exception $e) {

    error_log($e->getMessage());

    http_response_code(401);

    echo json_encode([
        "success" => false,
        "message" =>
            "Invalid or expired token"
    ]);
}

interface Student {
    id: number;
    name: string;
    email: string;
    mobile: string;
    course: string;
    address: string;
}

interface PaginationMeta {
    page: number;
    limit: number;
    total: number;
    total_pages: number;
}

interface StudentResponse {
    success: boolean;
    data: Student[];
    meta: PaginationMeta;
}

const [students, setStudents] =
    useState<Student[]>([]);

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

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

const [loadingMore, setLoadingMore] =
    useState(false);

const [hasMore, setHasMore] =
    useState(true);

const loadStudents =
    async () => {

    try {

        setLoading(true);

        const response =
            await api.get<StudentResponse>(
                "/students.php",
                {
                    params: {
                        page: 1,
                        limit: 10
                    }
                }
            );

        setStudents(
            response.data.data
        );

        setPage(1);

        setHasMore(
            response.data.meta.page
            < response.data.meta.total_pages
        );

    } finally {

        setLoading(false);

    }
};

const loadMore =
    async () => {

    if (
        loadingMore ||
        !hasMore
    ) {
        return;
    }

    const nextPage =
        page + 1;

    try {

        setLoadingMore(true);

        const response =
            await api.get<StudentResponse>(
                "/students.php",
                {
                    params: {
                        page: nextPage,
                        limit: 10
                    }
                }
            );

        setStudents(
            previous => [
                ...previous,
                ...response.data.data
            ]
        );

        setPage(nextPage);

        setHasMore(
            nextPage
            < response.data.meta.total_pages
        );

    } finally {

        setLoadingMore(false);

    }
};

<FlatList
    data={students}

    keyExtractor={item =>
        item.id.toString()
    }

    renderItem={({ item }) => (
        <StudentCard
            student={item}
        />
    )}

    onEndReached={loadMore}

    onEndReachedThreshold={0.5}
/>

onEndReached can load the next page when the user gets near the end of the list.

const renderFooter = () => {

    if (!loadingMore) {
        return null;
    }

    return (
        <ActivityIndicator />
    );
};

The footer can show a loading indicator while the next page is being downloaded.

const response =
    await api.get<StudentResponse>(
        "/students.php",
        {
            params: {
                search: search,
                page: page,
                limit: 10
            }
        }
    );

When the search keyword changes, the application should normally reset the page to 1.

search = "rahul"
page = 1
limit = 10

const searchStudents =
    async (keyword: string) => {

    setPage(1);
    setHasMore(true);

    const response =
        await api.get<StudentResponse>(
            "/students.php",
            {
                params: {
                    search: keyword,
                    page: 1,
                    limit: 10
                }
            }
        );

    setStudents(
        response.data.data
    );

    setHasMore(
        response.data.meta.page
        < response.data.meta.total_pages
    );
};

This prevents the application from starting a new search from an old page number.

Pagination can also work with sorting.

GET /api/students.php
    ?page=1
    &limit=10
    &sort=name
    &order=asc

When dynamic sorting is implemented, column names and sort directions should be checked against an allowlist before being used in SQL.

Method: GET

https://example.com/api/students.php?page=2&limit=10

Header:

Authorization: Bearer YOUR_JWT_TOKEN

Example Response:

{
    "success": true,
    "data": [],
    "meta": {
        "page": 2,
        "limit": 10,
        "total": 45,
        "total_pages": 5
    }
}

• Always provide a default page number.
• Always provide a reasonable default limit.
• Set a maximum allowed limit.
• Calculate the offset on the server.
• Use integer binding for LIMIT and OFFSET.
• Return total and total_pages metadata.
• Use pagination with search and filtering.
• Prevent duplicate load-more requests.
• Show loading feedback to the user.
• Use FlatList for large React Native lists.

React Native FlatList
        ↓
Page 1 Request
        ↓
Axios GET
        ↓
JWT Authorization
        ↓
PHP REST API
        ↓
Validate page/limit
        ↓
Calculate OFFSET
        ↓
MySQL LIMIT/OFFSET
        ↓
Return Students + Metadata
        ↓
Display Page 1
        ↓
User Scrolls
        ↓
Page 2 Request
        ↓
Append New Students
        ↓
Continue Until Last Page

Pagination completes another important part of our Student Management API.

• The API accepts page and limit.
• Offset is calculated using (page - 1) * limit.
• MySQL uses LIMIT and OFFSET.
• PDO binds pagination values as integers.
• The API returns only the requested records.
• Total records and total pages are returned as metadata.
• Search can be combined with pagination.
• Sorting can also be combined with pagination.
• React Native stores the current page in state.
• FlatList can load more records using onEndReached.
• Duplicate pagination requests should be prevented.
• The next lesson will create the Student Profile API.