分页使用MySQL LIMIT,OFFSET

首先,每个页面没有单独的服务器脚本,这只是疯狂.大多数应用程序通过在URL中使用分页参数来实现分页.就像是:

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

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

这使您的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;

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

SELECT * FROM menuitem LIMIT 4,4

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

为此,您必须了解表中的行数.

在尝试返回实际的有限记录集之前,您可以使用数据库调用执行此操作(我之前说过,因为您显然希望验证所请求的页面是否存在).

这实际上非常简单:

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