使用MySQL LIMIT，OFFSET的分页 [英] Pagination using MySQL LIMIT, OFFSET

问题描述

我有一些代码可以限制数据显示，每页仅显示4个项目.我使用的列大约有20到30个项目，因此我需要将这些项目分散在各个页面上.

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.

在第一页上，我有:

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

您会注意到，在页面底部，我的锚标记位于第二页"itempage2.php"的列表中.在项目页面2中，我具有相同的代码，除了我的select语句列出了偏移量4.

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?

推荐答案

首先，不要为每个页面使用单独的服务器脚本，这只是疯狂.大多数应用程序通过使用URL中的分页参数来实现分页.像这样:

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

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

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

这使您的SQL编写非常简单:

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;

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

So for example if input here was page=2, with 4 rows per page, your query would be"

SELECT * FROM menuitem LIMIT 4,4

所以这是分页的基本问题.现在，您有了一个附加的要求，即您希望了解页面总数(以便可以确定是否应显示下一页"，或者是否要允许通过链接直接访问页面X).

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

这实际上很简单:

$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>';
   }
}

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