How to Implement PHP & MySQL Pagination with a 10-Record Limit per Page?

PHP & MySQL Pagination: Displaying Records in Pages

Pagination is a crucial technique in web development for managing large datasets and enhancing user experience. In this article, we'll explore how to paginate results in PHP and MySQL, setting a limit of 10 results per page.

Creating the MySQL Query

The MySQL query for fetching records should include the appropriate LIMIT clause. For example:

SELECT *
FROM 'redirect'
WHERE 'user_id' = \''.$_SESSION['user_id'].' \'
ORDER BY 'timestamp'
LIMIT 0, 10;

PHP Code for Pagination

To implement pagination in PHP, you need to determine the current page, calculate the start index for the current page, fetch the total number of records in the database, and create navigation links.

<?php
// Connect to your MySQL database

// Set the number of results per page
$perPage = 10;

// Get the current page number (if not set, assume page 1)
$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;

// Calculate the start index for the current page
$startAt = $perPage * ($page - 1);

// Get the total number of records
$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);

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

// Get the records for the current page
$query = "SELECT * FROM 'redirect'
WHERE 'user_id' = \''.$_SESSION['user_id'].' \'
ORDER BY 'timestamp' LIMIT $startAt, $perPage";
?>

Displaying Records and Pagination Links

Once you have the results, you can display them in your interface. Additionally, you can echo the $links variable to show the navigation links for different pages.

The above is the detailed content of How to Implement PHP & MySQL Pagination with a 10-Record Limit per Page?. For more information, please follow other related articles on the PHP Chinese website!