Pagination, Sorting, and Searching

# CHAPTER 12

Pagination, Sorting, and Searching #

1. Introduction #

2. Learning Objectives #

• Implement offset-based pagination in an API.

• Use LIMIT and OFFSET in MySQL queries.

• Accept sorting parameters (e.g., sort_by and order) securely.

• Implement search parameters using SQL LIKE.

• Structure a JSON response to include pagination metadata.

3. Beginner-Friendly Explanation #

• Pagination: Instead of reading a whole encyclopedia at once, you read one page at a time. The client says, "Give me page 2, and I only want 10 items per page."

• Sorting: The client says, "Give me the items, but organize them alphabetically by name."

• Searching: The client says, "I don't know the exact name, but give me any item that has the word 'blue' in the title."

All three of these concepts rely on the Query Parameters we learned in Chapter 11!

4. Real-World Examples #

• Google Search: When you click "Page 2" on Google, look at the URL. You will see something like &start=10. This is pagination.

• Shopify API: To get the next batch of orders, Shopify uses limit parameters: /orders?limit=50&page=2.

5. Detailed Code Examples #

<?php
// 1. Get Parameters with safe defaults
$page = isset($_GET[&#039;page']) ? (int)$_GET['page'] : 1;
$limit = isset($_GET[&#039;limit']) ? (int)$_GET['limit'] : 10;
$search = $_GET[&#039;search'] ?? '';
$sortBy = $_GET[&#039;sort_by'] ?? 'created_at';
$order = isset($_GET[&#039;order']) && strtoupper($_GET['order']) === 'ASC' ? 'ASC' : 'DESC';

// 2. Math for Pagination
$offset = ($page - 1) * $limit;

// 3. Security check for Sort column (CRITICAL to prevent SQL Injection)
$allowedSortColumns = [&#039;id', 'name', 'price', 'created_at'];
if (!in_array($sortBy, $allowedSortColumns)) {
    $sortBy = &#039;created_at';
}

// 4. Build Query
$sql = "SELECT * FROM products WHERE name LIKE :search ORDER BY $sortBy $order LIMIT :limit OFFSET :offset";

// 5. Execute
$stmt = $pdo->prepare($sql);
$stmt->bindValue(&#039;:search', '%' . $search . '%', PDO::PARAM_STR);
$stmt->bindValue(&#039;:limit', $limit, PDO::PARAM_INT);
$stmt->bindValue(&#039;:offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo json_encode($data);
?>

6. Request/Response Examples #

Request:

GET /products?search=laptop&page=2&limit=5&sort_by=price&order=ASC

Response:

{
  "meta": {
    "current_page": 2,
    "per_page": 5,
    "total_records": 12,
    "total_pages": 3,
    "has_next_page": true
  },
  "data": [
    {"id": 45, "name": "Budget Laptop", "price": 499.99},
    {"id": 88, "name": "Student Laptop", "price": 549.99}
  ]
}

7. HTTP Examples #

• /users?page=1&limit=20 (Just pagination)

• /users?sortby=lastname&order=ASC (Just sorting)

• /users?search=john&sortby=age&order=DESC&page=1 (All three)

// PHP logic to get the total count
$countSql = "SELECT COUNT(*) FROM products WHERE name LIKE :search";
// ... execute countSql ...
$totalPages = ceil($totalRecords / $limit);

9. Best Practices #

• Max Limits: Never trust the client. If a client sends ?limit=1000000, your server will crash. Always hardcode a maximum allowed limit (e.g., if $limit > 100, $limit = 100).

• Whitelist Sort Columns: Never put $GET['sortby'] directly into the ORDER BY clause. PDO Prepared Statements do not protect table or column names! You MUST check the input against an array of allowed column names, as shown in the code example.

• Cursor Pagination: For massive datasets (like Twitter feeds), offset-based pagination (OFFSET 100000) becomes very slow. Advanced APIs use Cursor-based pagination (e.g., ?afterid=983).

• SQL Injection via ORDER BY: This is the most common vulnerability in beginner APIs. Since you can't use :bindings for column names in PDO, beginners concatenate the user input directly into the SQL string. A hacker can pass ?sortby=(SELECT password FROM users) and break into the system.

• 0-Indexed Pages: Usually, APIs treat Page 1 as the first page. If you treat Page 0 as the first page, it confuses frontend developers.

11. Mini Exercises #

1. 1. If you are on page=4 and your limit=10, what is the math formula to calculate the SQL OFFSET?

12. Coding Challenges #

13. MCQs with Answers #

14. Interview Questions #

• Q: Explain how you protect your database from SQL injection when dynamically sorting columns.

• Q: What is the performance problem with Offset-based pagination on tables with millions of rows, and what is the alternative?

• Q: Why is it important to return pagination metadata (like total_pages) in the JSON response?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #