php 使用 MySQL LIMIT、OFFSET 进行分页
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20364349/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pagination using MySQL LIMIT, OFFSET
提问by ValleyDigital
I have some code that LIMITs data to display only 4 items per page. The column I'm using has about 20-30 items, so I need to make those spread out across the pages.
我有一些代码限制数据每页只显示 4 个项目。我使用的列有大约 20-30 个项目，所以我需要将它们分布在页面上。
On the first page, I have:
在第一页，我有：
$result = mysqli_query($con,"SELECT * FROM menuitem LIMIT 4");
{
echo "<tr>";
echo "<td align='center'><img src=\"" . $row['picturepath'] . "\" /></td>";
echo "<td align='center'>" . $row['name'] . "</td> <td align='center'> <input type='button' value='More Info'; onclick=\"window.location='more_info.php?';\"> </td>";
echo "<td align='center'>" . $row['price'] . "</td> <td align='center'> <input type='button' value='Add to Order' onclick=''> </td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
<table width="1024" align="center" >
<tr height="50"></tr>
<tr>
<td width="80%" align="right">
<a href="itempage2.php">NEXT</a>
</td>
<td width="20%" align="right">
<a href="">MAIN MENU</a>
</td>
</tr>
</table>
You'll notice towards the bottom of the page my anchor tag within lists the second page, "itempage2.php". In item page 2, I have the same code, except my select statement lists the offset of 4.
您会注意到在页面底部，我的锚标签列出了第二个页面“itempage2.php”。在项目第 2 页中，我有相同的代码，除了我的 select 语句列出了 4 的偏移量。
$result = mysqli_query($con,"SELECT * FROM menuitem LIMIT 4 offset 4");
This works, this way when there is a pre-determined number of items within my database. But it's not that good. I need to create a new page only if there are more items, not hard-coded into it like it is now.
当我的数据库中有预定数量的项目时，这是有效的。但它不是那么好。仅当有更多项目时，我才需要创建一个新页面，而不是像现在这样硬编码到其中。
How can I create multiple pages without having to hard-code each new page, and offset?
如何创建多个页面而不必对每个新页面进行硬编码和偏移？
回答by Mike Brant
First off, don't have a separate server script for each page, that is just madness. Most applications implement pagination via use of a pagination parameter in the URL. Something like:
首先，不要为每个页面设置单独的服务器脚本，这太疯狂了。大多数应用程序通过在 URL 中使用分页参数来实现分页。就像是：
http://yoursite.com/itempage.php?page=2
You can access the requested page number via $_GET['page'].
您可以通过 访问请求的页码$_GET['page']。
This makes your SQL formulation really easy:
这使您的 SQL 公式变得非常简单：
// determine page number from $_GET
$page = 1;
if(!empty($_GET['page'])) {
$page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
if(false === $page) {
$page = 1;
}
}
// set the number of items to display per page
$items_per_page = 4;
// build query
$offset = ($page - 1) * $items_per_page;
$sql = "SELECT * FROM menuitem LIMIT " . $offset . "," . $items_per_page;
So for example if input here was page=2, with 4 rows per page, your query would be"
因此，例如，如果此处的输入为page=2，每页有 4 行，则您的查询将是“
SELECT * FROM menuitem LIMIT 4,4
So that is the basic problem of pagination. Now, you have the added requirement that you want to understand the total number of pages (so that you can determine if "NEXT PAGE" should be shown or if you wanted to allow direct access to page X via a link).
所以这就是分页的基本问题。现在，您需要了解总页数（以便您可以确定是否应显示“NEXT PAGE”或是否允许通过链接直接访问页面 X）。
In order to do this, you must understand the number of rows in the table.
为此，您必须了解表中的行数。
You can simply do this with a DB call before trying to return your actual limited record set (I say BEFORE since you obviously want to validate that the requested page exists).
在尝试返回实际有限的记录集之前，您可以简单地使用 DB 调用来执行此操作（我说之前是因为您显然想验证请求的页面是否存在）。
This is actually quite simple:
这实际上很简单：
$sql = "SELECT your_primary_key_field FROM menuitem";
$result = mysqli_query($con, $sql);
if(false === $result) {
throw new Exception('Query failed with: ' . mysqli_error());
} else {
$row_count = mysqli_num_rows($result);
// free the result set as you don't need it anymore
mysqli_free_result($result);
}
$page_count = 0;
if (0 === $row_count) {
// maybe show some error since there is nothing in your table
} else {
// determine page_count
$page_count = (int)ceil($row_count / $items_per_page);
// double check that request page is in range
if($page > $page_count) {
// error to user, maybe set page to 1
$page = 1;
}
}
// make your LIMIT query here as shown above
// later when outputting page, you can simply work with $page and $page_count to output links
// for example
for ($i = 1; $i <= $page_count; $i++) {
if ($i === $page) { // this is current page
echo 'Page ' . $i . '<br>';
} else { // show link to other page
echo '<a href="/menuitem.php?page=' . $i . '">Page ' . $i . '</a><br>';
}
}
回答by user2864740
Use .. LIMIT :pageSize OFFSET :pageStart
用 .. LIMIT :pageSize OFFSET :pageStart
Where :pageStartis bound to the_page_index (i.e. 0 for the first page) * number_of_items_per_pages (e.g. 4) and :pageSizeis bound to number_of_items_per_pages.
where:pageStart绑定到 the_page_index（即第一页为 0）* number_of_items_per_pages（例如 4）并且:pageSize绑定到 number_of_items_per_pages。
To detect for "has more pages", either use SQL_CALC_FOUND_ROWSor use .. LIMIT :pageSize OFFSET :pageStart + 1and detect a missing last (pageSize+1) record. Needless to say, for pages with an index > 0, there exists a previous page.
要检测“有更多页面”，请使用SQL_CALC_FOUND_ROWS或使用.. LIMIT :pageSize OFFSET :pageStart + 1并检测缺失的最后一个 (pageSize+1) 记录。不用说，对于索引 > 0 的页面，存在前一页。
If the page index value is embedded in the URL (e.g. in "prev page" and "next page" links) then it can be obtained via the appropriate $_GETitem.
如果页面索引值嵌入在 URL 中（例如在“prev page”和“next page”链接中），那么它可以通过适当的$_GET项目获得。
回答by Peter Gruppelaar
If you want to keep it simple go ahead and try this out.
如果您想保持简单，请继续尝试。
$page_number = mysqli_escape_string($con, $_GET['page']);
$count_per_page = 20;
$next_offset = $page_number * $count_per_page;
$cat =mysqli_query($con, "SELECT * FROM categories LIMIT $count_per_page OFFSET $next_offset");
while ($row = mysqli_fetch_array($cat))
$count = $row[0];
The rest is up to you. If you have result comming from two tables i suggest you try a different approach.
剩下的就看你了。如果您有来自两个表的结果，我建议您尝试不同的方法。
回答by Rick James
A dozen pages is not a big deal when using OFFSET. But when you have hundreds of pages, you will find that OFFSETis bad for performance. This is because all the skipped rows need to be read each time.
使用OFFSET. 但是当您有数百个页面时，您会发现这OFFSET对性能不利。这是因为每次都需要读取所有跳过的行。
It is better to remember where you left off.
最好记住你离开的地方。
