使用 MySQL LIMIT、OFFSET 进行分页 [英] Pagination using MySQL LIMIT, OFFSET

问题描述

我有一些代码限制数据每页只显示 4 个项目.我使用的列有大约 20-30 个项目，所以我需要将它们分布在页面上.

在第一页，我有:

 $result = mysqli_query($con,"SELECT * FROM menuitem LIMIT 4");{echo "";echo "<td align='center'><img src=\"" . $row['picturepath'] . "\"/></td>";回声<td align='center'>".$row['name'] ."</td> <td align='center'> <input type='button' value='More Info'; onclick=\"window.location='more_info.php?';\"></td>";回声<td align='center'>".$row['price'] ."</td> <td align='center'> <input type='button' value='Add to Order' onclick=''> </td>";回声</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="">主菜单</a></td></tr>

您会注意到在页面底部，我的锚标记列出了第二个页面itempage2.php".在第 2 页中，我有相同的代码，只是我的 select 语句列出了 4 的偏移量.

$result = mysqli_query($con,"SELECT * FROM menuitem LIMIT 4 offset 4");

当我的数据库中有预定数量的项目时，这是有效的.但它不是那么好.仅当有更多项目时，我才需要创建一个新页面，而不是像现在这样硬编码到其中.

如何创建多个页面而不必对每个新页面进行硬编码和偏移?

解决方案

首先，不要为每个页面设置单独的服务器脚本，这太疯狂了.大多数应用程序通过在 URL 中使用分页参数来实现分页.类似的东西:

http://yoursite.com/itempage.php?page=2

您可以通过$_GET['page']访问请求的页码.

这让您的 SQL 公式变得非常简单:

//从$_GET确定页码$页= 1;if(!empty($_GET['page'])) {$page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);if(false === $page) {$页= 1;}}//设置每页显示的项目数$items_per_page = 4;//构建查询$offset = ($page - 1) * $items_per_page;$sql = "SELECT * FROM menuitem LIMIT ".$偏移量.，".$items_per_page;

例如，如果此处输入的是 page=2，每页有 4 行，则您的查询将是

SELECT * FROM menuitem LIMIT 4,4

这就是分页的基本问题.现在，您需要了解页面总数(以便您可以确定是否应显示NEXT PAGE"或是否允许通过链接直接访问页面 X).

为了做到这一点，您必须了解表中的行数.

在尝试返回实际有限的记录集之前，您可以通过 DB 调用简单地执行此操作(我说 BEFORE，因为您显然想验证请求的页面是否存在).

这其实很简单:

$sql = "SELECT your_primary_key_field FROM menuitem";$result = mysqli_query($con, $sql);$row_count = mysqli_num_rows($result);//释放结果集，因为你不再需要它mysqli_free_result($result);$page_count = 0;如果(0 === $row_count){//可能会显示一些错误，因为您的表中没有任何内容} 别的 {//确定 page_count$page_count = (int)ceil($row_count/$items_per_page);//仔细检查请求页面是否在范围内if($page > $page_count) {//用户出错，可能将页面设置为 1$页= 1;}}//在此处进行 LIMIT 查询，如上所示//稍后在输出页面时，您可以简单地使用 $page 和 $page_count 来输出链接//例如for ($i = 1; $i <= $page_count; $i++) {if ($i === $page) {//这是当前页面回声页面".$i .'
';} else {//显示到其他页面的链接echo '

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.

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.

$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?

解决方案

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:

http://yoursite.com/itempage.php?page=2

You can access the requested page number via $_GET['page'].

This makes your SQL formulation really easy:

// 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"

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).

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).

This is actually quite simple:

$sql = "SELECT your_primary_key_field FROM menuitem";
$result = mysqli_query($con, $sql);
$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>';
   }
}

这篇关于使用 MySQL LIMIT、OFFSET 进行分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！