Create AJAX Pagination with PHP and MySQL

1. Request a page with Fetch

Keep the client contract small: send only the requested page number. URLSearchParams handles URL encoding, and the response is parsed as JSON.

async function loadPage(page) {
  const params = new URLSearchParams({ page: String(page) });
  const response = await fetch(`php_paging-ajaxck.php?${params}`);
  const payload = await response.json();

  if (!response.ok) {
    throw new Error(payload.error || 'Unable to load records');
  }

  renderRows(payload.data);
  nextButton.disabled = !payload.hasNext;
  previousButton.disabled = !payload.hasPrevious;
}

2. Validate the page on the server

Client-side values cannot be trusted. Accept only integers greater than or equal to 1. Invalid page values should return an HTTP 400 response instead of being used in SQL.

<?php
$pageRaw = $_GET['page'] ?? '1';

if (filter_var(
    $pageRaw,
    FILTER_VALIDATE_INT,
    ['options' => ['min_range' => 1]]
) === false) {
    http_response_code(400);
    echo json_encode(['error' => 'Invalid page number']);
    exit;
}

$page = (int) $pageRaw;
?>

3. Count rows and calculate the offset

Use COUNT(*) to obtain the total number of records. Avoid relying on rowCount() for SELECT queries because its behavior is driver-dependent.

$limit = 10;
$totalRecords = (int) $dbo->query('SELECT COUNT(*) FROM student')->fetchColumn();
$totalPages = max(1, (int) ceil($totalRecords / $limit));
$page = min($page, $totalPages);
$offset = ($page - 1) * $limit;

4. Bind LIMIT and OFFSET as integers

An explicit ORDER BY keeps paging stable, and integer binding keeps request-derived values out of the SQL string.

$stmt = $dbo->prepare(
    'SELECT id, name, class AS myclass, mark FROM student ORDER BY id LIMIT :limit OFFSET :offset'
);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

5. Return rows with paging metadata

echo json_encode([
  'data' => $rows,
  'page' => $page,
  'totalRecords' => $totalRecords,
  'totalPages' => $totalPages,
  'hasPrevious' => $page > 1,
  'hasNext' => $page < $totalPages,
]);

6. Render returned values as text

Database values should be placed into cells with textContent rather than concatenated into an HTML string. This keeps record data from being interpreted as markup.

rows.forEach((record) => {
  const td = document.createElement('td');
  td.textContent = String(record.name ?? '');
});

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: