How to Implement PHP & MySQL Pagination for Large Result Sets?

PHP & MySQL Pagination: Managing Results in Incremental Pages

In modern web applications, it's essential to paginate large result sets into manageable pages for user navigation. In PHP and MySQL, achieving pagination involves a combination of SQL queries and PHP logic.

Let's assume we have a MySQL query that returns a list of all rows in a 'redirect' table based on a specific 'user_id'. Our goal is to display these results in pages containing 10 results each.

PHP Implementation

To paginate the results, we utilize the following PHP code:

<?php

// Establish MySQL connection

$perPage = 10; // Results per page
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1; // Current page
$startAt = $perPage * ($page - 1); // Starting record index

// Count total results
$query = "SELECT COUNT(*) as total FROM redirect WHERE user_id = '".$_SESSION['user_id']."'";
$r = mysql_fetch_assoc(mysql_query($query));
$totalPages = ceil($r['total'] / $perPage); // Calculate total pages

// Generate pagination links
$links = "";
for ($i = 1; $i <= $totalPages; $i++) {
  $links .= ($i != $page) ? "<a href='index.php?page=$i'>Page $i</a> " : "$page ";
}

// Fetch and display results
$query = "SELECT * FROM 'redirect'
WHERE 'user_id'= \''.$_SESSION['user_id'].' \' ORDER BY 'timestamp' LIMIT $startAt, $perPage";
$r = mysql_query($query);

// Display results here the way you want

echo $links; // Show links to other pages

Explanation

1. We determine the page number, defaulting to 1 if not specified in the URL.
2. We calculate the starting record index for the current page.
3. We count the total number of rows in the table to determine the total number of pages.
4. We generate pagination links for navigation.
5. We execute the query to fetch the results for the current page.
6. Finally, we display the results and the pagination links to the user.

By implementing this approach, you can effectively paginate large result sets from a MySQL query into multiple pages, providing a user-friendly navigation experience for your web application.

The above is the detailed content of How to Implement PHP & MySQL Pagination for Large Result Sets?. For more information, please follow other related articles on the PHP Chinese website!