使用PDO连接数据库

PDO是一个高性能和积极维护的数据库扩展，与特定数据库的扩展相比，它具有独特的优势。它有一个通用的应用编程接口（API），可以兼容几乎十几种不同的关系型数据库管理系统（RDBMS）。学习如何使用这个扩展将节省您试图掌握同等的单个数据库扩展命令子集的时间。

PDO又分为四个主要类，如下表所示：

类 功能
PDO 保持与数据库的实际连接，并处理更底层功能，如事务支持。
PDOStatement 处理结果
PDOException 数据库特有的异常
PDODriver 与各数据库进行通信

如何做…

1.通过创建 PDO 实例来设置数据库连接。

  1. 您需要构建一个数据源名称（DSN）。DSN中包含的信息根据使用的数据库驱动程序而不同。举个例子，这里是一个用于连接到MySQL数据库的DSN。
  1. $params = [
  2.   'host' => 'localhost',
  3.   'user' => 'test',
  4.   'pwd'  => 'password',
  5.   'db'   => 'php7cookbook'
  6. ];
  8. try {
  9.   $dsn  = sprintf('mysql:host=%s;dbname=%s',
  10.   $params['host'], $params['db']);
  11.   $pdo  = new PDO($dsn, $params['user'], $params['pwd']);
  12. } catch (PDOException $e) {
  13.   echo $e->getMessage();
  14. } catch (Throwable $e) {
  15.   echo $e->getMessage();
  16. }

3.而 SQlite 一个更简单的扩展，只需要以下命令。

  1. $params = [
  2.   'db'   => __DIR__ . '/../data/db/php7cookbook.db.sqlite'
  3. ];
  4. $dsn  = sprintf('sqlite:' . $params['db']);
  1. 而 PostgreSQL 则在DSN中直接包含了用户名和密码。
  1. $params = [
  2.   'host' => 'localhost',
  3.   'user' => 'test',
  4.   'pwd'  => 'password',
  5.   'db'   => 'php7cookbook'
  6. ];
  7. $dsn  = sprintf('pgsql:host=%s;dbname=%s;user=%s;password=%s', 
  8.                $params['host'], 
  9.                $params['db'],
  10.                $params['user'],
  11.                $params['pwd']);

5.DSN还可以包含特定于服务器的指令，例如 unix_socket，如下例所示。

  1. $params = [
  2.   'host' => 'localhost',
  3.   'user' => 'test',
  4.   'pwd'  => 'password',
  5.   'db'   => 'php7cookbook',
  6.   'sock' => '/var/run/mysqld/mysqld.sock'
  7. ];
  9. try {
  10.   $dsn  = sprintf('mysql:host=%s;dbname=%s;unix_socket=%s', 
  11.                   $params['host'], $params['db'], $params['sock']);
  12.   $opts = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
  13.   $pdo  = new PDO($dsn, $params['user'], $params['pwd'], $opts);
  14. } catch (PDOException $e) {
  15.   echo $e->getMessage();
  16. } catch (Throwable $e) {
  17.   echo $e->getMessage();
  18. }

{% hint style=”info” %} 将创建PDO实例的语句封装在 try {} catch {} 块中。在失败的情况下，捕获 PDOException来获取数据库的特定信息。抓取Throwable来处理错误或其他异常。将PDO错误模式设置为 PDO::ERRMODE_EXCEPTION以 获得最佳效果。关于错误模式的更多细节请参见步骤8。

在PHP 5中，如果PDO对象不能被构造（例如，当使用无效参数时），实例被分配一个NULL值。在 PHP 7 中，会抛出一个 Exception。如果把PDO对象的构造包在try {} catch {}块中，并且PDO::ATTR_ERRMODE设置为PDO::ERRMODE_EXCEPTION，就可以捕捉和记录这样的错误，而不需要测试NULL。 {% endhint %}

6.使用 PDO::query() 发送一个SQL命令。返回一个 PDOStatement 实例，您可以根据它来获取结果。在这个例子中，我们正在寻找按ID排序的前20位客户。

  1. $stmt = $pdo->query(
  2. 'SELECT * FROM customer ORDER BY id LIMIT 20');

{% hint style=”info” %} PDO还提供了一个便利的方法 PDO::exec()，它不返回结果迭代，只返回受影响的行数。这个方法最好用于管理操作，比如 ALTER TABLE，DROP TABLE等。 {% endhint %}

7.遍历 PDOStatement 实例来处理结果。设置获取模式为 PDO::FETCH_NUM或 PDO::FETCH_ASSOC，以数字或关联数组的形式返回结果。在这个例子中，我们使用一个 while() 循环来处理结果。当最后一个结果被获取后，结果是一个布尔值 FALSE，结束循环。

  1. while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  2.   printf('%4d | %20s | %5s' . PHP_EOL, $row['id'], 
  3.   $row['name'], $row['level']);
  4. }

{% hint style=”info” %} PDO获取操作涉及游标，这个游标定义了迭代的方向（即正向或反向）。PDOStatement::fetch() 的第二个参数可以是 PDO::FETCHORI_* 常量中的任何一个。游标方向包括 prior、first、last、absolute 和 relative。默认的游标方向是 PDO::FETCH_ORI_NEXT。 {% endhint %}

8.将获取模式设置为 PDO::FETCH_OBJ，以 stdClass 实例的形式返回结果。这里您会注意到，while()循环利用了 PDO::FETCH_OBJ 的获取模式。请注意，printf() 语句引用的是对象属性，而前面的例子引用的是数组元素。

  1. while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
  2.   printf('%4d | %20s | %5s' . PHP_EOL, 
  3.   $row->id, $row->name, $row->level);
  4. }

9.如果你想在处理查询时创建一个特定类的实例，请将获取模式设置为 PDO::FETCH_CLASS。您还必须有类的定义，PDO::query() 应该设置类名。在下面的代码片段中可以看到，我们定义了一个名为 Customer的类，具有公共属性 $id、$name和$level。属性需要是公共的，这样取值注入才能正常工作。

  1. class Customer
  2. {
  3.   public $id;
  4.   public $name;
  5.   public $level;
  6. }
  8. $stmt = $pdo->query($sql, PDO::FETCH_CLASS, 'Customer');

10.当获取对象时，步骤5中所示的技术的更简单的替代方法是使用 PDOStatement::fetchObject()。

  1. while ($row = $stmt->fetchObject('Customer')) {
  2.   printf('%4d | %20s | %5s' . PHP_EOL, 
  3.   $row->id, $row->name, $row->level);
  4. }

11.您也可以使用 PDO::FETCH_INTO，它和 PDO::FETCH_CLASS 本质上是一样的，但是您需要一个对象实例而不是一个类引用。每一次循环的迭代都会用当前的信息集重新填充同一个对象实例。这个例子假设Customer 类和第5步一样，使用和第1步中定义的相同的数据库参数和PDO连接。

  1. $cust = new Customer();
  2. while ($stmt->fetch(PDO::FETCH_INTO)) {
  3.   printf('%4d | %20s | %5s' . PHP_EOL, 
  4.   $cust->id, $cust->name, $cust->level);
  5. }

12.如果您没有指定错误模式，默认的PDO错误模式是 PDO::ERRMODE_SILENT。您可以使用PDO::ATTR_ERRMODE 作为键，然后 PDO::ERRMODE_WARNING或PDO::ERRMODE_EXCEPTION 作为值来设置错误模式。错误模式可以以关联数组的形式指定为PDO构造函数的第四个参数，也可以使用 PDO::ERRMODE_WARNING或PDO::ERRMODE_EXCEPTION。另外，你也可以在现有的实例上使用PDO::setAttribute()。

  1. 让我们假设您有以下DSN和SQL（在开始认为这是一种新的SQL形式之前，请放心：这个SQL语句将无法使用！）。
  1. $params = [
  2.   'host' => 'localhost',
  3.   'user' => 'test',
  4.   'pwd'  => 'password',
  5.   'db'   => 'php7cookbook'
  6. ];
  7. $dsn  = sprintf('mysql:host=%s;dbname=%s', $params['host'], $params['db']);
  8. $sql  = 'THIS SQL STATEMENT WILL NOT WORK';

14.如果你使用默认的错误模式制定你的PDO连接，唯一的线索就是 PDO::query() 将返回一个布尔值FALSE，而不是生成一个PDOStatement实例。

  1. $pdo1  = new PDO($dsn, $params['user'], $params['pwd']);
  2. $stmt = $pdo1->query($sql);
  3. $row = ($stmt) ? $stmt->fetch(PDO::FETCH_ASSOC) : 'No Good';
  1. 接下来的例子显示了使用构造函数方法将错误模式设置为WARNING。
  1. $pdo2 = new PDO(
  2.   $dsn, 
  3.   $params['user'], 
  4.   $params['pwd'], 
  5.   [PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING]);

16.如果你需要将准备阶段和执行阶段完全分离，可以使用 PDO::prepare()和PDOStatement::execute()来代替。语句会被发送到数据库服务器进行预编译。然后你可以根据需要多次执行该语句，很可能是在一个循环中执行。

17.PDO::prepare()的第一个参数是一个SQL语句，用占位符代替实际值。然后可以向PDOStatement::execute()提供一个数组。PDO自动提供数据库引用，这有助于防止SQL注入。

{% hint style=”info” %} 最佳实践

任何将外部输入（即来自表单发布的输入）与SQL语句相结合的应用程序都会受到SQL注入攻击。所有的外部输入必须首先进行适当的过滤、验证和其他方面的校验。不要将外部输入直接放入SQL语句中。相反，使用占位符，并在执行阶段提供实际（经过校验）的值。 {% endhint %}

18.要以反向方式遍历结果，可以改变可滚动光标的方向。另外，可能更容易的是，只需将 ORDER BY从ASC反转到DESC。这行代码设置了一个PDOStatement对象，请求一个可滚动的游标。

  1. $dsn  = sprintf('pgsql:charset=UTF8;host=%s;dbname=%s', $params['host'], $params['db']);
  2. $opts = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]; 
  3. $pdo  = new PDO($dsn, $params['user'], $params['pwd'], $opts);
  4. $sql  = 'SELECT * FROM customer '
  5.     . 'WHERE balance > :min AND balance < :max '
  6.     . 'ORDER BY id LIMIT 20';
  7. $stmt = $pdo->prepare($sql, [PDO::ATTR_CURSOR  => PDO::CURSOR_SCROLL]);
  1. 你还需要在获取操作中指定游标指令。这个例子获取结果集中的最后一行，然后向后滚动。
  1. $stmt->execute(['min' => $min, 'max' => $max]);
  2. $row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_LAST);
  3. do {
  4.   printf('%4d | %20s | %5s | %8.2f' . PHP_EOL, 
  5.        $row['id'], 
  6.        $row['name'], 
  7.        $row['level'], 
  8.        $row['balance']);
  9. } while ($row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_PRIOR));
  1. MySQL和SQLite都不支持可滚动的光标! 为了达到同样的效果，请尝试对前面的代码进行以下修改。
  1. $dsn  = sprintf('mysql:charset=UTF8;host=%s;dbname=%s', $params['host'], $params['db']);
  2. $opts = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]; 
  3. $pdo  = new PDO($dsn, $params['user'], $params['pwd'], $opts);
  4. $sql  = 'SELECT * FROM customer '
  5.     . 'WHERE balance > :min AND balance < :max '
  6.     . 'ORDER BY id DESC 
  7.        . 'LIMIT 20';
  8. $stmt = $pdo->prepare($sql);
  9. while ($row = $stmt->fetch(PDO::FETCH_ASSOC));
  10. printf('%4d | %20s | %5s | %8.2f' . PHP_EOL, 
  11.        $row['id'], 
  12.        $row['name'], 
  13.        $row['level'], 
  14.        $row['balance']);
  15. }
  1. PDO提供了对事务的支持。借用步骤9中的代码，我们可以将 INSERT 系列命令包装成一个事务块。
  1. try {
  2.     $pdo->beginTransaction();
  3.     $sql  = "INSERT INTO customer ('" 
  4.     . implode("','", $fields) . "') VALUES (?,?,?,?,?,?)";
  5.     $stmt = $pdo->prepare($sql);
  6.     foreach ($data as $row) $stmt->execute($row);
  7.     $pdo->commit();
  8. } catch (PDOException $e) {
  9.     error_log($e->getMessage());
  10.     $pdo->rollBack();
  11. }

22.最后，为了保持一切的模块化和可重用性，我们可以将PDO连接包装成一个单独的类Application\Database\Connection。在这里，我们通过构造函数建立一个连接。另外，还有一个静态factory()方法，让我们生成一系列的PDO实例。

  1. namespace Application\Database;
  2. use Exception;
  3. use PDO;
  4. class Connection
  5. {
  6.     const ERROR_UNABLE = 'ERROR: no database connection';
  7.     public $pdo;
  8.     public function __construct(array $config)
  9.     {
  10.         if (!isset($config['driver'])) {
  11.             $message = __METHOD__ . ' : ' 
  12.             . self::ERROR_UNABLE . PHP_EOL;
  13.             throw new Exception($message);
  14.         }
  15.         $dsn = $this->makeDsn($config);        
  16.         try {
  17.             $this->pdo = new PDO(
  18.                 $dsn, 
  19.                 $config['user'], 
  20.                 $config['password'], 
  21.                 [PDO::ATTR_ERRMODE => $config['errmode']]);
  22.             return TRUE;
  23.         } catch (PDOException $e) {
  24.             error_log($e->getMessage());
  25.             return FALSE;
  26.         }
  27.     }
  29.     public static function factory(
  30.       $driver, $dbname, $host, $user, 
  31.       $pwd, array $options = array())
  32.     {
  33.         $dsn = $this->makeDsn($config);
  35.         try {
  36.             return new PDO($dsn, $user, $pwd, $options);
  37.         } catch (PDOException $e) {
  38.             error_log($e->getMessage);
  39.         }
  40.     }

23.这个 Connection 类的一个重要组成部分是一个可以用来构造DSN的通用方法。我们需要做的就是建立PDODriver作为前缀，后面是”:“。之后，我们只需从配置数组中附加键/值对。每个键/值对都用分号隔开。我们还需要把后面的分号去掉，为此使用带有负限制的substr()。

  1.   public function makeDsn($config)
  2.   {
  3.     $dsn = $config['driver'] . ':';
  4.     unset($config['driver']);
  5.     foreach ($config as $key => $value) {
  6.       $dsn .= $key . '=' . $value . ';';
  7.     }
  8.     return substr($dsn, 0, -1);
  9.   }
  10. }

如何运行…

首先，你可以将第一步的初始连接代码复制到 chap_05_pdo_connect_mysql.php 文件中。在这个例子中，我们假设你已经创建了一个名为 php7cookbook 的MySQL数据库，用户名为cook，密码为book。接下来，我们使用 PDO::query() 方法向数据库发送一条简单的SQL语句。最后，我们使用产生的语句对象以关联数组的形式获取结果。不要忘记用 try {} catch {} 块来包装你的代码。

  1. <?php
  2. $params = [
  3.   'host' => 'localhost',
  4.   'user' => 'test',
  5.   'pwd'  => 'password',
  6.   'db'   => 'php7cookbook'
  7. ];
  8. try {
  9.   $dsn  = sprintf('mysql:charset=UTF8;host=%s;dbname=%s',
  10.     $params['host'], $params['db']);
  11.   $pdo  = new PDO($dsn, $params['user'], $params['pwd']);
  12.   $stmt = $pdo->query(
  13.     'SELECT * FROM customer ORDER BY id LIMIT 20');
  14.   printf('%4s | %20s | %5s | %7s' . PHP_EOL, 
  15.     'ID', 'NAME', 'LEVEL', 'BALANCE');
  16.   printf('%4s | %20s | %5s | %7s' . PHP_EOL, 
  17.     '----', str_repeat('-', 20), '-----', '-------');
  18.   while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  19.     printf('%4d | %20s | %5s | %7.2f' . PHP_EOL, 
  20.     $row['id'], $row['name'], $row['level'], $row['balance']);
  21.   }
  22. } catch (PDOException $e) {
  23.   error_log($e->getMessage());
  24. } catch (Throwable $e) {
  25.   error_log($e->getMessage());
  26. }

下面是结果输出。

使用PDO连接数据库 - 图1

在PDO构造函数中添加选项，将错误模式设置为 EXCEPTION。现在修改SQL语句，观察产生的错误信息。

  1. $opts = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
  2. $pdo  = new PDO($dsn, $params['user'], $params['pwd'], $opts);
  3. $stmt = $pdo->query('THIS SQL STATEMENT WILL NOT WORK');

你会看到如下内容。

使用PDO连接数据库 - 图2

占位符可以是命名的，也可以是定位的。已命名的占位符在准备好的SQL语句中以冒号(:)开头，是提供给 execute() 的关联数组中作为键的引用。位置占位符在准备好的SQL语句中用问号(?)表示。

在下面的例子中，命名的占位符被用来表示WHERE子句中的值。

  1. try {
  2.   $dsn  = sprintf('mysql:host=%s;dbname=%s', 
  3.                   $params['host'], $params['db']);
  4.   $pdo  = new PDO($dsn, 
  5.                   $params['user'], 
  6.                   $params['pwd'], 
  7.                   [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
  8.   $sql  = 'SELECT * FROM customer '
  9.       . 'WHERE balance < :val AND level = :level '
  10.       . 'ORDER BY id LIMIT 20'; echo $sql . PHP_EOL;
  11.   $stmt = $pdo->prepare($sql);
  12.   $stmt->execute(['val' => 100, 'level' => 'BEG']);
  13.   while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  14.     printf('%4d | %20s | %5s | %5.2f' . PHP_EOL, 
  15.           $row['id'], $row['name'], $row['level'], $row['balance']);
  16.   }
  17. } catch (PDOException $e) {
  18.   echo $e->getMessage();
  19. } catch (Throwable $e) {
  20.   echo $e->getMessage();
  21. }

这个例子展示了在 INSERT 操作中使用位置占位符。请注意，作为第四个客户要插入的数据包括一个潜在的SQL注入攻击。你还会注意到，需要对正在使用的数据库的SQL语法有一定的认识。在这种情况下，MySQL的列名是用背标（'）来引用的。

  1. $fields = ['name', 'balance', 'email', 
  2.            'password', 'status', 'level'];
  3. $data = [
  4.   ['Saleen',0,'saleen@test.com', 'password',0,'BEG'],
  5.   ['Lada',55.55,'lada@test.com',   'password',0,'INT'],
  6.   ['Tonsoi',999.99,'tongsoi@test.com','password',1,'ADV'],
  7.   ['SQL Injection',0.00,'bad','bad',1,
  8.    'BEG\';DELETE FROM customer;--'],
  9. ];
  11. try {
  12.   $dsn  = sprintf('mysql:host=%s;dbname=%s', 
  13.     $params['host'], $params['db']);
  14.   $pdo  = new PDO($dsn, 
  15.                   $params['user'], 
  16.                   $params['pwd'], 
  17.                   [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
  18.   $sql  = "INSERT INTO customer ('" 
  19.    . implode("','", $fields) 
  20.    . "') VALUES (?,?,?,?,?,?)";
  21.   $stmt = $pdo->prepare($sql);
  22.   foreach ($data as $row) $stmt->execute($row);
  23. } catch (PDOException $e) {
  24.   echo $e->getMessage();
  25. } catch (Throwable $e) {
  26.   echo $e->getMessage();
  27. }

要测试使用带有命名参数的预备语句，请修改SQL语句，添加一个 WHERE 子句，检查余额小于一定金额的客户，以及级别等于 BEG、INT或ADV（即初级、中级或高级）。不使用PDO::query()，而使用PDO::prepare()。在获取结果之前，你必须执行PDOStatement::execute()，提供余额和级别的值。

  1. $sql  = 'SELECT * FROM customer '
  2.      . 'WHERE balance < :val AND level = :level '
  3.      . 'ORDER BY id LIMIT 20';
  4. $stmt = $pdo->prepare($sql);
  5. $stmt->execute(['val' => 100, 'level' => 'BEG']);

下面是结果输出。

使用PDO连接数据库 - 图3

在调用 PDOStatement::execute()时，你可以不提供参数，而是绑定参数。这允许您将变量分配给占位符。在执行时，使用变量的当前值。

在这个例子中，我们将变量$min、$max和$level绑定到准备好的语句中。

  1. $min   = 0;
  2. $max   = 0;
  3. $level = '';
  5. try {
  6.   $dsn  = sprintf('mysql:host=%s;dbname=%s', $params['host'], $params['db']);
  7.   $opts = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
  8.   $pdo  = new PDO($dsn, $params['user'], $params['pwd'], $opts);
  9.   $sql  = 'SELECT * FROM customer '
  10.       . 'WHERE balance > :min '
  11.       . 'AND balance < :max AND level = :level '
  12.       . 'ORDER BY id LIMIT 20';
  13.   $stmt = $pdo->prepare($sql);
  14.   $stmt->bindParam('min',   $min);
  15.   $stmt->bindParam('max',   $max);
  16.   $stmt->bindParam('level', $level);
  18.   $min   =  5000;
  19.   $max   = 10000;
  20.   $level = 'ADV';
  21.   $stmt->execute();
  22.   showResults($stmt, $min, $max, $level);
  24.   $min   = 0;
  25.   $max   = 100;
  26.   $level = 'BEG';
  27.   $stmt->execute();
  28.   showResults($stmt, $min, $max, $level);
  30. } catch (PDOException $e) {
  31.   echo $e->getMessage();
  32. } catch (Throwable $e) {
  33.   echo $e->getMessage();
  34. }

当这些变量的值发生变化时，下一次执行将反映修改后的标准。

{% hint style=”info” %} 最佳实践

使用PDO::query()来执行一次性的数据库命令。当你需要多次处理同一条语句但使用不同的值时，使用PDO::prepare()和PDOStatement::execute()。 {% endhint %}

参考

关于不同数据库特定PDO驱动的语法和独特行为的信息，请看这篇文章。

http://php.net/manual/en/pdo.drivers.php

关于PDO预定义常量的总结，包括获取模式、光标方向和属性，请看下面的文章。

http://php.net/manual/en/pdo.constants.php