《MySQL 入门教程》第 35 篇 PHP 访问 MySQL

本篇我们介绍如何利用 PHP 数据对象接口连接和操作 MySQL 数据库,包括创建和删除表、执行数据的增删改查操作以及事务处理等。

PHP 数据对象(PDO)提供了一个统一的 API 接口,用于在 PHP 程序中访问各种数据库,包括 MySQL。不同数据库可以基于该标准实现特定的驱动程序,也可以扩展专有的功能。通过 PHP PDO 连接数据库的示意图如下:

PDO

📝在 PHP 程序中访问 MySQL 数据库的另一种方法就是使用 mysqli 增强版扩展

35.1 连接数据库

如果使用 LAMPWAMPMAMP 工具栈安装集成开发环境,默认已经启用了 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 表中包含了以下几个步骤:

  1. 创建一个新的 PDO 实例,通过 connect() 方法连接到数据库;
  2. 构造一个 INSERT 语句,可以通过占位符(例如 :param1)传递参数;
  3. 调用 PDO 对象的 prepare() 方法返回一个预编译语句对象 PDOStatement;
  4. 调用 PDOStatement 对象的 bindValue() 方法为参数传递数值;
  5. 最后,调用 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 应用中查询表中的数据包含了以下几个步骤:

  1. 创建一个新的 PDO 实例,通过 connect() 方法连接到数据库;
  2. 调用 PDO 对象的 query() 方法,传入一个查询语句文本,返回一个 PDOStatement 对象;
  3. 调用 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 中通过一个表格显示用户信息,返回的页面如下:

getuser

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 使用手册。

不剪发的Tony老师 CSDN认证博客专家 数据库架构师
毕业于北京航空航天大学,十多年数据库管理与开发经验,拥有OCP以及RHCE证书。目前在一家全球性的游戏公司从事数据库架构设计和开发,CSDN学院签约讲师以及GitChat专栏作者。
©️2020 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页
实付 69.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值