Using PDO to connect to a database

PDO is a highly performant and actively maintained database extension that has a unique advantage over vendor-specific extensions. It has a common Application Programming Interface (API) that is compatible with almost a dozen different Relational Database Management Systems (RDBMS). Learning how to use this extension will save you hours of time trying to master the command subsets of the equivalent individual vendor-specific database extensions.

PDO is subdivided into four main classes, as summarized in the following table:

Class	Functionality
`PDO`	Maintains the actual connection to the database, and also handles low-level functionality such as transaction support
`PDOStatement`	Processes results
`PDOException`	Database-specific exceptions
`PDODriver`	Communicates with the actual vendor-specific database

How to do it...

Set up the database connection by creating a PDO instance.

You need to construct a Data Source Name (DSN). The information contained in the DSN varies according to the database driver used. As an example, here is a DSN used to connect to a MySQL database:

$params = [
  'host' => 'localhost',
  'user' => 'test',
  'pwd'  => 'password',
  'db'   => 'php7cookbook'
];

try {
  $dsn  = sprintf('mysql:host=%s;dbname=%s',
  $params['host'], $params['db']);
  $pdo  = new PDO($dsn, $params['user'], $params['pwd']);
} catch (PDOException $e) {
  echo $e->getMessage();
} catch (Throwable $e) {
  echo $e->getMessage();
}

On the other hand, SQlite, a simpler extension, only requires the following command:

$params = [
  'db'   => __DIR__ . '/../data/db/php7cookbook.db.sqlite'
];
$dsn  = sprintf('sqlite:' . $params['db']);

PostgreSQL, on the other hand, includes the username and password directly in the DSN:

$params = [
  'host' => 'localhost',
  'user' => 'test',
  'pwd'  => 'password',
  'db'   => 'php7cookbook'
];
$dsn  = sprintf('pgsql:host=%s;dbname=%s;user=%s;password=%s', 
               $params['host'], 
               $params['db'],
               $params['user'],
               $params['pwd']);

The DSN could also include server-specific directives, such as unix_socket, as shown in the following example:
```
$params = [
  'host' => 'localhost',
  'user' => 'test',
  'pwd'  => 'password',
  'db'   => 'php7cookbook',
  'sock' => '/var/run/mysqld/mysqld.sock'
];

try {
  $dsn  = sprintf('mysql:host=%s;dbname=%s;unix_socket=%s', 
                  $params['host'], $params['db'], $params['sock']);
  $opts = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
  $pdo  = new PDO($dsn, $params['user'], $params['pwd'], $opts);
} catch (PDOException $e) {
  echo $e->getMessage();
} catch (Throwable $e) {
  echo $e->getMessage();
}
```
Note
Best practice
Wrap the statement that creates the PDO instance in a try {} catch {} block. Catch a PDOException for database-specific information in case of failure. Catch Throwable for errors or any other exceptions. Set the PDO error mode to PDO::ERRMODE_EXCEPTION for best results. See step 8 for more details about error modes.
In PHP 5, if the PDO object cannot be constructed (for example, when invalid parameters are used), the instance is assigned a value of NULL. In PHP 7, an Exception is thrown. If you wrap the construction of the PDO object in a try {} catch {} block, and the PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION, you can catch and log such errors without having to test for NULL.
Send an SQL command using PDO::query(). A PDOStatement instance is returned, against which you can fetch results. In this example, we are looking for the first 20 customers sorted by ID:
```
$stmt = $pdo->query(
'SELECT * FROM customer ORDER BY id LIMIT 20');
```
Note
PDO also provides a convenience method, PDO::exec(), which does not return a result iteration, just the number of rows affected. This method is best used for administrative operations such as ALTER TABLE, DROP TABLE, and so on.
Iterate through the PDOStatement instance to process results. Set the fetch mode to either PDO::FETCH_NUM or PDO::FETCH_ASSOC to return results in the form of a numeric or associative array. In this example we use a while() loop to process results. When the last result has been fetched, the result is a boolean FALSE, ending the loop:
```
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  printf('%4d | %20s | %5s' . PHP_EOL, $row['id'], 
  $row['name'], $row['level']);
}
```
Note
PDO fetch operations involve a cursor that defines the direction (that is, forward or reverse) of the iteration. The second argument to PDOStatement::fetch() can be any of the PDO::FETCH_ORI_* constants. Cursor orientations include prior, first, last, absolute, and relative. The default cursor orientation is PDO::FETCH_ORI_NEXT.
Set the fetch mode to PDO::FETCH_OBJ to return results as a stdClass instance. Here you will note that the while() loop takes advantage of the fetch mode, PDO::FETCH_OBJ. Notice that the printf() statement refers to object properties, in contrast with the preceding example, which references array elements:
```
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
  printf('%4d | %20s | %5s' . PHP_EOL, 
  $row->id, $row->name, $row->level);
}
```
If you want to create an instance of a specific class while processing a query, set the fetch mode to PDO::FETCH_CLASS. You must also have the class definition available, and PDO::query() should set the class name. As you can see in the following code snippet, we have defined a class called Customer, with public properties $id, $name, and $level. Properties need to be public for the fetch injection to work properly:
```
class Customer
{
  public $id;
  public $name;
  public $level;
}

$stmt = $pdo->query($sql, PDO::FETCH_CLASS, 'Customer');
```

When fetching objects, a simpler alternative to the technique shown in step 5 is to use PDOStatement::fetchObject():

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

You could also use PDO::FETCH_INTO, which is essentially the same as PDO::FETCH_CLASS, but you need an active object instance instead of a class reference. Each iteration through the loop re-populates the same object instance with the current information set. This example assumes the same class Customer as in step 5, with the same database parameters and PDO connections as defined in step 1:
```
$cust = new Customer();
while ($stmt->fetch(PDO::FETCH_INTO)) {
  printf('%4d | %20s | %5s' . PHP_EOL, 
  $cust->id, $cust->name, $cust->level);
}
```
If you do not specify an error mode, the default PDO error mode is PDO::ERRMODE_SILENT. You can set the error mode using the PDO::ATTR_ERRMODE key, and either the PDO::ERRMODE_WARNING or the PDO::ERRMODE_EXCEPTION value. The error mode can be specified as the fourth argument to the PDO constructor in the form of an associative array. Alternatively, you can use PDO::setAttribute() on an existing instance.

Let us assume you have the following DSN and SQL (before you start thinking that this is a new form of SQL, please be assured: this SQL statement will not work!):

$params = [
  'host' => 'localhost',
  'user' => 'test',
  'pwd'  => 'password',
  'db'   => 'php7cookbook'
];
$dsn  = sprintf('mysql:host=%s;dbname=%s', $params['host'], $params['db']);
$sql  = 'THIS SQL STATEMENT WILL NOT WORK';

If you then formulate your PDO connection using the default error mode, the only clue that something is wrong is that instead of producing a PDOStatement instance, the PDO::query() will return a boolean FALSE:
```
$pdo1  = new PDO($dsn, $params['user'], $params['pwd']);
$stmt = $pdo1->query($sql);
$row = ($stmt) ? $stmt->fetch(PDO::FETCH_ASSOC) : 'No Good';
```

The next example shows setting the error mode to WARNING using the constructor approach:

$pdo2 = new PDO(
  $dsn, 
  $params['user'], 
  $params['pwd'], 
  [PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING]);

If you need full separation of the prepare and execute phases, use PDO::prepare() and PDOStatement::execute() instead. The statement is then sent to the database server to be pre-compiled. You can then execute the statement as many times as is warranted, most likely in a loop.
The first argument to PDO::prepare() can be an SQL statement with placeholders in place of actual values. An array of values can then be supplied to PDOStatement::execute(). PDO automatically provides database quoting, which helps safeguard against SQL Injection.
Note
Best practice
Any application in which external input (that is, from a form posting) is combined with an SQL statement is subject to an SQL injection attack. All external input must first be properly filtered, validated, and otherwise sanitized. Do not put external input directly into the SQL statement. Instead, use placeholders, and provide the actual (sanitized) values during the execution phase.

To iterate through the results in reverse, you can change the orientation of the scrollable cursor. Alternatively, and probably more easily, just reverse the ORDER BY from ASC to DESC. This line of code sets up a PDOStatement object requesting a scrollable cursor:

$dsn  = sprintf('pgsql:charset=UTF8;host=%s;dbname=%s', $params['host'], $params['db']);
$opts = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]; 
$pdo  = new PDO($dsn, $params['user'], $params['pwd'], $opts);
$sql  = 'SELECT * FROM customer '
    . 'WHERE balance > :min AND balance < :max '
    . 'ORDER BY id LIMIT 20';
$stmt = $pdo->prepare($sql, [PDO::ATTR_CURSOR  => PDO::CURSOR_SCROLL]);

You also need to specify cursor instructions during the fetch operation. This example gets the last row in the result set, and then scrolls backwards:

$stmt->execute(['min' => $min, 'max' => $max]);
$row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_LAST);
do {
  printf('%4d | %20s | %5s | %8.2f' . PHP_EOL, 
       $row['id'], 
       $row['name'], 
       $row['level'], 
       $row['balance']);
} while ($row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_PRIOR));

Neither MySQL nor SQLite support scrollable cursors! To achieve the same results, try the following modifications to the preceding code:

$dsn  = sprintf('mysql:charset=UTF8;host=%s;dbname=%s', $params['host'], $params['db']);
$opts = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]; 
$pdo  = new PDO($dsn, $params['user'], $params['pwd'], $opts);
$sql  = 'SELECT * FROM customer '
    . 'WHERE balance > :min AND balance < :max '
    . 'ORDER BY id DESC 
       . 'LIMIT 20';
$stmt = $pdo->prepare($sql);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC));
printf('%4d | %20s | %5s | %8.2f' . PHP_EOL, 
       $row['id'], 
       $row['name'], 
       $row['level'], 
       $row['balance']);
}

PDO provides support for transactions. Borrowing the code from step 9, we can wrap the INSERT series of commands into a transactional block:

try {
    $pdo->beginTransaction();
    $sql  = "INSERT INTO customer ('" 
    . implode("','", $fields) . "') VALUES (?,?,?,?,?,?)";
    $stmt = $pdo->prepare($sql);
    foreach ($data as $row) $stmt->execute($row);
    $pdo->commit();
} catch (PDOException $e) {
    error_log($e->getMessage());
    $pdo->rollBack();
}

Finally, to keep everything modular and re-usable, we can wrap the PDO connection into a separate class Application\Database\Connection. Here, we build a connection through the constructor. Alternatively, there is a static factory() method that lets us generate a series of PDO instances:

namespace Application\Database;
use Exception;
use PDO;
class Connection
{
    const ERROR_UNABLE = 'ERROR: no database connection';
    public $pdo;
    public function __construct(array $config)
    {
        if (!isset($config['driver'])) {
            $message = __METHOD__ . ' : ' 
            . self::ERROR_UNABLE . PHP_EOL;
            throw new Exception($message);
        }
        $dsn = $this->makeDsn($config);        
        try {
            $this->pdo = new PDO(
                $dsn, 
                $config['user'], 
                $config['password'], 
                [PDO::ATTR_ERRMODE => $config['errmode']]);
            return TRUE;
        } catch (PDOException $e) {
            error_log($e->getMessage());
            return FALSE;
        }
    }

    public static function factory(
      $driver, $dbname, $host, $user, 
      $pwd, array $options = array())
    {
        $dsn = $this->makeDsn($config);
        
        try {
            return new PDO($dsn, $user, $pwd, $options);
        } catch (PDOException $e) {
            error_log($e->getMessage);
        }
    }

An important component of this Connection class is a generic method that can be used to construct a DSN. All we need for this to work is to establish the PDODriver as a prefix, followed by ":". After that, we simply append key/value pairs from our configuration array. Each key/value pair is separated by a semi-colon. We also need to strip off the trailing semi-colon, using substr() with a negative limit for that purpose:
```
  public function makeDsn($config)
  {
    $dsn = $config['driver'] . ':';
    unset($config['driver']);
    foreach ($config as $key => $value) {
      $dsn .= $key . '=' . $value . ';';
    }
    return substr($dsn, 0, -1);
  }
}
```

How it works...

First of all, you can copy the initial connection code from step 1 into a chap_05_pdo_connect_mysql.php file. For the purposes of this illustration, we will assume you have created a MySQL database called php7cookbook, with a username of cook and a password of book. Next, we send a simple SQL statement to the database using the PDO::query() method. Finally, we use the resulting statement object to fetch results in the form of an associative array. Don't forget to wrap your code in a try {} catch {} block:

<?php
$params = [
  'host' => 'localhost',
  'user' => 'test',
  'pwd'  => 'password',
  'db'   => 'php7cookbook'
];
try {
  $dsn  = sprintf('mysql:charset=UTF8;host=%s;dbname=%s',
    $params['host'], $params['db']);
  $pdo  = new PDO($dsn, $params['user'], $params['pwd']);
  $stmt = $pdo->query(
    'SELECT * FROM customer ORDER BY id LIMIT 20');
  printf('%4s | %20s | %5s | %7s' . PHP_EOL, 
    'ID', 'NAME', 'LEVEL', 'BALANCE');
  printf('%4s | %20s | %5s | %7s' . PHP_EOL, 
    '----', str_repeat('-', 20), '-----', '-------');
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    printf('%4d | %20s | %5s | %7.2f' . PHP_EOL, 
    $row['id'], $row['name'], $row['level'], $row['balance']);
  }
} catch (PDOException $e) {
  error_log($e->getMessage());
} catch (Throwable $e) {
  error_log($e->getMessage());
}

Here is the resulting output:

Add the option to the PDO constructor, which sets the error mode to EXCEPTION. Now alter the SQL statement and observe the resulting error message:

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

You will observe something like this:

Placeholders can be named or positional. Named placeholders are preceded by a colon (:) in the prepared SQL statement, and are references as keys in an associative array provided to execute(). Positional placeholders are represented as question marks (?) in the prepared SQL statement.

In the following example, named placeholders are used to represent values in a WHERE clause:

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

This example shows using positional placeholders in an INSERT operation. Notice that the data to be inserted as the fourth customer includes a potential SQL injection attack. You will also notice that some awareness of the SQL syntax for the database being used is required. In this case, MySQL column names are quoted using back-ticks ('):

$fields = ['name', 'balance', 'email', 
           'password', 'status', 'level'];
$data = [
  ['Saleen',0,'saleen@test.com', 'password',0,'BEG'],
  ['Lada',55.55,'lada@test.com',   'password',0,'INT'],
  ['Tonsoi',999.99,'tongsoi@test.com','password',1,'ADV'],
  ['SQL Injection',0.00,'bad','bad',1,
   'BEG\';DELETE FROM customer;--'],
];

try {
  $dsn  = sprintf('mysql:host=%s;dbname=%s', 
    $params['host'], $params['db']);
  $pdo  = new PDO($dsn, 
                  $params['user'], 
                  $params['pwd'], 
                  [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
  $sql  = "INSERT INTO customer ('" 
   . implode("','", $fields) 
   . "') VALUES (?,?,?,?,?,?)";
  $stmt = $pdo->prepare($sql);
  foreach ($data as $row) $stmt->execute($row);
} catch (PDOException $e) {
  echo $e->getMessage();
} catch (Throwable $e) {
  echo $e->getMessage();
}

To test the use of a prepared statement with named parameters, modify the SQL statement to add a WHERE clause that checks for customers with a balance less than a certain amount, and a level equal to either BEG, INT, or ADV (that is, beginning, intermediate, or advanced). Instead of using PDO::query(), use PDO::prepare(). Before fetching results, you must then perform PDOStatement::execute(), supplying the values for balance and level:

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

Here is the resulting output:

Instead of providing parameters when calling PDOStatement::execute(), you could alternatively bind parameters. This allows you to assign variables to placeholders. At the time of execution, the current value of the variable is used.

In this example, we bind the variables $min, $max, and $level to the prepared statement:

$min   = 0;
$max   = 0;
$level = '';

try {
  $dsn  = sprintf('mysql:host=%s;dbname=%s', $params['host'], $params['db']);
  $opts = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
  $pdo  = new PDO($dsn, $params['user'], $params['pwd'], $opts);
  $sql  = 'SELECT * FROM customer '
      . 'WHERE balance > :min '
      . 'AND balance < :max AND level = :level '
      . 'ORDER BY id LIMIT 20';
  $stmt = $pdo->prepare($sql);
  $stmt->bindParam('min',   $min);
  $stmt->bindParam('max',   $max);
  $stmt->bindParam('level', $level);
  
  $min   =  5000;
  $max   = 10000;
  $level = 'ADV';
  $stmt->execute();
  showResults($stmt, $min, $max, $level);
  
  $min   = 0;
  $max   = 100;
  $level = 'BEG';
  $stmt->execute();
  showResults($stmt, $min, $max, $level);
  
} catch (PDOException $e) {
  echo $e->getMessage();
} catch (Throwable $e) {
  echo $e->getMessage();
}

When the values of these variables change, the next execution will reflect the modified criteria.

Tip

Best practice

Use PDO::query() for one-time database commands. Use PDO::prepare() and PDOStatement::execute() when you need to process the same statement multiple times but using different values.

Table of Contents for
PHP 7: Real World Application Development

Using PDO to connect to a database

How to do it...

Note

Note

Note

Note

How it works...

Tip

See also

Table of Contents for PHP 7: Real World Application Development

Using PDO to connect to a database

How to do it...

Note

Note

Note

Note

How it works...

Tip

See also

Table of Contents for
PHP 7: Real World Application Development