How to implement pagination in PHP and MySQL for large datasets?

Pagination in PHP & MySQL: A Beginner's Guide

Pagination is essential for managing large datasets and enhancing user experience. This article addresses a common pagination scenario in PHP and MySQL.

The Query and Pagination Requirement

Consider the following MySQL query:

SELECT * FROM 'redirect'
WHERE 'user_id'= \''.$_SESSION['user_id'].' \' 
ORDER BY 'timestamp'

The goal is to paginate the results, displaying 10 results per page.

Pagination Implementation

The key to pagination is determining the starting point of the results to fetch for each page. Implement this with the following PHP code:

<code class="php">$perPage = 10;
$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$startAt = $perPage * ($page - 1);</code>

Counting Total Pages

To display pagination links, we need to calculate the total number of pages:

<code class="php">$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);</code>

Generating Pagination Links

Loop through the total number of pages to generate pagination links:

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

Fetching Results for Current Page

Finally, fetch the results for the current page:

<code class="php">$query = "SELECT * FROM 'redirect'
WHERE 'user_id'= \''.$_SESSION['user_id'].' \' 
ORDER BY 'timestamp' LIMIT $startAt, $perPage";
$result = mysql_query($query);</code>

Displaying Results

Display the results in the desired format and include the pagination links to navigate through the pages.

The above is the detailed content of How to implement pagination in PHP and MySQL for large datasets?. For more information, please follow other related articles on the PHP Chinese website!