How to configure and use PDO for database access on Linux

by

Objective

Learn how to configure and use PDO for database access: from error modes to fetch methods.

Requirements

  • A standard knowledge of MySQL and mysql command line client;
  • Being familiar with the fundamental concepts of Object Oriented Programming
  • PHP >= 5.1
  • Have a working MySQL/MariaDB database

Difficulty

MEDIUM

Conventions

  • # – requires given linux commands to be executed with root privileges either
    directly as a root user or by use of sudo command
  • $ – requires given linux commands to be executed as a regular non-privileged user

pdo_vs_mysqli

Introduction

PDO is an acronym for PHP Data Objects: it is a PHP extension for interacting with databases through the use of objects. One of its strengths resides in the fact that it is not strictly tied to some particular database: its interface provides a common way to access several different environments, among the others:

  • MySQL
  • SQLite
  • PostgreSQL
  • Microsoft SQL Server

This guide aims to provide a quite complete overview of PDO, guiding the reader step by step from the establishment of a connection to the database, to the choice of the most appropriate fetch mode, showing how to create prepared statements and describing the possible error modes.

Create a test database and table

The first thing we are going to do is to create a database for this tutorial:

CREATE DATABASE solar_system;
GRANT ALL PRIVILEGES ON solar_system.* TO 'testuser'@'localhost'
IDENTIFIED BY 'testpassword';

We granted the user testuser all privileges on the solar_system database, using testpassword as password. Now let’s create a table and fill it with some data (no astronomic accuracy intended):

USE solar_system;

CREATE TABLE planets (
    id TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    name VARCHAR(10) NOT NULL,
    color VARCHAR(10) NOT NULL
    );

INSERT INTO planets(name, color) VALUES('earth', 'blue'), ('mars', 'red'), ('jupiter', 'strange');

DSN: Data Source Name

Now that we have a database, we must define a DSN. DSN stands for Data Source Name, and it’s basically a set of information required to connect to the database, represented in the form of a string. The syntax may be different depending on the database you want to connect to, but since we are interacting with MySQL/MariaDB, we will provide:

  • The type of driver to use for the connection
  • The hostname of the machine hosting the database
  • The port to use for the connection (optional)
  • The name of the database
  • The charset (optional)

The format of the string, in our case would be the following (we are going to store it in the $dsn variable):

$dsn = "mysql:host=localhost;port=3306;dbname=solar_system;charset=utf8";

First of all, we provided the database prefix. In this case, since we are connecting to a MySQL/MariaDB database, we used mysql. We then separated the prefix from the rest of the string by a colon and each of the other sections by a semicolon.

In the next two sections we specified the hostname of the machine on which the database is hosted and the port to use for the connection. If the latter is not provided, the default one will be used, which, in this case is 3306. Immediately after we provided the database name, and after it, the charset to use.

Creating the PDO object

Now that our DSN is ready, we are going to build the PDO object. The PDO constructor takes the dsn string as first parameter, the name of the user on the database as second parameter, its password as third, and optionally an array of options as the fourth one:

$options = [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ];

$pdo = new PDO($dsn, 'testuser', 'testpassword', $options);

However, the options can be specified also after the object has been constructed, via the SetAttribute() method:

$pdo->SetAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Setting PDO behavior on errors

Let’s take a look at some of the options available for PDO::ATTR_ERRMODE. This option is really important, because defines PDO behavior in case of errors. Possible options are:

PDO::ERRMODE_SILENT

This is the default. PDO will just set the error code and error message. They can be retrieved by using the errorCode() and errorInfo() methods.

PDO::ERRMODE_EXCEPTION

This is, in my opinion, the recommended one. With this option, in addition to setting the error code and info, PDO will throw a PDOException, which will break the script flow, and it’s particularly useful in case of PDO transactions (we are going to see what transactions are later in this tutorial).

PDO::ERRMODE_WARNING

With this option, PDO will set the error code and info as indexed PDO::ERRMODE_SILENT, but will also output a WARNING, which will not break the flow of the script.

Setting default fetch mode

Another important setting can be specified via the PDO::DEFAULT_FETCH_MODE. constant. It lets you specify the default fetch method to use when retrieving results from a query. These are the most commonly used options:

PDO::FETCH_BOTH:

This is the default. With it the result retrieved by a fetch query will be indexed both by integer and by column name. Applying this fetch mode when retrieving a row from the planets table would give us this result:

$stmt = $pdo->query("SELECT * FROM planets");
$results = $stmt->fetch(PDO::FETCH_BOTH);

Array
(
    [id] => 1
    [0] => 1
    [name] => earth
    [1] => earth
    [color] => blue
    [2] => blue
)

PDO::FETCH_ASSOC:

With this option, the result will be stored in an associative array in which every key will be the name of the column, and each value will be the corresponding value in a row:

$stmt = $pdo->query("SELECT * FROM planets");
$results = $stmt->fetch(PDO::FETCH_ASSOC);
Array
(
    [id] => 1
    [name] => earth
    [color] => blue
)

PDO::FETCH_NUM

This fetch mode returns the fetched row into a 0-indexed array:

Array
(
   [0] => 1
   [1] => earth
   [2] => blue
)

PDO::FETCH_COLUMN

This fetch method is useful when retrieving just the values of a column and will return all the results inside a plain, one-dimensional array. For example this query:

$stmt = $pdo->query("SELECT name FROM planets");

Would return this result:

Array
(
    [0] => earth
    [1] => mars
    [2] => jupiter
)

PDO::FETCH_KEY_PAIR

This fetch method is useful when retrieving the values of just 2 columns. It will return the results in the form of an associative array in which the values retrieved from the database for the first specified column in the query, will be used as the array keys, while the values retrieved for the second column, will represent the associative array values:

$stmt = $pdo->query("SELECT name, color FROM planets");
$result = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

Would return:

Array
(
   [earth] => blue
   [mars] => red
   [jupiter] => strange
)

PDO::FETCH_OBJECT:

When using the PDO::FETCH_OBJECT constant, an anonymous object will be created for each row retrieved. Its (public) properties will be named after the columns, and the query results will be used as their values. Applying this fetch mode to the same query above would return us a result in the form:

$results = $stmt->fetch(PDO::FETCH_OBJ);
stdClass Object
(
    [name] => earth
    [color] => blue
)

PDO::FETCH_CLASS:

This fetch mode, like the above, will assign the value of the columns to the properties of an object, but in this case we should specify an existing class that should be used to create the object. Let’s demonstrate it, first we are going to create a class:

class Planet
{
    private $name;
    private $color;

    public function setName($planet_name)
    {
        $this->name = $planet_name;
    }

    public function setColor($planet_color)
    {
        $this->color = $planet_color;
    }

    public function getName()
    {
        return $this->name;
    }

    public function getColor()
    {
        return $this->color;
    }
}

Please ignore the naiveness of the code above and just notice that the properties of the Planet class are private and the class has no constructor. Now let’s try to fetch the results.

When using fetch() with PDO::FETCH_CLASS you must use the setFechMode() method on the statement object before trying to retrieve the data, for example:

$stmt = $pdo->query("SELECT name, color FROM planets");
$stmt->setFetchMode(PDO::FETCH_CLASS, 'Planet');

We provided the fetch option constant PDO::FETCH_CLASS as the first argument of the setFetchMode() method, and the name of the class that should be used to create the object (‘Planet’ in this case) as the second one. Now we run:

$planet = $stmt->fetch();

A Planet object should have been created:

var_dump($planet);
Planet Object
(
    [name:Planet:private] => earth
    [color:Planet:private] => blue
)

Notice how the values retrieved resulting from the query, have been assigned to the corresponding properties of the object even if they are private.

Assigning properties after the object construction

The planet class has no explicit constructor defined, so no problems when assigning the properties; but what if the class had a constructor in which the property were assigned or manipulated? Since the values are assigned before the constructor is called, they would have been overwritten.

PDO helps providing the FETCH_PROPS_LATE constant: when using it, the values will be assigned to the properties after the object is constructed. For example:

class Planet
{
    private $name;
    private $color;

    public function __construct($name = moon, $color = grey)
    {
        $this->name = $name;
        $this->color = $color;
    }


    public function setName($planet_name)
    {
        $this->name = $planet_name;
    }


    public function setColor($planet_color)
    {
        $this->color = $planet_color;
    }


    public function getName()
    {
        return $this->name;
    }


    public function getColor()
    {
        return $this->color;
    }
}

We modified our Planet class, providing a constructor which takes two arguments: the first is name and the second is color. Those arguments have a default value respectively of moon and gray: this means that if no values are explicitly provided those will be the defaults assigned.

In this case, if we don’t use FETCH_PROPS_LATE, no matter the values retrieved from the database, the properties will have always the default values, because they will be overwritten when the object is constructed. Let’s verify it. First we run the query:

$stmt = $pdo->query("SELECT name, color  FROM solar_system WHERE name = 'earth'");
$stmt->setFetchMode(PDO::FETCH_CLASS, 'Planet');
$planet = $stmt->fetch();

Then we dump the Planet object and check what values its properties have:

var_dump($planet);

object(Planet)#2 (2) {
  ["name":"Planet":private]=>
  string(4) "moon"
  ["color":"Planet":private]=>
  string(4) "gray"
}

As expected, the values retrieved from the database have been overwritten by the defaults. Now, we demonstrate how this problem can be solved by using FETCH_PROPS_LATE (the query is the same as above):

$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'Planet');
$planet = $stmt->fetch();

var_dump($planet);

object(Planet)#4 (2) {
["name":"Planet":private]=>
string(5) "earth"
["color":"Planet":private]=>
string(4) "blue"
}

Finally we got the desired results. But what if the class constructor has no default values, and they must be provided ? Simple: we can specify the constructor parameters in the form of an array as a third argument, after the class name, in the setFetchMode() method. For example, let change The constructor:

class Planet
{

    private $name;
    private $color;

    public function __construct($name, $color)
    {
        $this->name = $name;
        $this->color = $color;
    }

    [...]
}

The constructor arguments are now mandatory, so we would run:

$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'Planet', ['moon', 'gray']);

In this case, the parameters we provided serve just as default values, needed to initialize the object with no errors: they will be overwritten by the values retrieved from the database.

Fetching multiple objects

Of course it’s possible to fetch multiple results as objects, either using fetch() method inside a while loop:

while ($planet = $stmt->fetch()) {
    // do stuff with the results
}

or by fetching all results at once. In this case, as said above, using the fetchAll() method, you don’t have to specify the fetch mode before calling the method itself, but at the moment you call it:

$stmt->fetchAll(PDO::FETCH_CLASS|PDO_FETCH_PROPS_LATE, 'Planet', ['moon', 'gray']);

PDO::FETCH_INTO

With this fetch method set, PDO will not create a new object, instead it will update the properties of an existing one, but only if they are public, or if you use the __set magic method inside the object.

Prepared vs direct statements

PDO has two ways to execute queries: one is the direct, one-step way. The other, more secure is to use prepared statements.

Direct queries

When using direct queries you have two main methods: query() and exec(). The former returns returns a PDOStatemnt object which you can use to access results via the fetch() or fetchAll() methods: you use it for statement which doesn’t modify a table, such as SELECT.

The latter, instead, returns the number of row that were changed by the query: we use it for statements that modifies rows, like INSERT, DELETE or UPDATE. Direct statements are to be used only when there are no variables in the query and you absolutely trust it is safe and properly escaped.

Prepared statements

PDO supports also two-stage, prepared statements: this is useful when using variables in the query, and it’s more secure in general, because the prepare() method will perform all the necessary escaping for us. Let’s see how variables are used. Imagine we want to insert the properties of a Planet object into the Planets table. First we would prepare the query:

$stmt = $pdo->prepare("INSERT INTO planets(name, color) VALUES(?, ?)");

As said before, first we would use the prepare() method which takes the sql query as argument, using placeholders for the variables. Now placeholders can be of two types:

Positional placeholders

When using ? positional placeholders we can obtain more concise code, but we must provide the values to be substituted in the same order of the column names, in an array provided as the argument to the execute() method:

$stmt->execute([$planet->name, $planet->color]);

Named placeholders

Using named placeholders, we don’t have to respect a particular order, but we are going to create more verbose code. When executing the execute() method we should provide the values in the form of an associative array in which each key would be the name of the used placeholder, and the associated value would be the one to be substituted in the query. For example the above query would become:

$stmt = $pdo->prepare("INSERT INTO planets(name, color) VALUES(:name, :color)");
$stmt->execute(['name' => $planet->name, 'color' => $planet->color]);

The prepare and execute methods can be used both when performing queries that modify or just retrieves data from the database. In the former case we use the fetch methods we saw above to retrieve the data, while in latter we can retrieve the number of affected rows by using the rowCount() method.

The bindValue() and bindParam() methods

To provide the values to be substituted in the query we can also use the bindValue() and bindParam() methods. The first binds the value of the variable provided to the related positional or named placeholder used when preparing the query. Using the above example we would have done:

$stmt->bindValue('name', $planet->name, PDO::PARAM_STR);

We bind the value of $planet->name to the :name placeholder. Notice that using both bindValue() and bindParam() methods we can specify, as third argument, the type of the variable, using the related PDO constant, in this case PDO::PARAM_STR.

Using bindParam(), instead, we can bind the variable to the related placeholder used when preparing the query. Notice that in this case the variable is bound by reference, and its value will only be substituted to the placeholder at the time the execute() method it’s called. The syntax is the same as above:

$stmt->bindParam('name', $planet->name, PDO::PARAM_STR)

We bound the $planet->name variable to the :name placeholder, not its current value! As said above the conversion will be performed just when the execute() method will be called, so the placeholder will by be substituted by the value the variable has at that time.

PDO Transactions

Transactions provides a way to preserve consistency when issuing multiple queries. All the queries are done in a “batch”, and committed to the database only if all of them are successful. Transactions will not work in all databases and not for all sql constructs, because some of them cause and implicit commit (full list here)

With an extreme and weird example, imagine that the user has to select a list of Planets, and each time he submits a new selection, you want to the delete the previous one from the database before inserting the new one. What would happen if the deletion succeeds, but not the insertion? We would have an user with no planets! Typically this is how transactions are implemented:

$pdo->beginTransaction();

try {
    $stmt1 = $pdo->exec("DELETE FROM planets");
    $stmt2 = $pdo->prepare("INSERT INTO planets(name, color) VALUES (?, ?)");
    foreach ($planets as $planet) {
        $stmt2->execute([$planet->getName(), $planet->getColor()]);
    }
    $pdo->commit();

} catch (PDOException $e) {
    $pdo->rollBack();
}

First of all the beginTransaction() method of the PDO object disables query autocommit, then inside a try-catch block, the queries are executed in the wanted order. At this point if no PDOException is raised, the queries are committed with the commit() method, otherwise, via the rollBack() method, the transactions are reverted and autocommit is restored.

This way there will always be consistency when issuing multiple queries. It’s quite obvious that you can use PDO transactions only when the PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION.

Related Linux Tutorials:

Comments and Discussions
Linux Forum