How to Implement PHP & MySQL Pagination?

PHP & MySQL Pagination: A Comprehensive Guide

Pagination is a technique used to display large datasets in manageable portions. In PHP and MySQL, pagination can be implemented using a combination of SQL queries and PHP code.

To paginate results from the MySQL query you provided, you can follow these steps:

1. Determine the number of results per page: Let's assume you want to display 10 results per page. Store this value in a variable named $perPage.
2. Get the current page number: Use the $_GET['page'] parameter to get the current page number. If it's not set, default it to 1. Store this value in a variable named $page.
3. Calculate the starting limit: To determine the starting row for the current page, use the formula $startAt = $perPage * ($page - 1). This will calculate the row number to start fetching results from.
4. Get the total number of pages: Execute a MySQL query to count the total number of rows in the table. Divide this count by $perPage and round it up using ceil(). Store the result in a variable named $totalPages.
5. Generate pagination links: Create a loop from 1 to $totalPages. For each page, generate a link with the appropriate page number. Use the isset($_GET['page']) condition to highlight the current page.
6. Execute the pagination query: Modify your original MySQL query to add the LIMIT clause with the values $startAt and $perPage. This will fetch only the results for the current page.
7. Display the results and pagination links: Iterate over the results and display them as desired. Display the pagination links below or above the results to allow users to navigate through the pages.

Here's an example code that implements the pagination steps described above:

<?php
// MySQL connection code here

$perPage = 10;
$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$startAt = $perPage * ($page - 1);

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

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

$r = mysql_query($query);

$query = "SELECT * FROM 'redirect'
WHERE 'user_id'= \''.$_SESSION['user_id'].' \' 
ORDER BY 'timestamp' LIMIT $startAt, $perPage";

$r = mysql_query($query);

// Display results and pagination links here
echo $links; // Show links to other pages
?>

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