Codular

HomeWriters RSS

PDO: A How To

Introduction

We've already looked at MySQLi, and now we're going to have a look at the PDO class. PDO stands for PHP Data Objects and is described as a "lightweight, consistent interface for accessing databases in PHP". Although it's name isn't that great, PDO is a lovely way of accessing your database in PHP.

Differences with MySQLi

MySQLi and PDO are very alike, except for two major differences:

  1. While MySQLi only works with MySQL, PDO works with 12 different Databases

  2. PDO does not have a procedural fallback (mysqli_* functions)

Getting started

In order to use PDO, you will need to check if your PHP installation has the PDO plugin installed.

You can test this by running $test = new PDO(); and see what it says. If it complains that the argument count doesn't match, you have the plugin installed. If it says the object doesn't exist, you can first try checking your php.ini file to see if extension=php_pdo_yoursqlserverhere.ext is commented out. If not, you'll have to install PDO - this is outside the scope of this article.

Connecting

Now that we are sure our server is ready to go, lets get started with connecting to the database:

$dsn = 'mysql:dbname=demo;host=localhost;port=3306';
$username = 'root';
$password = 'password_here';
try {
    $db = new PDO($dsn, $username, $password); // also allows an extra parameter of configuration
} catch(PDOException $e) {
    die('Could not connect to the database:<br/>' . $e);
}

All of this is pretty self explanatory, except for $dsn. DSN stands for Data Source Name, and allows for different types of input. The most common one is the one we just used above, a string with all of the settings. The PHP site explains the other DSN options that are available.

You can leave out all extra settings in the dsn, so long as you have a database driver specified with a colon after, e.g. mysql:. In that case PDO will attempt to connect to the host localhost. Just like with MySQLi you will have to specify the database name in your queries when doing this.

The last thing you might have noticed is that we wrapped our object initialization attempt in a try-catch block. PDO will throw a PDOException when it fails to connect, but not when queries fail. If you want this anyway, you can use the below code after the $db = line to enable it:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

or you can add it directly into your PDO initialization:

$db = new PDO($dsn, $username, $password, array (
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));

We will be using the default error mode for now, which will simply return false on failure, but there's no reason to not use the Exception alternative.

Basic Queries

Queries are easy in PDO with the two default methods: query and exec, of which the latter is useful if you want to get the number of rows affected and therefore this is useless for SELECT statements.

Now lets see an example for both these methods:

$statement = <<<SQL
    SELECT *
    FROM `foods`
    WHERE `healthy` = 0
SQL;

$foods = $db->query($statement);

Assuming our query is correct, $foods is now a PDOStatement object, which we can use to fetch our results or check to see how many rows we collected with this query.

Number of rows

Unfortunately PDO does not provide a one size fits all solution for counting the returned rows. PDOStatement does contain a method called rowCount, but this is not guaranteed to work with every SQL driver (luckily, it works with MySQL).

In case your SQL driver does not support this method, you have two options: Get the count from a second query (SELECT COUNT(*)), or simply use count($foods).

Luckily for our MySQL example we can simply do this, which will output the correct value:

echo $foods->rowCount();

Loop through result

Printing these lovely foods isn't difficult either:

foreach($foods->FetchAll() as $food) {
    echo $food['name'] . '<br />';
}

One thing to keep in mind is that PDO also supplies a Fetch method, which will only return the first result, which is useful if you only query for 1 result.

Escape user input

You may have heard of (mysqli_)real_escape_string, which is used to secure user input. PDO offers a method called quote, which will place quotes around the given string (when required) and escapes special characters:

$input:             this is's' a '''pretty dange'rous str'ing

After escaping it you end up with:

$db->quote($input): 'this is\'s\' a \'\'\'pretty dange\'rous str\'ing'

exec()

As mentioned above, you can use the exec method for UPDATE, DELETE and INSERT statements, and it will return the amount of rows affected:

$statement = <<<SQL
    DELETE FROM `foods`
    WHERE `healthy` = 1;
SQL;

echo $db->exec($statement); // outputs number of deleted rows

Prepared Statements

Although the methods exec and query are still widely used and supported by PHP, it even tells on it's own site it's better to use prepared statements instead. Now why is that? Mostly for one reason: It's safer. Prepared statements don't insert the parameters into the actual query, therefor avoiding a lot of potential SQL injections.

For some reason however, PDO actually does not use true prepared statements. Instead it will simulate them by inserting the parameters into the query before sending it to the SQL server, which makes certain systems vulnerable for SQL injections anyway.

If your SQL server does support true prepared statements, there's an easy fix which can put right below the PDO object initialization:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Now lets get started with our first prepared statements:

$statement = $db->prepare('SELECT * FROM foods WHERE `name`=? AND `healthy`=?');
$statement2 = $db->prepare('SELECT * FROM foods WHERE `name`=:name AND `healthy`=:healthy)';

As you can see there are two ways of creating parameters, named and unnamed ones (cannot be both in one statement). You can then use bindValue to enter your input:

$statement->bindValue(1, 'Cake');
$statement->bindValue(2, true);

$statement2->bindValue(':name', 'Pie');
$statement2->bindValue(':healthy', false);

Note that you will need to include the colon (:) when using named parameters. PDO also has a bindParam method, which will bind the value by reference, meaning it will only look up the value when the statement is executed.

Now all that's left to do, is execute our statements:

$statement->execute();
$statement2->execute();

// Get our results:
$cake = $statement->Fetch();
$pie  = $statement2->Fetch();

To avoid lenghy pieces of code with only bindValue, you can also use an array as parameter for the execute method, like this:

$statement->execute(array(1 => 'Cake', 2 => true));
$statement2->execute(array(':name' => 'Pie', ':healthy' => false));

Transactions

We have previously described what transactions are:

A transaction is a group of queries that execute but don't save their effects in the database. The advantage of this is if you have 4 inserts that all rely on each other, and one fails, you can roll back the others so that none of the data is inserted, or if updating fields relies on fields being inserted correctly. You need to ensure that the database engine that you're using supports transactions.

Starting a transaction

You can easily start a transaction by calling the beginTransaction method:

$db->beginTransaction();

$db->inTransaction(); // true!

You can then proceed by running your queries and finally, executing the transaction:

$db->commit();

There is also a rollBack method like in MySQLi, but as it doesn't roll back all types (like DROP TABLE in MySQL) the method isn't really reliable and I'd recommend avoid relying on it.

Other useful options

There are several options you might want to consider using. These can be entered as the fourth parameter into your object initialization:

$options = array($option1 => $value1, $option[..]);
$db = new PDO($dsn, $username, $password, $options);

PDO::ATTR_DEFAULT_FETCH_MODE

You can choose what type of resultset PDO will return to you, like PDO::FETCH_ASSOC, which will allow you to use $result['column_name'], or PDO::FETCH_OBJ, which will return an anonymous Object so that you can use $result->column_name

You can also fetch the result into a specific class (Model), which can be done by setting a fetchMode for each individual query like this:

$query = $db->query('SELECT * FROM `foods`');
$foods = $query->fetchAll(PDO::FETCH_CLASS, 'Food'); 

- All fetch modes

PDO::ATTR_ERRMODE

We've already explained this one above, but the true TryCatch-lover needs to use this: PDO::ERRMODE_EXCEPTION. If for whatever reason you want to throw PHP Warnings instead, use PDO::ERRMODE_WARNING

PDO::ATTR_TIMEOUT

In case you're worried about loadtimes, you can use this attribute to specify a time out in seconds for your query. Note that this by default throws E_WARNING if the time is exceeded, unless PDO::ATTR_ERRMODE is changed.

More attributes can be found on the PHP site.

Final Thoughts

PDO is a great way of accessing your database in PHP, and argueable the best way to go. Unless you refuse to use an OO approach or are too used to the MySQLi method names, there's no reason to not use PDO.

Even better is to fully switch to only using prepared statements, which will make your life easier in the end!