如何在 Ubuntu 18.04 上使用 PHP 在 MySQL 中实现分页

作者选择了 Apache Software Foundation作为 Write for Donations计划的一部分接受捐款。

介绍

Pagination 是将记录集中返回的行数限制为单独、有序的页面的概念,以便在它们之间轻松导航,所以当有一个大数据集时,您可以配置您的页面化以在每个页面上只返回特定数量的行。例如,页面化可以帮助避免在网店中包含数千个产品时压倒用户,从而减少页面上列出的项目数量,因为用户通常不太可能需要查看每个产品。

除了对最终用户的视觉效益外,页面化使应用程序更快,因为它减少一次返回的记录数量,这限制了客户端和服务器之间需要传输的数据,有助于保护服务器资源,如RAM。

在本教程中,您将构建一个 PHP脚本连接到您的数据库,并使用 MySQL LIMIT条款实现脚本的页面化。

前提条件

在你开始之前,你需要以下几点:

一个 Ubuntu 18.04 服务器通过遵循 Ubuntu 18.04 初始服务器设置设置,包括一个 sudo 非根用户
安装在您的系统上的 Apache、MySQL 和 PHP。

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

在本教程中,您将创建一个PHP脚本,将连接到MySQL数据库,收集记录,并在表中显示它们的HTML页面。您将测试PHP脚本从您的Web浏览器的两种不同的方式。

PHP 代码需要一个 MySQL 用户用于身份验证和一个样本数据库来连接. 在此步骤中,您将为您的 MySQL 数据库创建一个非根用户,一个样本数据库和一个表来测试 PHP 脚本。

要开始登录到您的服务器,然后使用以下命令登录到您的MySQL服务器:

1sudo mysql -u root -p

输入您的MySQL服务器的 root密码,然后按ENTER继续,然后,你会看到MySQL提示。要创建一个样本数据库,我们将在本教程中称之为test_db,请执行以下命令:

1Create database test_db;

您将看到以下结果:

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)

然后,创建一个test_user,并将所有权限授予用户到test_db。

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

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)

重新加载 MySQL 特权:

1FLUSH PRIVILEGES;

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)

接下来,切换到test_db数据库以开始直接在test_db数据库上工作:

1Use test_db;

1[secondary_label Output]
2Database changed

现在创建一个产品表表将包含您的样本产品 - 对于本教程,您只需要两个数据列。product_id列将作为主要密钥,以便单独识别每个记录。这个列将设置为AUTO_INCREMENT,为每个插入的项目生成一个新的product_id。

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

1[secondary_label Output]
2Query OK, 0 rows affected (0.02 sec)

若要将十个测试产品添加到产品表中,请运行以下 SQL 语句:

 1Insert into products(product_name) values ('WIRELESS MOUSE');
 2Insert into products(product_name) values ('BLUETOOTH SPEAKER');
 3Insert into products(product_name) values ('GAMING KEYBOARD');
 4Insert into products(product_name) values ('320GB FAST SSD');
 5Insert into products(product_name) values ('17 INCHES TFT');
 6Insert into products(product_name) values ('SPECIAL HEADPHONES');
 7Insert into products(product_name) values ('HD GRAPHIC CARD');
 8Insert into products(product_name) values ('80MM THERMAL PRINTER');
 9Insert into products(product_name) values ('HDMI TO VGA CONVERTER');
10Insert into products(product_name) values ('FINGERPRINT SCANNER');

你会看到这个输出:

1[secondary_label Output]
2Query OK, 1 row affected (0.02 sec)

通过运行检查产品是否被插入表中:

1select * from products;

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

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

离开思维:

1quit;

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

步骤 2 – 显示 MySQL 记录而无需页面化

现在你将创建一个PHP脚本,连接到你在上一个步骤中创建的MySQL数据库,并列出产品在Web浏览器中。在这个步骤中,你的PHP代码将运行没有任何形式的页面化,以展示非分割记录如何显示在单个页面上。

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

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

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

 1[label /var/www/html/pagination_test.php]
 2<?php
 3
 4try {
 5  $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
 6  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 7  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
 8
 9  $sql = "select * from products";
10
11  $stmt = $pdo->prepare($sql);
12
13  $stmt -> execute();
14
15  echo "<table border='1' align='center'>";
16
17  while (($row = $stmt -> fetch(PDO::FETCH_ASSOC)) !== false) {
18    echo "<tr>";
19    echo "<td>".$row['product_id']."</td>";
20    echo "<td>".$row['product_name']."</td>";
21    echo "</tr>";
22  }
23
24  echo "</table>";
25
26} catch(PDOException $e) {
27  echo $e->getMessage();
28}
29
30?>

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

在本脚本中,您使用 PDO (PHP Data Object)库连接到 MySQL 数据库,并使用您在步骤 1 中创建的数据库凭证。

PDO 是一个轻量级的接口,用于连接到数据库。数据访问层更便携式,可以在不同的数据库上工作,只需少量编写代码。 PDO 具有更大的安全性,因为它支持 prepared statements - 一个功能,可让查询以安全的方式运行更快。

然后,您指示 PDO API 执行从产品中选择 *声明,并在 HTML 表中列出产品,而无需页面化。字符串$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);确保数据类型返回,因为它们出现在数据库中。这意味着 PDO 将返回product_id作为整数和product_name作为字符串。

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

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

你会看到一个页面与你的产品表。

MySQL Records Displayed with a PHP script - No Pagination

您的PHP脚本按预期工作;列出所有产品在一个页面上. 如果你有成千上万的产品,这将导致一个漫长的循环,因为产品从数据库中提取并在PHP页面上渲染。

为了克服这种限制,您将修改PHP脚本,并在表的底部添加MySQLLIMIT条款和一些导航链接,以增加页面化功能。

步骤3 – 使用PHP实现页面化

在这个步骤中,你的目标是将测试数据分成多个可管理的页面,这不仅会提高可读性,还会更有效地利用服务器的资源。

要做到这一点,你将执行MySQLLIMIT条款.在将此添加到脚本之前,让我们看看MySQLLIMIT语法的例子:

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

该LIMIT条款使用两个参数,如本声明的末尾所示。offset值是要在第一行之前跳过的记录的数量。

要测试页面化,您将显示每页三个记录. 要获得页面的总数,您必须将表中的总记录分为您想要显示每页的行. 然后,您将结果值旋转到最接近的整数,使用PHP Ceil函数如下PHP代码片段示例所示:

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

以下是 PHP 脚本的修改版本,包含完整的页面编码. 要包含页面编码和导航代码,请打开 /var/www/html/pagination_test.php 文件:

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

然后将以下突出代码添加到您的文件中:

 1[label /var/www/html/pagination_test.php]
 2<?php
 3
 4try {
 5  $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");
 6  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 7  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
 8
 9  /* Begin Paging Info */
10
11  $page = 1;
12
13  if (isset($_GET['page'])) {
14    $page = filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_INT);
15  }
16
17  $per_page = 3;
18
19  $sqlcount = "select count(*) as total_records from products";
20  $stmt = $pdo->prepare($sqlcount);
21  $stmt->execute();
22  $row = $stmt->fetch();
23  $total_records = $row['total_records'];
24
25  $total_pages = ceil($total_records / $per_page);
26
27  $offset = ($page-1) * $per_page;
28
29  /* End Paging Info */
30
31  $sql = "select * from products limit :offset, :per_page";
32  $stmt = $pdo->prepare($sql);
33  $stmt->execute(['offset'=>$offset, 'per_page'=>$per_page]);
34
35  echo "<table border='1' align='center'>";
36
37  while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {
38    echo "<tr>";
39    echo "<td>".$row['product_id']."</td>";
40    echo "<td>".$row['product_name']."</td>";
41    echo "</tr>";
42  }
43
44  echo "</table>";
45
46  /* Begin Navigation */
47
48  echo "<table border='1' align='center'>";
49
50  echo "<tr>";
51
52  if ($page-1 >= 1) {
53    echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page - 1).">Previous</a></td>";
54  }
55
56  if ($page+1 <= $total_pages) {
57    echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page + 1).">Next</a></td>";
58  }
59
60  echo "</tr>";
61
62  echo "</table>";
63
64  /* End Navigation */
65
66} catch(PDOException $e) {
67  echo $e->getMessage();
68}
69
70?>

在您的文件中,您已经使用了额外的参数来执行页面编辑:

$_GET(‘page’) 变量:本变量在你的脚本中保持当前页面。在页面之间移动时,你的脚本会使用 $_GET(‘page’) 变量检索一个 URL 参数,名为 page.
$per_page: 这个变量包含你想要显示每页的最大记录。在你的情况下,你想在每个页面上列出三个产品
$total_records: 在你列出产品之前,你正在运行一个 SQL 陈述来获得目标表中的总记录,并将其分配给 $total_records 变量。 $setoff 变量: 这是在跳过第一行之前

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

1http://your_server_ip/pagination_test.php

您现在将看到页面底部的一些导航按钮. 在第一页,您将不会收到一个 ** 之前** 按钮. 同样的情况发生在最后一页,您将不会得到一个 ** 下一页** 按钮. 另外,请注意,当您访问每个页面时,页面 URL 参数如何变化。

MySQL Records Displayed with a PHP script with Pagination - Page 1

MySQL Records Displayed with a PHP script with Pagination - Page 2

Final page of MySQL Records Displayed with a PHP script with Pagination - Page 4

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

 1[label /var/www/html/pagination_test.php]
 2. . .
 3if( $page-1>=1) {
 4  echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Previous</a></td>";
 5}
 6
 7if( $page+1<=$total_pages) {
 8  echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Next</a></td>";
 9}
10. . .

在这里,$page变量代表当前的页面号码,然后,以获取前一页,代码会从变量中减去1。所以,如果你在第2页面上,公式(2-1)会给你一个结果1,这将是链接中出现的前一页。

同样,要进入下一个页面,你将一个添加到$page变量中,你还必须确保我们附加到pageURL参数的$page结果不超过你在PHP代码中计算的页面总数。

此时,您的PHP脚本正在使用页面化,您可以实施MySQLLIMIT条款,以便更好地导航记录。

结论

在本教程中,您在 Ubuntu 18.04 服务器上实现了 MySQL 中的页面化,您可以使用这些步骤使用较大的记录集使用 PHP 脚本来包括页面化。

从这里开始,您可以考虑进一步优化您的数据库和其他数据库任务,使用这些教程:

在 Ubuntu 18.04 上如何优化 MySQL 使用 Query Cache(https://andsky.com/tech/tutorials/how-to-optimize-mysql-with-query-cache-on-ubuntu-18-04) *(https://andsky.com/tech/tutorials/introduction-to-queries-mysql) *(https://www.digitalocean.com/community/tutorial_series/how-to-troubleshoot-issues-in-mysql)