《PostgreSQL 开发指南》第 28 篇 PHP 访问 PostgreSQL

  • BUAA_dxy

    2024-06-11 21:59:01
  • 数据库开发技术
  • 原创

PHP（Hypertext Preprocessor）是一种流行的通用脚本语言，具有快速、灵活和实用等特性，特别适合于 Web 应用和网站开发。

PHP 数据对象（PDO）定义了一个统一的 API 接口，用于在 PHP 程序中访问关系型数据库。不同数据库可以基于该标准实现特定于的驱动程序，也可以实现专有的扩展功能。通过 PHP PDO 连接数据库的示意图如下：


本篇我们来介绍如何利用 PHP 数据对象接口连接和操作 PostgreSQL 数据库，包括创建和删除表、执行数据的 CRUD 操作等。

?除此之外，在 PHP 中访问 PostgreSQL 数据库的另一种方法就是使用原生的 PostgreSQL 扩展。

连接数据库

如果使用 LAPP、WAPP、MAPP 工具栈安装集成开发环境，默认已经启用了 PDO_PGSQL 驱动；如果是单独安装的 PHP 环境，需要在 php.ini 配置文件中增加该驱动。可以增加或者去掉下面两行配置项前面的注释符号（;）：

extension_dir = "php-install-path/ext"
extension=pdo_pgsql

首先在 web 根目录创建一个数据库的配置文件 db.ini：

host=192.168.56.104
port=5432
database=hrdb
user=tony
password=tony

将以上内容替换成你的数据库信息。然后创建一个用于连接数据库的文件 connection.php：


/**
 * 数据库连接
 */
class Connection {

/**
     * Connection
     * @var type 
     */
private static $conn;

/**
     * 连接数据库并返回一个 PDO 对象实例
     * @return PDO
     * @throws Exception
     */
public function connect() {

// 读取数据库配置参数文件
$params = parse_ini_file('db.ini');
if ($params === false) {
throw new Exception("读取数据库配置参数文件错误！");
}
// connect to the postgresql database
$conStr = sprintf("pgsql:host=%s;port=%d;dbname=%s;user=%s;password=%s", 
$params['host'], 
$params['port'], 
$params['database'], 
$params['user'], 
$params['password']);

$pdo = new PDO($conStr);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

return $pdo;
}

/**
     * 返回连接对象实例
     * @return type
     */
public static function get() {
if (null === static::$conn) {
static::$conn = new static();
}

return static::$conn;
}

}

我们创建了一个 Connection 类，用于连接数据库；其中 connect() 方法通过读取配置文件，创建并返回了一个 PDO 对象。

接下来创建一个测试连接的文件 test_connection.php：



require 'connection.php';

try {
Connection::get()->connect();
echo '成功连接 PostgreSQL 数据库服务器！';
} catch (PDOException $e) {
echo $e->getMessage();
}

通过 require 引用前面的 connection.php，然后调用静态方法 connect() 获取连接。在浏览器中输入该文件的访问地址，页面显示以下信息表示连接数据库成功：

成功连接 PostgreSQL 数据库服务器！

创建和删除表

PDO 对象的 exec() 方法可以用于执行 SQL 命令，创建各种数据库对象。

我们先创建一个新的代码文件 create_table.php：



require 'connection.php';

try {
$pdo = Connection::get()->connect();
echo '成功连接 PostgreSQL 数据库服务器！
';

$sql = 'create table if not exists users (
              id serial primary key,
              name character varying(10) not null unique,
              created_at timestamp not null 
            );';

$pdo->exec($sql);
echo '成功创建表 users！';

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

在浏览器中打开该文件地址，返回以下信息：

成功连接 PostgreSQL 数据库服务器！
成功创建表 users！

此时，PostgreSQL 数据库中多了一个 users 表。在完成本篇的测试内容之后，我们可以将 $sql 的内容换成 DROP TABLE 语句，从而删除users 表。

插入数据

在 PHP 应用中插入数据到 PostgreSQL 表中包含了以下几个步骤：

  1. 创建一个新的 PDO 实例，通过 connect() 方法连接到数据库；

  2. 构造一个 INSERT 语句，可以通过占位符（例如 :param1）传递参数；

  3. 调用 PDO 对象的 prepare() 方法返回一个预编译语句对象 PDOStatement；

  4. 调用 PDOStatement 对象的 bindValue() 方法为参数传递数值；

  5. 最后，调用 PDOStatement 对象的 execute() 方法执行 INSERT 语句。

我们创建一个新的 PHP 文件 insert_user.php：


require 'connection.php';

try {
$pdo = Connection::get()->connect();
echo '成功连接 PostgreSQL 数据库服务器！
';

// 预编译插入语句
$sql = 'INSERT INTO users(name, created_at) VALUES(:name,:createdAt)';
$stmt = $pdo->prepare($sql);

// 绑定参数值
$name = 'tony';
$createdAt = '2020-06-03 11:30:16';
$stmt->bindValue(':name', $name);
$stmt->bindValue(':createdAt', $createdAt);

// 执行插入操作
$stmt->execute();

// 返回id
$id = $pdo->lastInsertId('users_id_seq');
echo '插入数据成功，用户id：' . $id . '
';

// 绑定参数值
$name = 'david';
$createdAt = '2020-06-01 20:11:11';
$stmt->bindValue(':name', $name);
$stmt->bindValue(':createdAt', $createdAt);

// 执行插入操作
$stmt->execute();

// 返回id
$id = $pdo->lastInsertId('users_id_seq');
echo '插入数据成功，用户id：' . $id . '
';

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

我们执行了两次插入操作，并且通过 PDO 对象的 lastInsertId() 方法返回了插入数据的 id；users_id_seq 是 users 表中自增字段 id 对应的序列名称。

在浏览器中打开以上文件地址，返回信息如下：

成功连接 PostgreSQL 数据库服务器！
插入数据成功，用户id：1
插入数据成功，用户id：2

如果想要在页面显示 users 表中的数据，需要执行查询操作。

查询数据

在 PHP 应用中查询表中的数据包含了以下几个步骤：

  1. 创建一个新的 PDO 实例，通过 connect() 方法连接到数据库；

  2. 调用 PDO 对象的 query() 方法，传入一个查询语句文本，返回一个 PDOStatement 对象；

  3. 调用 PDOstatement 对象的 fetch() 方法从查询结果中返回下一行数据。该方法的 fetch_style 参数决定了返回结果的方式。

下面我们创建一个新的文件 get_users.php，查询并显示用户信息：


require 'connection.php';

try {
$pdo = Connection::get()->connect();

// 执行查询语句
$stmt = $pdo->query('SELECT id, name, created_at FROM users');
$users = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$users[] = [
'id' => $row['id'],
'name' => $row['name'],
'createdAt' => $row['created_at']
];
}

} catch (PDOException $e) {
echo $e->getMessage();
}
?>
DOCTYPE html>
<html>
<head>
<style>
      table {
        font-family: arial, sans-serif;
        border-collapse: collapse;
        width: 100%;
}

      td, th {
        border: 1px solid #dddddd;
        text-align: left;
        padding: 8px;
}

      tr:nth-child(even) {
        background-color: #dddddd;
}
style>
<title>查询用户数据title>
head>
<body>
<h2>用户列表h2>
<table>
<thead>
<tr>
<th>IDth>
<th>Nameth>
<th>CreatedAtth>
tr>
thead>
<tbody>
php foreach ($users as $user) : ?>
<tr>
<td>php echo htmlspecialchars($user['id']) ?>td>
<td>php echo htmlspecialchars($user['name']); ?>td>
<td>php echo htmlspecialchars($user['createdAt']); ?>td>
tr>
php endforeach; ?>
tbody>
table>
body>
html>

首先，通过 fetch() 方法返回用户信息；参数 PDO::FETCH_ASSOC 表示返回一个数组，下标是字段的名称。然后在 HTML 中通过一个表格显示用户信息，返回的页面如下：

修改数据

修改数据的步骤和插入数据类似，只是将 INSERT 语句改成了 UPDATE 语句。我们创建一个新的文件 update_user.php：



require 'connection.php';

try {
$pdo = Connection::get()->connect();

// 预编译更新语句
$sql = 'UPDATE users ' 
. 'SET name = :name '
. 'WHERE id = :id';
$stmt = $pdo->prepare($sql);

// 绑定参数值
$name = 'tom';
$id = 1;
$stmt->bindValue(':name', $name);
$stmt->bindValue(':id', $id);

// 执行更新操作
$stmt->execute();

// 返回更新的行数
$rowCount = $stmt->rowCount();
echo '更新数据成功，更新记录数：' . $rowCount . '
';

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

在代码的最后通过 PDOStatement 对象的 rowCount() 方法返回更新的记录数。在浏览器中打开以上文件地址，返回信息如下：

更新数据成功，更新记录数：1

我们也可以通过 get_users.php 查看数据的变化。

删除数据

通过 PHP 删除数据的步骤和更新数据几乎相同。我们创建一个新的文件 delete_user.php：



require 'connection.php';

try {
$pdo = Connection::get()->connect();

// 预编译删除语句
$sql = 'DELETE FROM users '
. 'WHERE id = :id';
$stmt = $pdo->prepare($sql);

// 绑定参数值
$id = 1;
$stmt->bindValue(':id', $id);

// 执行删除操作
$stmt->execute();

// 返回删除的行数
$rowCount = $stmt->rowCount();;
echo '删除数据成功，删除记录数：' . $rowCount . '
';

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

在浏览器中打开以上文件地址，返回信息如下：

删除数据成功，删除记录数：1

如果再次执行 get_users.php，用户列表中只返回一个

管理事务

默认情况下，PostgreSQL 使用自动提交方式；也就是对于任何 SQL 语句，都会自动执行一次 COMMIT 操作。因此，前面的示例中我们没有进行任何的事务控制。

不过，在 PHP 应用中可以手动控制事务的提交和回滚。通过 PDO 对象的 beginTransaction()、commit() 和 rollback() 方法开始、提交和回滚一个事务。

我们创建一个新的文件 transacion.php：



require 'connection.php';

try {
$pdo = Connection::get()->connect();
echo '成功连接 PostgreSQL 数据库服务器！
';

$pdo->beginTransaction();

// 预编译插入语句
$sql = 'INSERT INTO users(name, created_at) VALUES(:name,:createdAt)';
$stmt = $pdo->prepare($sql);

// 绑定参数值
$name = 'bob';
$createdAt = '2020-06-04 22:00:00';
$stmt->bindValue(':name', $name);
$stmt->bindValue(':createdAt', $createdAt);

// 执行插入操作
$stmt->execute();

// 返回id
$id = $pdo->lastInsertId('users_id_seq');
echo '插入数据成功，用户id：' . $id . '
';

// 预编译更新语句
$sql = 'UPDATE users ' 
. 'SET name = :name '
. 'WHERE id = :id';
$stmt = $pdo->prepare($sql);

// 绑定参数值
$name = 'david';
$stmt->bindValue(':name', $name);
$stmt->bindValue(':id', $id);

// 执行更新操作
$stmt->execute();

// 返回更新的行数
$rowCount = $stmt->rowCount();;
echo '更新数据成功，更新记录数：' . $rowCount . '
';

$pdo->commit();
} catch (PDOException $e) {
$pdo->rollBack();
echo '执行操作失败，回滚事务！' . '
';
echo $e->getMessage();
}

首先，通过 beginTransaction() 方法开始一个事务；然后分别执行插入和更新操作，成功后提交事务；如果出现异常，回滚事务。执行该文件返回以下信息：

成功连接 PostgreSQL 数据库服务器！
插入数据成功，用户id：4
执行操作失败，回滚事务！
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "users_name_key" DETAIL: Key (name)=(david) already exists.

错误消息显示 name 字段违反了唯一约束，因为 david 已经存在。此时，整个事务都被回滚，插入的记录也不会存在。

调用存储过程

最后，我们介绍一下如何在 PHP 中调用 PostgreSQL 存储过程和函数。我们在 PostgreSQL 创建一个存储过程 add_user：

CREATE OR REPLACE PROCEDURE add_user(pv_name varchar, pd_created_at timestamp)
AS $$
BEGIN
insert into users(name, created_at)
values (pv_name, pd_created_at);
END; $$
LANGUAGE plpgsql;

add_user 用于增加一个用户。然后我们通过 PHP 调用该存储过程，创建一个新的文件 stored_procedure.php：



require 'connection.php';

try {
$pdo = Connection::get()->connect();

// 预编译语句
$sql = 'call add_user(:name,:createdAt)';
$stmt = $pdo->prepare($sql);

// 绑定参数值
$name = 'anne';
$createdAt = '2020-06-04 08:08:08';
$stmt->bindValue(':name', $name);
$stmt->bindValue(':createdAt', $createdAt);

// 执行操作
$stmt->execute();

// 返回id
$id = $pdo->lastInsertId('users_id_seq');
echo '插入数据成功，用户id：' . $id . '
';

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

我们使用 call 命令调用存储过程，执行插入操作。在浏览器中输入该文件返回以下信息：

插入数据成功，用户id：6

此时 users 表中增加了一条记录。

如果调用的是函数，可以通过 fetch()、fetchColumn() 和 fetchAll() 方法获取函数返回值。具体可以参考 PDO 使用手册。

请使用浏览器的分享功能分享到微信等