php mysql分页_使用PHP和Mysql分页

php mysql分页

Requirements

要求

PHP 5.3+

PHP 5.3以上

mysqli connection library

mysqli连接库

MySQL 5+

MySQL 5+

Introduction

介绍

This article gives an overview of how to add pagination to your application. If you have an application that has to display multiple rows of data it is advisable to break this information up into pages so as not to overwhelm the reader.

本文概述了如何向您的应用程序添加分页。 如果您的应用程序必须显示多行数据，则建议将此信息分成几页，以免使读者不知所措。

The Basics

基础

MySQL has a useful feature LIMIT which allows you to select how many rows you want to retrieve from a query and what row to start from. It is therefore easy to break your rows up into pages all you need is

MySQL具有一项有用的功能LIMIT ，它使您可以选择要从查询中检索多少行以及从哪一行开始。 因此，很容易将您的行分成几页，您所需要的只是

PageSize - specifying how many rows per page you are interested in

PageSize-指定您感兴趣的每页多少行

PageNumber - identifies a particular page

PageNumber-标识特定页面

TotalRows - how many rows there are in the dataset

TotalRows-数据集中有多少行

With the above information it is simple to build the queries to retreive the data you need

利用以上信息，可以轻松构建查询以检索所需的数据

The query to retrieve the data from the dataset would need to look something like this

从数据集中检索数据的查询需要看起来像这样

SELECT field1, field2, ... 
  FROM table 
  WHERE condition 
  LIMIT StartRow, TotalRows

All we need to do is calculate the value of [StartRow] and [TotalRows].

我们需要做的就是计算[StartRow]和[TotalRows]的值。

Where [StartRow] and [TotalRows] are calculated and added to your query by your script

通过脚本计算[StartRow]和[TotalRows]并将其添加到查询中的位置

StartRow

起始行

This is simply a product of the PageSize and the requested PageNo - 1.

这简直是每页和要求您做生意的产物- 1。

LIMIT works from a 0 base but people are more used to pages starting from 1 so we need to subtract 1 from the human readable page to get the start page for Limit

LIMIT从0的基础开始工作，但是人们更习惯于从1开始的页面，因此我们需要从人类可读的页面中减去1以获得Limit的开始页面

Example

例

If our PageSize = 10 and PageNumber is 3 then we calculate StartRow as

如果我们的PageSize = 10且PageNumber为3，则我们将StartRow计算为

StartRow = 10 x (3 -1) = 20

起始行= 10 x（3 -1）= 20

TotalRows is simply the PageSize which you can define as constant in your script OR add functionality for the user to select how many rows they want to see on a page.

TotalRows只是PageSize，您可以在脚本中将其定义为常量，或者添加功能以供用户选择他们希望在页面上看到多少行。

What is the Total number of Pages in the Recordset?

记录集中的总页数是多少？

N It would be useful to show the visitor how many pages there are in the table. How do we find the total number of rows that would have been retrieved had we not added LIMIT to the query?

N向访问者显示表中有多少页会很有用。 如果不将LIMIT添加到查询中，我们如何找到将要检索的总行数？

One way is to run another query with COUNT to return the number of available rows

一种方法是使用COUNT运行另一个查询以返回可用行数

SELECT COUNT(*) FROM table WHERE condition

SQL_CALC_FOUND_ROWS.

SQL_CALC_FOUND_ROWS 。

If we add SQL_CALC_FOUND_ROWS to our query MySQL will automatically store the total number of rows in the dataset. We can retrieve this value by calling the FOUND_ROWS() function in MySQL.

如果我们向查询中添加SQL_CALC_FOUND_ROWS ，MySQL将自动在数据集中存储总行数。 我们可以通过在MySQL中调用FOUND_ROWS（）函数来检索此值。

Example

例

SELECT SQL_CALC_FOUND_ROWS, field1, field2, ... 
  FROM table 
  WHERE condition 
  LIMIT 20,10

SELECT FOUND_ROWS()

Putting it all together

放在一起

The code below is a fully functional pagination example. Before we can use it though we need to create the data.

下面的代码是一个功能齐全的分页示例。 在使用它之前，我们需要创建数据。

Client Table

客户表

CREATE TABLE `client` (
  `id` int(11) NOT NULL auto_increment,
  `firstname` varchar(50) default NULL,
  `surname` varchar(50) default NULL,
  `email` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
)

The Database class is a wrapper for the mysqli connection library

Database类是mysqli连接库的包装器

database.php

database.php

// Our connection variables - in a real project these would most likely be 
// included in a config file that would be included in the application 
// deprecating the requirement to include them here.
// They are reproduced here for clarity.
 
define('db_server','localhost');
define('db_user','user');
define('db_password','password');
define('db_database','ee');
 
class Database
{
  private $connection;  // A property to hold our database connection
  
  function __construct()
  {
    $this->connection = new mysqli(
      db_server, 
      db_user, 
      db_password, 
      db_database);
  }
  
  // A static method to return the current database connection
  // The function ensures a single instance of the database 
  // object is used in the application
  static function &getConnection()
  {
    static $connection;
 
    // If a database object has not been instantiated then do so
    if (!is_object($connection)) {
      $connection = new Database();
    }
    
    return $connection;
  }
  
  // Wrapper function for the query method
  // This can be expanded with additional parameters to 
  // use prepared statements etc
  function query($query)
  {
    return $this->connection->query($query);
  }
}

Result class manages the results we retrieve from the database and renders the client table and pagination

Result类管理从数据库中检索到的结果，并呈现客户端表和分页

results.php

results.php

// The pg_pagesize constant determines how many results you want 
// to see on a page. Again, this could be defined in the config file. 
// In more advanced implementations this can be modifiable by the 
// reader to show more or less items on a page.
 
define('pg_pagesize', 10);
 
class Results
{
  private $pagenumber = null; // Requested page number
  private $result = null;   // Results from querying the client table
  private $db = null;     // Instance of the Database singleton class
  private $total = 0;     // Total number of records in the client table
  
  // The constructor takes a requested pagenumber
  // It builds and executes the query against the client's table
  // It also calculates total number of rows in the client's table
  function __construct($pagenumber)
  {
    // We want to work with pagenumbers starting from 0 - but humans 
    // are used to page numbers starting from 1 so we need to compensate.
    $this->pagenumber = $pagenumber - 1;
    
    // Store the pagenumber in the session so we don't annoy users
    // who navigate away from the page and want to see the same 
    // results when they return
    $_SESSION['pagenumber'] = $this->pagenumber;
    
    // Calculate the start row
    $start = pg_pagesize * $this->pagenumber;
    
    // Build the query to fetch the required rows from the database 
    // starting from the requested page
    $this->db = Database::getConnection();
    $query = sprintf("SELECT SQL_CALC_FOUND_ROWS * FROM `client` LIMIT %d, %d", 
               $start, pg_pagesize);
  
    $this->result = $this->db->query($query);
    
    // Fetch the calculated total rows of the dataset without the LIMIT
    $total = $this->db->query("SELECT FOUND_ROWS() AS total");
    $row = $total->fetch_object();
    $this->total = $row->total;
  }
  
  // This is the workhorse of the class. It cycles through the dataset 
  // and outputs the HTML rows for our data table
  function showResults()
  {
    print "<table>" . _L;
    print "\t<tr>" . _L;
    print "\t\t<th>First Name</th><th>Surname</th><th>Email</th>" . _L;
    print "\t</tr>" . _L;
    while($row = $this->result->fetch_object()) {
      print "\t\t<tr>" . _L;
      printf("\t\t\t<td>%s</td><td>%s</td><td>%s</td>" . _L, 
              $row->firstname, $row->surname, $row->email);
      print "\t\t</tr>" . _L;
    }
    print "</table>" . _L;
    $this->result->close();
  }
  
  // Here is where the pagination happens.
  // The class is designed that this function can be called from 
  // anywhere in the code (and multiple times)
  // It is not dependent on showResults having already been called.
  function showPagination()
  {
    // Calculate how many pages there are. We do that by taking the 
    // integer division of the total rows
    $pages = intval($this->total / pg_pagesize);
    
    // and adding 1 if there is a reminder.
    $pages += ($this->total%$pages)?1:0;
    
    // Finally we have enough information to output our pagination HTML
    print "<div class=\"page-navigation\">" . _L;
    print '<a href="client.php?page=1" class="pagination-arrows">&lt;&lt;</a>';    
    
    // We don't want to show the goto previous record if we are on record 1
    if ($this->pagenumber > 0) {
      printf( "<a href=\"client.php?page=%d\" class=\"pagination-arrows\">&lt;</a>", 
            $this->pagenumber);
    }
    for($i=1;$i<=$pages;$i++) {
      if ($i == $this->pagenumber+1) {
        printf("<span>%d</span>" . _L, $i);
      }
      else {
        printf("<a href=\"client.php?page=%d\">%d</a>" . _L, $i, $i);
      }
    }      
    
    // We don't want to show goto next record if we are on the last page.
    if ($this->pagenumber < $pages-1) {
      printf("<a href=\"client.php?page=%d\" class=\"pagination-arrows\">&gt;</a>", 
            $this->pagenumber + 2);
    }
    printf("<a href=\"client.php?page=%d\" class=\"pagination-arrows\">&gt;&gt;</a>", 
          $pages);
    print "</div>" . _L;
  }
}

client.php

client.php

// We are using sessions to store our pagenumber so initiate a session
session_start();
 
// shortcut to the newline character
define('_L', PHP_EOL);
 
// Include our workhorse files
require_once('database.php');
require_once('results.php');
 
// Work out what page number to use
// First we look to see if a page number is specified in the URL request.
// If it is we sanitize it to remove everything except numbers.
// If no page was found then default to the empty string
$pageno = isset($_GET['page']) 
  ? preg_replace('/[^0-9]/', '', $_GET['page']) 
  : '';
 
// This could have been done on one line but has been split for clarity
// If pageno is empty then attempt to get it from the session.
// If the session is not set yet then default to 1.
$pageno = empty($pageno) 
  ? (
    isset($_SESSION['pagenumber']) 
    ? $_SESSION['pagenumber'] + 1
    : 1
    ) 
  : $pageno;
 
// Instantiate the results object to output to the page
$results = new Results($pageno);
?>
<!doctype html>
<html>
<head>
<title>Client Results Page</title>
</script>
<style type="text/css">
body {
  font-size: 100%;
  font-family: Arial;
}
table {
  border-collapse: collapse;
  font-size: 0.8em;
}
th, td {
  border: 1px solid #888;
  padding: 2px 5px;
}
tr:nth-child(2n) {
  background: #effeff;
}
.page-navigation {
  font-size: 0.6em;
}
.page-navigation a.pagination-arrows {
  padding: 0 4px;
}
</style>
</head>
<body>
<?php 
  // Demonstrate that pagination can be output anywhere
  // and any number of times
  $results->showPagination();
  
  // Display our results
  $results->showResults(); ;?>
  
  // Demonstrate that pagination can be output anywhere 
  // and any number of times
  $results->showPagination();
?>
</body>
</html>

SQL_CALC_FOUND_ROWS and FOUND_ROWS

SQL_CALC_FOUND_ROWS和FOUND_ROWS

翻译自: https://www.experts-exchange.com/articles/12238/Pagination-with-PHP-and-Mysql.html

php mysql分页