---
title: "DB-Read MySQL Records with PHP (Paginated Table Display) (en)"
description: "This guide shows how to read every record from a MySQL table with PHP and display them in a paginated table, so the page isn't slowed down by loading too many rows at once."
url: "https://aaronchan.com/kb/php-read-mysql-paginated-table/"
updated: "2026-09-18"
category: "Code-php+MySQL (en)"
---

# DB-Read MySQL Records with PHP (Paginated Table Display) (en)

## 🧩 Concept & Core Syntax

When a table holds thousands of rows, loading them all at once slows the page down and hurts readability. Pagination uses MySQL's `LIMIT` clause to fetch only a set number of rows each time.

### LIMIT Syntax (two forms, same result)

 Copy

```
-- Syntax 1: offset comes first
SELECT * FROM table_name LIMIT offset, row_count;

-- Syntax 2: using the OFFSET keyword (more readable)
SELECT * FROM table_name LIMIT row_count OFFSET offset;
```

| Parameter | Description |
| --- | --- |
| `row_count` | how many rows to show per page |
| `offset` | how many rows to skip before starting (counts from 0; `OFFSET 0` is the first row) |

### Pagination Math (three variables)

 Copy

```
$per_page = 10;                                   // rows per page
$page     = isset($_GET['page']) ? (int)$_GET['page'] : 1;  // current page
$offset   = ($page - 1) * $per_page;              // rows to skip
```

✅ Page 1 → `offset = 0`; page 2 → `offset = 10`; page 3 → `offset = 20`

---

## 📊 Full Example (procedural mysqli)

Here is a complete, runnable example that uses `mysqli` to read the `students` table and display it as a paginated table:

 Copy

```
<?php
// 1. Connect to the database
$conn = mysqli_connect('localhost', 'root', '', 'pagination');
if (!$conn) {
    die('Connection failed: ' . mysqli_connect_error());
}
mysqli_set_charset($conn, 'utf8mb4');   // support Chinese, avoid mojibake

// 2. Pagination variables
$per_page = 5;                                        // rows per page
$page     = max(1, (int)($_GET['page'] ?? 1));        // current page (min 1)
$offset   = ($page - 1) * $per_page;                  // rows to skip

// 3. Get total records, then compute total pages
$total_result = mysqli_query($conn, 'SELECT COUNT(*) FROM students');
$total_records = mysqli_fetch_row($total_result)[0];
$total_pages = (int) ceil($total_records / $per_page);  // ceil rounds up

// 4. Fetch current-page records using LIMIT + OFFSET
$sql = "SELECT * FROM students ORDER BY id LIMIT $offset, $per_page";
$result = mysqli_query($conn, $sql);

// 5. Render the table
echo '<table border="1">';
echo '<tr><th>ID</th><th>Name</th><th>Age</th></tr>';
while ($row = mysqli_fetch_assoc($result)) {
    echo '<tr>';
    echo '<td>' . htmlspecialchars($row['id']) . '</td>';
    echo '<td>' . htmlspecialchars($row['name']) . '</td>';
    echo '<td>' . htmlspecialchars($row['age']) . '</td>';
    echo '</tr>';
}
echo '</table>';

// 6. Pagination navigation links
for ($i = 1; $i <= $total_pages; $i++) {
    if ($i == $page) {
        echo "<strong>$i</strong> ";          // bold the current page
    } else {
        echo "<a href='?page=$i'>$i</a> ";
    }
}

mysqli_close($conn);
?>
```

---

## 🔒 Security (Prepared Statement)

Concatenating `$offset` and `$per_page` straight into SQL carries a SQL injection risk. Binding integer parameters with a prepared statement is the correct approach:

 Copy

```
<?php
$conn = mysqli_connect('localhost', 'root', '', 'pagination');

$per_page = 5;
$page     = max(1, (int)($_GET['page'] ?? 1));
$offset   = ($page - 1) * $per_page;

// use ? placeholders, then bind integer parameters
$stmt = mysqli_prepare($conn, 'SELECT id, name, email FROM users ORDER BY id LIMIT ? OFFSET ?');
mysqli_stmt_bind_param($stmt, 'ii', $per_page, $offset);   // 'ii' = two integers
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

while ($row = mysqli_fetch_assoc($result)) {
    echo htmlspecialchars($row['name']) . '<br>';
}
mysqli_close($conn);
?>
```

### PDO Version (recommended)

 Copy

```
<?php
$pdo = new PDO('mysql:host=localhost;dbname=demo', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$per_page = 20;
$page     = max(1, (int)($_GET['page'] ?? 1));
$offset   = ($page - 1) * $per_page;

// total record count
$total_records = (int) $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();
$total_pages   = (int) ceil($total_records / $per_page);

// fetch the current page
$stmt = $pdo->prepare('SELECT id, name, email FROM users ORDER BY id LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', $per_page, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($rows as $row) {
    echo htmlspecialchars($row['name']) . '<br>';
}
?>
```

---

## ⚠️ Notes

| Note | Explanation |
| --- | --- |
| Always add `ORDER BY` | Without `ORDER BY`, MySQL can return rows in any order and pagination becomes unstable. Always sort by a unique column (like `id`). |
| Two queries | Pagination needs two queries: one `COUNT(*)` to get the total, and one `LIMIT/OFFSET` to fetch the current page. |
| offset starts at 0 | A common off-by-one mistake: page 1 needs `OFFSET 0`, so the formula is `($page - 1) * $per_page`. |
| Deep pagination is slow | `LIMIT 20 OFFSET 100000` makes MySQL scan the first 100,000 rows. For large tables, use keyset pagination (`WHERE id > :lastId`). |
| Chinese mojibake | Call `mysqli_set_charset($conn, 'utf8mb4')` or Chinese characters turn into gibberish. |
| Escape output | Use `htmlspecialchars()` before displaying to prevent XSS (cross-site scripting). |

---

## 🛠️ Quick Comparison

| Item | mysqli (procedural) | PDO |
| --- | --- | --- |
| Connection | `mysqli_connect()` | `new PDO('mysql:host=...;dbname=...')` |
| Fetch results | `mysqli_fetch_assoc()` | `fetchAll(PDO::FETCH_ASSOC)` |
| Bind parameters | `mysqli_stmt_bind_param()` | `bindValue()` |
| Multiple databases | MySQL only | many databases |
| Recommendation | fair | ✅ recommended (safer, easier to maintain) |

---

## 🔗 References

| Source | Link |
| --- | --- |
| W3docs — MySQL LIMIT | [w3docs.com](https://www.w3docs.com/learn-php/mysql-limit-data) |
| Tutorial Republic — LIMIT | [tutorialrepublic.com](https://www.tutorialrepublic.com/php-tutorial/php-mysql-limit-clause.php) |
| CodeShack — PHP Pagination | [codeshack.io](https://codeshack.io/how-to-create-pagination-php-mysql) |
| Mike Lopez — pagination | [mikelopez.com](https://mikelopez.com/blog/1118/) |
| Campcodes — custom pagination | [campcodes.com](https://www.campcodes.com/blog/php-pagination-tutorial-build-custom-pagination-with-mysql-2026/) |