Read MySQL Records with PHP (Paginated Table Display) (en)

-- 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;

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

<?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);
?>

<?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);
?>

<?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>';
}
?>