How to Paginate MySQL Queries with PHP to Display 10 Results Per Page?

PHP & MySQL Pagination

Pagination is crucial when working with large data sets as it allows the user to load only a portion of the relevant data , thereby reducing server load and improving application response speed. This Q&A will guide you on how to use paginated queries with PHP and MySQL.

Question:

How to paginate a MySQL query to display 10 results per page?

Answer:

<?php // 数据库连接代码在此处省略

$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 "
            : "$page ";
}

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

$r = mysql_query($query);

// 在此处显示结果

echo $links; // 显示其他页面的链接

Explanation:

1. Set paging variables: $perPage specifies the number of results per page, $page Get the current page number (defaults to 1 if not specified).
2. Calculate SQL offset: $startAt calculates the offset required for the SKIP clause in the query.
3. Get the total number of pages of results: Execute a separate query to get the total number of results in the dataset, and then calculate the total number of pages.
4. Generate paginated links: Use a loop to generate links to other pages and highlight the current page.
5. Perform a paginated query: Add OFFSET and LIMIT clauses to the main query to limit the query results.
6. Show results: Show results in a custom page with pagination links at the bottom of the page.

The above is the detailed content of How to Paginate MySQL Queries with PHP to Display 10 Results Per Page?. For more information, please follow other related articles on the PHP Chinese website!