本篇我们介绍如何利用 PHP 数据对象接口连接和操作 MySQL 数据库,包括创建和删除表、执行数据的增删改查操作以及事务处理等。
PHP 数据对象(PDO)提供了一个统一的 API 接口,用于在 PHP 程序中访问各种数据库,包括 MySQL。不同数据库可以基于该标准实现特定的驱动程序,也可以扩展专有的功能。通过 PHP PDO 连接数据库的示意图如下:
📝在 PHP 程序中访问 MySQL 数据库的另一种方法就是使用 mysqli 增强版扩展。
35.1 连接数据库
如果使用 LAMP、WAMP、MAMP 工具栈安装集成开发环境,默认已经启用了 PDO_MYSQL 驱动;如果是单独安装的 PHP 环境,需要在 php.ini 配置文件中增加该驱动,可以去掉下面两行配置项前面的注释符号(;):
extension_dir = "php-install-path/ext"
extension=pdo_mysql
首先在 web 根目录创建一个数据库的配置文件 db.ini:
host=192.168.56.104
port=3306
database=hrdb
user=tony
password=tony
将以上内容替换成你自己的数据库信息,然后创建一个用于连接数据库的文件 mysql_connection.php:
<?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 MySQL database
$conStr = sprintf("mysql: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:
<?php
require 'mysql_connection.php';
try {
Connection::get()->connect();
echo '成功连接 MySQL 数据库服务器!';
} catch (PDOException $e) {
echo $e->getMessage();
}
?>
通过 require 引用前面的 mysql_connection.php,然后调用静态方法 connect() 获取连接。在浏览器中输入该文件的访问地址,页面显示以下信息表示连接数据库成功:
成功连接 MySQL 数据库服务器!
35.2 创建和删除表
PDO 对象的 exec() 方法可以用于执行 SQL 命令,创建各种数据库对象。
我们先创建一个新的代码文件 create_table.php:
<?php
require 'mysql_connection.php';
try {
$pdo = Connection::get()->connect();
echo '成功连接 MySQL 数据库服务器!<br>';
$sql = 'create table if not exists users (
id int auto_increment not null primary key,
name varchar(10) not null unique,
created_at timestamp not null
);';
$pdo->exec($sql);
echo '成功创建表 users!';
} catch (PDOException $e) {
echo $e->getMessage();
}
// 关闭连接
$pdo = null;
?>
在浏览器中打开该文件地址,返回以下信息:
成功连接 MySQL 数据库服务器!
成功创建表 users!
此时,数据库中多了一个 users 表。
在完成本篇的测试内容之后,我们可以将 $sql 的内容换成 DROP TABLE 语句,从而删除 users 表。
35.3 插入数据
在 PHP 应用中插入数据到 MySQL 表中包含了以下几个步骤:
- 创建一个新的 PDO 实例,通过 connect() 方法连接到数据库;
- 构造一个 INSERT 语句,可以通过占位符(例如 :param1)传递参数;
- 调用 PDO 对象的 prepare() 方法返回一个预编译语句对象 PDOStatement;
- 调用 PDOStatement 对象的 bindValue() 方法为参数传递数值;
- 最后,调用 PDOStatement 对象的 execute() 方法执行 INSERT 语句。
我们创建一个新的 PHP 文件 insert_user.php:
<?php
require 'mysql_connection.php';
try {
$pdo = Connection::get()->connect();
echo '成功连接 MySQL 数据库服务器!<br>';
// 预编译插入语句
$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();
echo '插入数据成功,用户id:' . $id . '<br>';
// 绑定参数值
$name = 'david';
$createdAt = '2020-06-01 20:11:11';
$stmt->bindValue(':name', $name);
$stmt->bindValue(':createdAt', $createdAt);
// 执行插入操作
$stmt->execute();
// 返回id
$id = $pdo->lastInsertId();
echo '插入数据成功,用户id:' . $id . '<br>';
} catch (PDOException $e) {
echo $e->getMessage();
}
// 关闭连接
$pdo = null;
?>
我们执行了两次插入操作,并且通过 PDO 对象的 lastInsertId() 方法返回了插入数据的 id。在浏览器中打开以上文件地址,返回信息如下:
成功连接 MySQL 数据库服务器!
插入数据成功,用户id:1
插入数据成功,用户id:2
如果想要在页面显示 users 表中的数据,需要执行查询操作。
35.4 查询数据
在 PHP 应用中查询表中的数据包含了以下几个步骤:
- 创建一个新的 PDO 实例,通过 connect() 方法连接到数据库;
- 调用 PDO 对象的 query() 方法,传入一个查询语句文本,返回一个 PDOStatement 对象;
- 调用 PDOstatement 对象的 fetch() 方法从查询结果中返回下一行数据。该方法的 fetch_style 参数决定了返回结果的方式。
下面我们创建一个新的文件 get_users.php,查询并显示用户信息:
<?php
require 'mysql_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();
}
// 关闭连接
$pdo = null;
?>
<!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>ID</th>
<th>Name</th>
<th>CreatedAt</th>
</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 中通过一个表格显示用户信息,返回的页面如下:
35.5 修改数据
修改数据的步骤和插入数据类似,只是将 INSERT 语句改成了 UPDATE 语句。我们创建一个新的文件 update_user.php:
<?php
require 'mysql_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 . '<br>';
} catch (PDOException $e) {
echo $e->getMessage();
}
// 关闭连接
$pdo = null;
?>
在代码的最后通过 PDOStatement 对象的 rowCount() 方法返回更新的记录数。在浏览器中打开以上文件地址,返回信息如下:
更新数据成功,更新记录数:1
我们也可以通过 get_users.php 查看数据的变化。
35.6 删除数据
通过 PHP 删除数据的步骤和更新数据几乎相同。我们创建一个新的文件 delete_user.php:
<?php
require 'mysql_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 . '<br>';
} catch (PDOException $e) {
echo $e->getMessage();
}
// 关闭连接
$pdo = null;
?>
在浏览器中打开以上文件地址,返回信息如下:
删除数据成功,删除记录数:1
如果再次执行 get_users.php,用户列表中只返回一个
35.7 管理事务
默认情况下,MySQL 使用自动提交方式;也就是对于任何 SQL 语句,都会自动执行一次 COMMIT 操作。因此,前面的示例中我们没有进行任何的事务控制。
不过,在 PHP 应用中可以手动控制事务的提交和回滚。通过 PDO 对象的 beginTransaction()、commit() 和 rollback() 方法开始、提交和回滚一个事务。我们创建一个新的文件 mysql_transacion.php:
<?php
require 'mysql_connection.php';
try {
$pdo = Connection::get()->connect();
echo '成功连接 MySQL 数据库服务器!<br>';
$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();
echo '插入数据成功,用户id:' . $id . '<br>';
// 预编译更新语句
$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 . '<br>';
$pdo->commit();
} catch (PDOException $e) {
$pdo->rollBack();
echo '执行操作失败,回滚事务!' . '<br>';
echo $e->getMessage();
}
// 关闭连接
$pdo = null;
?>
首先,通过 beginTransaction() 方法开始一个事务;然后分别执行插入和更新操作,成功后提交事务;如果出现异常,回滚事务。执行该文件返回以下信息:
成功连接 MySQL 数据库服务器!
插入数据成功,用户id:4
执行操作失败,回滚事务!
ERROR 1062 (23000): Duplicate entry 'david' for key 'users.name'
错误消息显示 name 字段违反了唯一约束,因为 david 已经存在。此时,整个事务都被回滚,插入的记录也不会存在。
35.8 调用存储过程
最后,我们介绍一下如何在 PHP 中调用 MySQL 存储过程和函数。我们在 MySQL 创建一个存储过程 add_user:
DELIMITER $$
CREATE PROCEDURE add_user(pv_name varchar(10), pd_created_at timestamp)
BEGIN
insert into users(name, created_at)
values (pv_name, pd_created_at);
END;
DELIMITER ;
add_user 用于增加一个用户。然后我们通过 PHP 调用该存储过程,创建一个新的文件 stored_procedure.php:
<?php
require 'mysql_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();
echo '插入数据成功,用户id:' . $id . '<br>';
} catch (PDOException $e) {
echo $e->getMessage();
}
// 关闭连接
$pdo = null;
?>
我们使用 call 命令调用存储过程,执行插入操作。在浏览器中输入该文件返回以下信息:
插入数据成功,用户id:6
此时 users 表中增加了一条记录。
如果调用的是函数,可以通过 fetch()、fetchColumn() 和 fetchAll() 方法获取函数返回值。具体可以参考 PDO 使用手册。