如何在Ubuntu18.04上使用PHP在MySQL中实现分页

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

介绍

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

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

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

先决条件

在开始之前，您将需要以下内容：

按照 Initial Server Setup 和 Ubuntu 18.04 设置一台 Ubuntu 18.04 服务器，包括 sudo 非 root 用户。
您的系统上安装了 Apache、MySQL 和 PHP。您可以按照如何在 Ubuntu 18.04 上安装 Linux、Apache、MySQL、PHP (LAMP) 堆栈的指南进行操作。

第 1 步 — 创建数据库用户和测试数据库

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

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

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

sudo mysql -u root -p

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

Create database test_db;

您将看到以下输出：

OutputQuery OK, 1 row affected (0.00 sec)

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

GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';

OutputQuery OK, 1 row affected (0.00 sec)

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

FLUSH PRIVILEGES;

OutputQuery OK, 1 row affected (0.00 sec)

接下来，切换到 test_db 数据库，直接在 test_db 数据库上开始工作：

Use test_db;

OutputDatabase changed

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

Create table products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50) NOT NULL ) Engine = InnoDB;

OutputQuery OK, 0 rows affected (0.02 sec)

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

Insert into products(product_name) values ('WIRELESS MOUSE');
Insert into products(product_name) values ('BLUETOOTH SPEAKER');
Insert into products(product_name) values ('GAMING KEYBOARD');
Insert into products(product_name) values ('320GB FAST SSD');
Insert into products(product_name) values ('17 INCHES TFT');
Insert into products(product_name) values ('SPECIAL HEADPHONES');
Insert into products(product_name) values ('HD GRAPHIC CARD');
Insert into products(product_name) values ('80MM THERMAL PRINTER');
Insert into products(product_name) values ('HDMI TO VGA CONVERTER');
Insert into products(product_name) values ('FINGERPRINT SCANNER');

你会看到这个输出：

OutputQuery OK, 1 row affected (0.02 sec)

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

select * from products;

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

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)

退出 MySQL：

quit;

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

第 2 步 — 不分页显示 MySQL 记录

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

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

sudo nano /var/www/html/pagination_test.php

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

/var/www/html/pagination_test.php

<?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();
}

?>

按 CTRL+X、Y 和 ENTER 保存文件。

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

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

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

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

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

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

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

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

第三步——用 PHP 实现分页

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

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

Select [column1, column2, column n...] from [table name] LIMIT offset, records;

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

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

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

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

sudo nano /var/www/html/pagination_test.php

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

/var/www/html/pagination_test.php

<?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();
}

?>

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

$page ：此变量保存脚本中的当前页面。在页面之间移动时，您的脚本使用 $_GET['page'] 变量检索名为 page 的 URL 参数。
$per_page：此变量保存您希望每页显示的最大记录数。在您的情况下，您希望在每个页面上列出三个产品。
$total_records：在列出产品之前，您正在执行一条 SQL 语句以获取目标表中的记录总数并将其分配给 $total_records 变量。
$offset：此变量表示在第一行之前要跳过的总记录。此值由您的 PHP 脚本使用公式 $offset=($page-1)*$per_page 动态计算。您可以将此公式应用于您的 PHP 分页项目。请记住，您可以更改 $per_page 变量以满足您的需要。例如，您可以将其更改为 50 的值以在每页显示 50 个项目，如果您正在运行一个网站或其他数量的移动设备。

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

http://your_server_ip/pagination_test.php

您现在将在页面底部看到一些导航按钮。在第一页上，您不会得到 Previous 按钮。同样的情况发生在最后一页上，您不会获得 Next 页面按钮。另外，请注意 page URL 参数在您访问每个页面时如何变化。

页面底部的导航链接是使用文件中的以下 PHP 代码片段实现的：

/var/www/html/pagination_test.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>";
}
. . .

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

同样，要进入下一页，您将一个添加到 $page 变量，您还必须确保我们附加到 page URL 参数的 $page 结果是不大于您在 PHP 代码中计算的总页数。

此时，您的 PHP 脚本正在处理分页，您可以实现 MySQL LIMIT 子句以获得更好的记录导航。

结论

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

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