The author selected the the Apache Software Foundation to receive a donation as part of the Write for DOnations program.

作者选择了Apache软件基金会作为Write for DOnations计划的一部分来接受捐赠。

介绍 (Introduction)

Pagination is the concept of constraining the number of returned rows in a recordset into separate, orderly pages to allow easy navigation between them, so when there is a large dataset you can configure your pagination to only return a specific number of rows on each page. For example, pagination can help to avoid overwhelming users when a web store contains thousands of products by reducing the number of items listed on a page, as it’s often unlikely a user will need to view every product. Another example is an application that shows records on a mobile device; enabling pagination in such a case would split records into multiple pages that can fit better on a screen.

分页是将记录集中返回的行数限制为单独的，有序的页面，以便于它们之间的轻松导航的概念，因此，当数据集很大时，可以将分页配置为仅在每个页面上返回特定的行数。 例如，分页可以通过减少页面上列出的项目数量来帮助避免网店中包含数千种产品的用户的压力，因为用户通常不太可能需要查看每个产品。 另一个示例是一个在移动设备上显示记录的应用程序。 在这种情况下启用分页功能会将记录分成多个页面，可以更好地适合屏幕。

Besides the visual benefits for end-users, pagination makes applications faster because it reduces the number of records that are returned at a time. This limits the data that needs to be transmitted between the client and the server, which helps preserve server resources such as RAM.

除了给最终用户带来视觉上的好处外，分页还使应用程序更快，因为它减少了一次返回的记录数。 这限制了需要在客户端和服务器之间传输的数据，这有助于保留服务器资源(例如RAM)。

In this tutorial, you’ll build a PHP script to connect to your database and implement pagination to your script using the MySQL LIMIT clause.

在本教程中，您将构建一个PHP脚本以连接到数据库并使用MySQL LIMIT子句对脚本执行分页。

先决条件 (Prerequisites)

Before you begin, you will need the following:

在开始之前，您需要满足以下条件：

• One Ubuntu 18.04 server set up by following the Initial Server Setup with Ubuntu 18.04, including a sudo non-root user.

  通过对Ubuntu 18.04进行初始服务器设置来设置一台Ubuntu 18.04服务器，包括sudo非root用户。

• Apache, MySQL, and PHP installed on your system. You can follow the guide on How To Install Linux, Apache, MySQL, PHP (LAMP) stack on Ubuntu 18.04.

  系统上安装了Apache，MySQL和PHP。 您可以按照有关如何在Ubuntu 18.04上安装Linux，Apache，MySQL，PHP(LAMP)堆栈的指南进行操作 。

第1步-创建数据库用户和测试数据库 (Step 1 — Creating a Database User and a Test Database)

In this tutorial you’ll create a PHP script that will connect to a MySQL database, fetch records, and display them in an HTML page within a table. You’ll test the PHP script in two different ways from your web browser. First, creating a script without any pagination code to see how the records are displayed. Second, adding page navigation code in the PHP file to understand how pagination works practically.

在本教程中，您将创建一个PHP脚本，该脚本将连接到MySQL数据库，获取记录并在表HTML页面中显示它们。 您将通过Web浏览器以两种不同的方式测试PHP脚本。 首先，创建一个没有任何分页代码的脚本以查看记录的显示方式。 其次，在PHP文件中添加页面导航代码，以了解分页实际上如何工作。

The PHP code requires a MySQL user for authentication purposes and a sample database to connect to. In this step you’ll create a non-root user for your MySQL database, a sample database, and a table to test the PHP script.

PHP代码要求使用MySQL用户进行身份验证和连接示例数据库。 在这一步中，您将为MySQL数据库，示例数据库和用于测试PHP脚本的表创建一个非root用户。

To begin log in to your server. Then log in to your MySQL server with the following command:

要开始登录到服务器。 然后使用以下命令登录到您MySQL服务器：

Enter the root password of your MySQL server and hit ENTER to continue. Then, you’ll see the MySQL prompt. To create a sample database, which we will call test_db in this tutorial, run the following command:

输入MySQL服务器的root密码，然后按ENTER继续。 然后，您将看到My​​SQL提示符。 要创建一个示例数据库，在本教程中我们将其称为test_db ，请运行以下命令：

You will see the following output:

您将看到以下输出：

   

    Output
   
Query OK, 1 row affected (0.00 sec)

Then, create a test_user and grant the user all privileges to the test_db. Replace PASSWORD with a strong value:

然后，创建一个test_user并向该用户授予test_db所有特权。 将PASSWORD替换为强值：

   

    Output
   
Query OK, 1 row affected (0.00 sec)

Reload the MySQL privileges with:

使用以下命令重新加载MySQL特权：

   

    Output
   
Query OK, 1 row affected (0.00 sec)

Next, switch to the test_db database to start working directly on the test_db database:

接下来，切换到test_db数据库直接开始了有关工作test_db数据库：

   

    Output
   
Database changed

Now create a products table. The table will hold your sample products—for this tutorial you’ll require only two columns for the data. The product_id column will serve as the primary key to uniquely identify each record. This column will be set to AUTO_INCREMENT to generate a new product_id for each item inserted. You’ll use the product_name field to differentiate each item by name:

现在创建一个products表。 该表将保存您的示例产品-在本教程中，您仅需要两列数据。 product_id列将用作唯一标识每个记录的主键。 此列将设置为AUTO_INCREMENT ，以为插入的每个项目生成一个新的product_id 。 您将使用product_name字段按名称区分每个项目：

   

    Output
   
Query OK, 0 rows affected (0.02 sec)

To add ten test products to the products table run the following SQL statements:

要将十个测试产品添加到products表，请运行以下SQL语句：

You’ll see this output:

您将看到以下输出：

   

    Output
   
Query OK, 1 row affected (0.02 sec)

Verify that the products were inserted to the table by running:

通过运行以下命令来验证产品是否已插入表中：

You’ll see the products in your output within the two columns:

您会在两列的输出中看到产品：

   

    Output
   
+------------+-----------------------+
| product_id | product_name          |
+------------+-----------------------+
|          1 | WIRELESS MOUSE        |
|          2 | BLUETOOTH SPEAKER     |
|          3 | GAMING KEYBOARD       |
|          4 | 320GB FAST SSD        |
|          5 | 17 INCHES TFT         |
|          6 | SPECIAL HEADPHONES    |
|          7 | HD GRAPHIC CARD       |
|          8 | 80MM THERMAL PRINTER  |
|          9 | HDMI TO VGA CONVERTER |
|         10 | FINGERPRINT SCANNER   |
+------------+-----------------------+
10 rows in set (0.00 sec)

Exit MySQL:

退出MySQL：

With the sample database, table, and test data in place, you can now create a PHP script to display data on a web page.

有了示例数据库，表和测试数据后，您现在可以创建一个PHP脚本以在网页上显示数据。

第2步-不分页显示MySQL记录 (Step 2 — Displaying MySQL Records Without Pagination)

Now you’ll create a PHP script that connects to the MySQL database that you created in the previous step and list the products in a web browser. In this step, your PHP code will run without any form of pagination to demonstrate how non-split records show on a single page. Although you only have ten records for testing purposes in this tutorial, seeing the records without pagination will demonstrate why segmenting data will ultimately create a better user experience and put less burden on the server.

现在，您将创建一个PHP脚本，该脚本连接到在上一步中创建MySQL数据库，并在Web浏览器中列出产品。 在此步骤中，您PHP代码将在没有任何分页形式的情况下运行，以演示非拆分记录在单个页面上的显示方式。 尽管在本教程中您只有10条记录用于测试目的，但查看没有分页的记录将说明为什么对数据进行分段最终可以创建更好的用户体验，并减轻服务器负担。

Create the PHP script file in the document root of your website with the following command:

使用以下命令在网站的文档根目录中创建PHP脚本文件：

Then add the following content to the file. Remember to replace PASSWORD with the correct value of the password that you assigned to the test_user in the previous step:

然后将以下内容添加到文件中。 请记住test_user一步中分配给test_user的正确PASSWORD替换PASSWORD ：

<?php

try {
  $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

  $sql = "select * from products";

  $stmt = $pdo->prepare($sql);

  $stmt -> execute();

  echo "<table border='1' align='center'>";

  while (($row = $stmt -> fetch(PDO::FETCH_ASSOC)) !== false) {
    echo "<tr>";
    echo "<td>".$row['product_id']."</td>";
    echo "<td>".$row['product_name']."</td>";
    echo "</tr>";
  }

  echo "</table>";

} catch(PDOException $e) {
  echo $e->getMessage();
}

?>

Save the file by pressing CTRL+X, Y, and ENTER.

通过按CTRL+X ， Y和ENTER保存文件。

In this script you’re connecting to the MySQL database using the PDO (PHP Data Object) library with the database credentials that you created in Step 1.

在此脚本中，您将使用PDO(PHP数据对象)库以及在步骤1中创建的数据库凭据连接到MySQL数据库。

PDO is a light-weight interface for connecting to databases. The data access layer is more portable and can work on different databases with just minor code rewrites. PDO has greater security since it supports prepared statements—a feature for making queries run faster in a secure way.

PDO是用于连接数据库的轻量级接口。 数据访问层具有更高的可移植性，并且只需少量的代码重写就可以在不同的数据库上工作。 PDO支持预准备语句，因此具有更高的安全性-一种使查询以安全方式更快运行的功能。

Then, you instruct the PDO API to execute the select * from products statement and list products in an HTML table without pagination. The line $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); ensures that the data types are returned as they appear in the database. This means that PDO will return the product_id as an integer and the product_name as a string. $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); instructs PDO to throw an exception if an error is encountered. For easier debugging you’re catching the error inside the PHP try{}...catch{} block.

然后，您指示PDO API执行select * from products语句中的select * from products并在HTML表中列出产品而不进行分页。 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); 确保数据类型按它们在数据库中出现的方式返回。 这意味着PDO将以整数形式返回product_name ，以字符串形式返回product_id 。 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 指示PDO在遇到错误时引发异常。 为了简化调试，您可以在PHP try{}...catch{}块中try{}...catch{}错误。

To execute the /var/www/html/pagination_test.php PHP script file that you’ve created, visit the following URL replacing your-server-IP with the public IP address of your server:

要执行您创建的/var/www/html/pagination_test.php PHP脚本文件，请访问以下URL，将your-server-IP替换your-server-IP的公共IP地址：

http://your-server-IP/pagination_test.php

You’ll see a page with a table of your products.

您会看到一个包含产品表的页面。

Your PHP script is working as expected; listing all products on one page. If you had thousands of products, this would result in a long loop as the products are fetched from the database and rendered on the PHP page.

您PHP脚本按预期工作； 在一页上列出所有产品。 如果您有成千上万的产品，这将导致漫长的循环，因为这些产品是从数据库中提取并呈现在PHP页面上的。

To overcome this limitation, you will modify the PHP script and include the MySQL LIMIT clause and some navigation links at the bottom of the table to add pagination functionality.

为了克服此限制，您将修改PHP脚本，并在表底部添加MySQL LIMIT子句和一些导航链接以添加分页功能。

第3步-使用PHP实现分页 (Step 3 — Implementing Pagination with PHP)

In this step your goal is to split the test data into multiple and manageable pages. This will not only enhance readability but also use the resources of the server more efficiently. You will modify the PHP script that you created in the previous step to accommodate pagination.

在此步骤中，您的目标是将测试数据分为多个可管理的页面。 这不仅可以提高可读性，而且可以更有效地使用服务器的资源。 您将修改在上一步中创建PHP脚本以适应分页。

To do this, you’ll be implementing the MySQL LIMIT clause. Before adding this to the script, let’s see an example of the MySQL LIMIT syntax:

为此，您将实现MySQL LIMIT子句。 在将其添加到脚本之前，让我们来看一个MySQL LIMIT语法的示例：

The LIMIT clause takes two arguments as shown toward the end of this statement. The offset value is the number of records to skip before the first row. records sets the maximum number of records to display per page.

LIMIT子句采用两个自变量，如该语句结尾所示。 offset值是第一行之前要跳过的记录数。 records设置每页显示的最大记录数。

To test pagination, you’ll display three records per page. To get the total number of pages, you must divide the total records from your table with the rows that you want to display per page. You then round the resulting value to the nearest integer using PHP Ceil function as shown in the following PHP code snippet example:

要测试分页，每页将显示三个记录。 要获得总页数，必须将表中的总记录除以要在每页上显示的行。 然后，使用PHP Ceil函数将结果值舍入为最接近的整数，如以下PHP代码片段示例所示：

$total_pages=ceil($total_records/$per_page);

Following is the modified version of the PHP script with the full pagination code. To include the pagination and navigation codes, open the /var/www/html/pagination_test.php file:

以下是带有完整分页代码PHP脚本的修改版本。 要包含分页和导航代码，请打开/var/www/html/pagination_test.php文件：

Then, add the following highlighted code to your file:

然后，将以下突出显示的代码添加到您的文件中：

<?php

try {
  $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

  /* Begin Paging Info */

  $page = 1;

  if (isset($_GET['page'])) {
    $page = filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_INT);
  }

  $per_page = 3;

  $sqlcount = "select count(*) as total_records from products";
  $stmt = $pdo->prepare($sqlcount);
  $stmt->execute();
  $row = $stmt->fetch();
  $total_records = $row['total_records'];

  $total_pages = ceil($total_records / $per_page);

  $offset = ($page-1) * $per_page;

  /* End Paging Info */

  $sql = "select * from products limit :offset, :per_page";
  $stmt = $pdo->prepare($sql);
  $stmt->execute(['offset'=>$offset, 'per_page'=>$per_page]);

  echo "<table border='1' align='center'>";

  while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {
    echo "<tr>";
    echo "<td>".$row['product_id']."</td>";
    echo "<td>".$row['product_name']."</td>";
    echo "</tr>";
  }

  echo "</table>";

  /* Begin Navigation */

  echo "<table border='1' align='center'>";

  echo "<tr>";

  if ($page-1 >= 1) {
    echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page - 1).">Previous</a></td>";
  }

  if ($page+1 <= $total_pages) {
    echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page + 1).">Next</a></td>";
  }

  echo "</tr>";

  echo "</table>";

  /* End Navigation */

} catch(PDOException $e) {
  echo $e->getMessage();
}

?>

In your file you’ve used additional parameters to execute paging:

在文件中，您使用了其他参数来执行分页：

• $page : This variable holds the current page in your script. When moving between the pages, your script retrieves a URL parameter named page using the $_GET['page'] variable.

  $page ：此变量保存脚本中的当前页面。 在页面之间移动时，脚本使用$_GET['page']变量检索名为page的URL参数。

• $per_page: This variable holds the maximum records that you want to be displayed per page. In your case, you want to list three products on each page.

  $per_page ：此变量保存每个页面要显示的最大记录数。 您要在每个页面上列出三个产品。

• $total_records: Before you list the products, you’re executing a SQL statement to get a total count of records in your target table and assigning it to the $total_records variable.

  $total_records ：在列出产品之前，您正在执行一条SQL语句以获取目标表中记录的总数，并将其分配给$total_records变量。

• $offset: This variable represents the total records to skip before the first row. This value is calculated dynamically by your PHP script using the formula $offset=($page-1)*$per_page. You may adapt this formula to your PHP pagination projects. Remember you can change the $per_page variable to suit your needs. For instance, you might change it to a value of 50 to display fifty items per page if you’re running a website or another amount for a mobile device.

  $offset ：此变量表示在第一行之前要跳过的总记录。 此值由您PHP脚本使用$offset=($page-1)*$per_page公式动态计算。 您可以根据您PHP分页项目调整此公式。 请记住，您可以更改$per_page变量以适合您的需求。 例如，您可以将其更改为50的值，以在运行网站时显示每页五十个项目，或者在移动设备上显示其他金额。

Again, visit your IP address in a browser and replace your_server_ip with the public IP address of your server:

再次，在浏览器中访问您的IP地址，并将your_server_ip替换为服务器的公共IP地址：

http://your_server_ip/pagination_test.php

You’ll now see some navigation buttons at the bottom of the page. On the first page, you will not get a Previous button. The same case happens on the last page where you will not get the Next page button. Also, note how the page URL parameter changes as you visit each page.

现在，您将在页面底部看到一些导航按钮。 在第一页上，您不会获得上一步按钮。 在最后一页上不会出现“ 下一页 ”按钮，情况相同。 另外，请注意page URL参数在您访问每个页面时如何变化。

The navigation links at the bottom of the page are achieved using the following PHP code snippet from your file:

使用文件中的以下PHP代码段可访问页面底部的导航链接：

. . .
if( $page-1>=1) {
  echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Previous</a></td>";
}

if( $page+1<=$total_pages) {
  echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Next</a></td>";
}
. . .

Here, the $page variable represents the current page number. Then, to get the previous page, the code will minus 1 from the variable. So, if you’re on page 2, the formula (2-1) will give you a result of 1 and this will be the previous page to appear in the link. However, keep in mind that it will only show the previous page if there is a result greater or equal to 1.

在这里， $page变量代表当前的页码。 然后，要获得上一页，代码将从变量中减去1 。 因此，如果您在第2页上，则公式(2-1)将为您提供1的结果，这将是该链接中显示的上一页。 但是，请记住，只有结果大于或等于1 ，它才会显示上一页。

Similarly, to get to the next page, you add one to the $page variable and you must also make sure that the $page result that we append to the page URL parameter is not greater than the total pages that you’ve calculated in your PHP code.

同样，要转到下一页，请在$page变量中添加一个，并且还必须确保我们附加到page URL参数的$page结果不大于您在自己的网页中计算出的总页数PHP代码。

At this point, your PHP script is working with pagination and you are able to implement the MySQL LIMIT clause for better record navigation.

至此，您PHP脚本正在使用分页，并且您可以实现MySQL LIMIT子句以实现更好的记录导航。

结论 (Conclusion)

In this tutorial, you implemented paging in MySQL with PHP on an Ubuntu 18.04 server. You can use these steps with a larger recordset using the PHP script to include pagination. By using pagination on your website or application you can create better user navigation and optimum resource utilization on your server.

在本教程中，您在Ubuntu 18.04服务器上使用PHP在MySQL中实现了分页。 您可以将这些步骤与更大的记录集一起使用，该记录集使用PHP脚本来包括分页。 通过在您的网站或应用程序上使用分页，可以在服务器上创建更好的用户导航和最佳资源利用。

From here you could consider further optimization for your database and other database tasks with these tutorials:

从这里开始，您可以考虑通过这些教程进一步优化数据库和其他数据库任务：

• How To Optimize MySQL with Query Cache on Ubuntu 18.04

  如何在Ubuntu 18.04上使用查询缓存优化MySQL

• An Introduction to Queries in MySQL

  MySQL查询简介

• How to Troubleshoot Issues in MySQL

  如何解决MySQL中的问题

翻译自: https://www.digitalocean.com/community/tutorials/how-to-implement-pagination-in-mysql-with-php-on-ubuntu-18-04