怎样使用 PHP 对 PostgreSQL 数据进行分页

介绍

分页是一种从较大的数据集中检索小的、定义的块的方法。 例如,如果您经营一家拥有数千种产品的在线商店,您可以一次展示 50 件商品,并为客户提供导航按钮以在页面之间移动。

在 PostgreSQL 中,您可以使用 LIMITOFFSET 条款。 使用 LIMIT 子句来定义要检索的行数和 OFFSET 关键字来指定从头开始跳过的行数。

先决条件

要完成本 PostgreSQL 分页教程,请确保您具备以下条件。

一个 Ubuntu 20.04 服务器。 一个非 root 用户 sudo 权利。 PostgreSQL 数据库服务器。 Apache 网络服务器和 PHP。 对于本教程,您可以跳过 步骤 – 2. 安装数据库服务器 因为您将使用 PostgreSQL 作为数据库服务器。

1. 安装 PHP 扩展

SSH 到您的服务器并更新包信息索引。

$ sudo apt -y update

安装 pdo_pgsql 司机。 PHP 需要此扩展来与 PostgreSQL 服务器通信。

$ sudo apt install -y php-pgsql

重新启动 Apache 以加载新更改。

$ sudo systemctl restart apache2

2. 创建测试数据库

登录到您的 PostgreSQL 服务器作为 postgres 用户。

$ sudo -u postgres psql

输入密码并按 ENTER 继续。

创建一个 test_shop 数据库。

postgres=# CREATE DATABASE test_shop;

切换到新的 test_shop 数据库。

postgres=# c test_shop;

创建一个 products 桌子。

test_shop=# CREATE TABLE products (
            product_id SERIAL PRIMARY KEY,
            product_name VARCHAR (50),
            retail_price NUMERIC
            );

插入一些数据到 products 桌子。

test_shop=# INSERT INTO products(product_name, retail_price) VALUES ('RAIN JACKET', 55.60 );
            INSERT INTO products(product_name, retail_price) VALUES ('LEATHER BELT', 9.63);
            INSERT INTO products(product_name, retail_price) VALUES ('WOOLEN SWEATER', 55.32);
            INSERT INTO products(product_name, retail_price) VALUES ('JUMP SUIT', 18.30 );
            INSERT INTO products(product_name, retail_price) VALUES ('JEANS HANGER', 2.35);
            INSERT INTO products(product_name, retail_price) VALUES ('KITCHEN TOWEL', 1.89);
            INSERT INTO products(product_name, retail_price) VALUES ('PEN ORGANIZER', 3.65);
            INSERT INTO products(product_name, retail_price) VALUES ('RECHARGEABLE TORCH', 22.60);
            INSERT INTO products(product_name, retail_price) VALUES ('COTTON SWABS', 1.23);
            INSERT INTO products(product_name, retail_price) VALUES ('TOOTH PICKS', 2.20);
            INSERT INTO products(product_name, retail_price) VALUES ('USB CABLE', 3.50);
            INSERT INTO products(product_name, retail_price) VALUES ('QUICK CHARGER', 35.60);

通过执行一个确保记录到位 SELECT 声明反对 products 桌子。

test_shop=# SELECT
            product_id,
            product_name,
            retail_price
            FROM products;

确认下面的输出。

 product_id |    product_name    | retail_price
------------+--------------------+--------------
          1 | RAIN JACKET        |        55.60
          2 | LEATHER BELT       |         9.63
          3 | WOOLEN SWEATER     |        55.32
          4 | JUMP SUIT          |        18.30
          5 | JEANS HANGER       |         2.35
          6 | KITCHEN TOWEL      |         1.89
          7 | PEN ORGANIZER      |         3.65
          8 | RECHARGEABLE TORCH |        22.60
          9 | COTTON SWABS       |         1.23
         10 | TOOTH PICKS        |         2.20
         11 | USB CABLE          |         3.50
         12 | QUICK CHARGER      |        35.60
(12 rows)

退出 PostgreSQL。

test_shop=# q

3. 创建一个 PHP 脚本

要显示分页的 PostgreSQL 数据,您需要从 PHP 脚本连接到您的数据库。 然后,您将在标准 HTML 网页上显示数据。

您的 PHP 文件应该位于您的网络服务器的根目录中。 要创建它,请打开一个新的 /var/www/html/products.php 文件使用 nano.

$ sudo nano /var/www/html/products.php

定义一个新的 HTML 文档并包含一个 title, 一种 head, 和 body 标签。 然后,定义一个 HTML table. 您将使用它来列出数据库中的产品。

<html>
  <head>
    <title>Paginated PostgreSQL Data</title>
  </head>
  <body>
    <h1 align = 'center'> Products List </h1>
    <table align='center' border="1px">
      <tr align='left'>
        <th>Id</th>
        <th>Product Name</th>
        <th>Retail Price</th>
      </tr>

在下面创建 PHP 内容以连接到您之前创建的数据库。 代替 EXAMPLE_PASSWORD 使用正确的密码 postgres 用户。

    <?php

        try {
            $db_name="test_shop";
            $db_user="postgres";
            $db_password = 'EXAMPLE_PASSWORD';
            $db_host="localhost";

            $pdo = new PDO('pgsql:host=" . $db_host . "; dbname=" . $db_name, $db_user, $db_password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

定义一个 $sql_count 下面的声明。 使用 PDO prepareexecute 获取产品总数并将结果放在一个 $count 多变的。

            $sql_count = "select count(*) as count
                          from products
                         ';

            $stmt = $pdo->prepare($sql_count);
            $stmt->execute();
            $row_count = $stmt->fetch();
            $count     = $row_count['count'];

在此脚本中,您将使用 page 网址变量。 单击导航链接后,该值会更改。 使用下面的代码来检索 $_GET['page'] 变量仅在设置时。 否则,使用 1 作为默认 $page 价值。

        if (isset($_GET['page'])) {
            $page = $_GET['page'];
        } else {
            $page = 1;
        }

分配变量 $per_page 一个值; 这控制了您要在每个页面上显示的产品总数。 对于本教程,请使用 5. 当您在 Web 浏览器上访问此脚本时,您会看到 5 每页上的产品。 要指示 PHP 脚本应该期望的总页数,请使用公式 $total_pages = ceil($count / $per_page);. 这将舍入任何小数,因为页面中不可能有分数。

在本指南中,您的页数($total_pages) 应该 3 自从你的 products 表有 12 记录。 如果你分 12 经过 5, 你会得到 2.4. 用 PHP 对这个数字进行四舍五入后 ceil 函数,你得到 3 页。

接下来,定义一个 $offset 多变的。 这表示执行时要跳过的行数 $sql 陈述。 使用公式确定此变量的值 ($page - 1) * $per_page;. 对结果进行分页 products 表,包括 $per_page$offset 使用语法的 SQL 字符串中的变量 ...limit ' . $per_page . ' offset ' . $offset.

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

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

        $sql="select
                product_id,
                product_name,
                retail_price
                from products
                limit " . $per_page . ' offset ' . $offset ;

准备上面的SQL命令并执行。 当。。。的时候 sql 字符串完成后,它应该根据当前的情况执行以下命令 page.

第 1 页: /products.php?page=1

‘从产品限制5偏移0中选择productid,productname,retail_price;

第2页: /products.php?page=2

‘从产品限制 5 偏移 5 中选择产品 ID、产品名称、零售价格;

第 3 页: /products.php?page=3

‘从产品限制 5 偏移 10 中选择产品 ID、产品名称、零售价格;

输入下面的代码以遍历记录并将它们列在 HTML 表中。

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

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

在新的表格行中回显当前页码和总页数。

        echo "<tr align='center'>"
                . "<td colspan='3'>Page " . $page . " of " . $total_pages . "</td>"
             . "</tr>";

填充 $pagination_urls 多变的。 这是用于在页面之间移动的可点击导航链接列表。 这 First 页面和 Last 页面链接是强制性的,将在所有页面上激活。

        $pagination_urls="";

        $pagination_urls .= "<a href="https://www.vultr.com/products.php?page=1">First </a>";

显示一个 Previous 页面链接,从页面获取当前页面的值 $page 变量并将其递减一次。 使用 PHP if (...) {...} else {...} 通过删除链接来禁用链接 href 如果你已经在 First 页。

        if ($page != 1) {
            $pagination_urls .= "&nbsp;&nbsp;<a href="https://www.vultr.com/products.php?page=". ($page - 1) . "">Previous</a>";
        } else {
            $pagination_urls .= "&nbsp;&nbsp;<a>Previous</a>";
        }

获取链接 Next 页,增加 $page 变量一次。 另外,禁用 Next 页面链接,如果你已经在 Last 页。

        if ($page != $total_pages) {
            $pagination_urls .= "&nbsp;&nbsp;<a href="https://www.vultr.com/products.php?page=". ($page + 1) . "">Next</a>";
        } else {
            $pagination_urls .= "&nbsp;&nbsp;<a>Next</a>";
        }

        $pagination_urls .= "&nbsp;&nbsp;<a href="https://www.vultr.com/products.php?page=" . $total_pages ."">Last</a>";

最后,将分页 URL 回显到一个 <td> 标记并关闭 php, table, body, 和 html 标签。

            echo "<tr align='center'>"
                    . "<td colspan='3'>" . $pagination_urls . "</td>"
                 . "</tr>";

        } catch (PDOException $e) {
            echo 'Database error.' . $e->getMessage();
        }
    ?>
    </table>
  </body>
</html>

完成后,您的 PHP 文件应类似于以下内容。

<html>
  <head>
    <title>Paginated PostgreSQL Data</title>
  </head>
  <body>
    <h1 align = 'center'> Products List </h1>
    <table align='center' border="1px">
      <tr align='left'>
        <th>Id</th>
        <th>Product Name</th>
        <th>Retail Price</th>
      </tr>

    <?php

        try {
            $db_name="test_shop";
            $db_user="postgres";
            $db_password = 'EXAMPLE_PASSWORD';
            $db_host="localhost";

            $pdo = new PDO('pgsql:host=" . $db_host . "; dbname=" . $db_name, $db_user, $db_password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

            $sql_count = "select count(*) as count
                          from products
                         ';

            $stmt = $pdo->prepare($sql_count);
            $stmt->execute();
            $row_count = $stmt->fetch();
            $count     = $row_count['count'];

            if (isset($_GET['page'])) {
                $page = $_GET['page'];
            } else {
                $page = 1;
            }

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

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

            $sql="select
                    product_id,
                    product_name,
                    retail_price
                    from products
                    limit " . $per_page . ' offset ' . $offset ;


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

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

            echo "<tr align='center'>"
                    . "<td colspan='3'>Page " . $page . " of " . $total_pages . "</td>"
                 . "</tr>";

            $pagination_urls="";

            $pagination_urls .= "<a href="https://www.vultr.com/products.php?page=1">First </a>";


            if ($page != 1) {
                $pagination_urls .= "&nbsp;&nbsp;<a href="https://www.vultr.com/products.php?page=". ($page - 1) . "">Previous</a>";
            } else {
                $pagination_urls .= "&nbsp;&nbsp;<a>Previous</a>";
            }

            if ($page != $total_pages) {
                $pagination_urls .= "&nbsp;&nbsp;<a href="https://www.vultr.com/products.php?page=". ($page + 1) . "">Next</a>";
            } else {
                $pagination_urls .= "&nbsp;&nbsp;<a>Next</a>";
            }

            $pagination_urls .= "&nbsp;&nbsp;<a href="https://www.vultr.com/products.php?page=" . $total_pages ."">Last</a>";

            echo "<tr align='center'>"
                    . "<td colspan='3'>" . $pagination_urls . "</td>"
                 . "</tr>";

        } catch (PDOException $e) {
            echo 'Database error.' . $e->getMessage();
        }
    ?>
    </table>
  </body>
</html>

按 CTRL + X,然后按 Y 和 ENTER 保存文件。 您的 PostgreSQL 分页脚本现已准备好进行测试。

4. 测试 PHP 脚本

在网络浏览器中,访问下面的 URL 并替换 192.0.2.1 使用您服务器的正确公共 IP 地址或域名。

http://192.0.2.1/products.php

你现在应该看到 First 页面显示 5 产品如下图。 从下面的输出中可以看出, 以前的 页面链接已禁用,因为您已经在 First 页。

第 1 页:

点击 下一个 导航到第二页并注意 Web 浏览器上的 URL 如何更改以包含 page 范围。

第2页:

第2页

再次点击 下一个 要么 最后的 导航到 Last 页。 这 下一个 此页面上的链接已禁用,因为您已经在 Last 页。

第 3 页:

第 3 页

以上输出确认您的 PHP 脚本现在按预期工作。

结论

在本指南中,您已经创建了一个示例数据库和一个表。 然后,您用示例记录填充了表,并使用 PostgreSQL 编写了一个 PHP 脚本来分页数据 LIMITOFFSET 条款。 在处理许多记录时,使用本指南中的语法对数据库数据进行分页。

注:本教程在Vultr VPS上测试通过,如需部署请前往Vultr.com