How to retrieve and display data in paginated results using PDO in PHP?

To retrieve and display data in paginated results using PDO in PHP, you can follow these steps:

Set the number of results to be displayed per page:

$resultsPerPage = 10; // Define the number of results to be displayed per page

Get the current page number:

if (isset($_GET['page'])) {
    $currentPage = $_GET['page'];
} else {
    $currentPage = 1; // By default, set the current page to 1
}

Calculate the offset for the SQL query:

$offset = ($currentPage - 1) * $resultsPerPage;

Execute the SQL query with the LIMIT and OFFSET clauses:

$query = "SELECT * FROM your_table LIMIT :limit OFFSET :offset";
$statement = $pdo->prepare($query);
$statement->bindParam(':limit', $resultsPerPage, PDO::PARAM_INT);
$statement->bindParam(':offset', $offset, PDO::PARAM_INT);
$statement->execute();

Fetch the results and display them:

while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    // Display the results here
    echo $row['column_name'] . "<br>";
}

Calculate the total number of pages:

$queryCount = "SELECT COUNT(*) as total FROM your_table"; // Perform a separate query to count the total number of rows
$statementCount = $pdo->query($queryCount);
$totalResults = $statementCount->fetch()['total'];
$totalPages = ceil($totalResults / $resultsPerPage);

Create links for pagination:

for ($page = 1; $page <= $totalPages; $page++) {
    echo "<a href='your_page.php?page=$page'>$page</a> "; // Create links to each page
}

Make sure to replace 'your_table' with the actual name of your table and 'column_name' with the name of the column you want to display. Also, don't forget to handle any potential errors and validate user input.