How to PDO in One, Two, Three!

[john.]

Today we'll cover the basics of PDO, PHP Data Objects which is a modern alternative to the now depreceated MySQL API. (mysql_connect, mysql_query, you know!) It provides some new features such as prepared statements, a well-designed object-oriented interface, multiple support for different database drivers, object mapping, stored procedores and it's performance is fast.

To follow along with the tutorial, create a database and use the following sql diagram, we'll use utf8 as our encoding.

 

CREATE SCHEMA `booky` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;

CREATE TABLE `booky`.`books` (
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(128) NULL,
 `author` VARCHAR(150) NULL,
 PRIMARY KEY (`id`));

 

Let's start off by making a connection:

A PDO object is constructed with 4 parameters where three of them are optional, the dns, username, password and options. The second and third one is quite obvious the username and password to your database server, but what about dns and options?

dns or Data Source Name as it stands for contains the information required to make a connection. Typically with the old MySQL extension you would provide a host, and the database name, well if you didn't figure it out already that's what you do in the dns. (Or the location to a file database like Sqlite).

How about some code to illustrate?

 

<?php

$db = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

$db = new PDO("sqlite:/path/database.db");

 

So we create a database hantle (the $db object), we provide the Data Source Name that contains the database type (in first example mysql), and then some database specific stuff, like only the path to the db when sqlite and host and name on mysql.

To ensure we have the same collation (character encoding) throughout our application it is considered good practice to set the charset attribute in the DNS, like this:

 

$db = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $user, $pass);

 

As we mentioned earlier PDO has many different drivers, you may check out the ones you have by doing:

 

<?php
print_r(PDO::getAvailableDrivers());

 

or looking in your php.ini file.

To close a connection you simply set the variable containing the object to null, i.e.

 

$db = null;

 

Error Handling

If you ever remember how bad error handling with the old MySql extension was, I'll brief you to say it's simple and great in PDO.

You probably did

 

or die(mysql_error());

 

No more of that. In PDO we have three different error modes, as they are called. These error modes can be set through setting attributes to the object we created when connecting. The method is called

setAttribute

.

Error Strategies:

PDO::ERRMODE_SILENT (default)

The errors can be read via errorCode() and errorInfo methods().

No errors will be displayed or outputted.

PDO::ERRMODE_WARNING

The same functionality as above, but also generates a traditional E_WARNING message.

PDO::ERRMODE_EXCEPTION

Exceptions. Throws PDOException that you can catch and act accordingly.

 

<?php

try {
   $db = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {

   if ($debugging) {
       echo $e; //__toString() calls and outputs some nice debugging info
   }

   //In production: ****! Send a SMS to engineer now...
}

 

We use setAttribute to set the PDO::ATTR_ERRMODE (which is a constant) to whet ever error mode we like. Here's a list if you want to know more attributes that can be set.

http://se1.php.net/manual/en/pdo.setattribute.php

 

-- CRUD --

Create - Read - Update - Delete

Create

Let's create a book (by inserting data to the books table).

 

//Data, provided by a User, from a form, etc.

// A object $db is assumed to have been created earlier

$name = 'In Paradise: A Novel';
$author = 'Peter Matthiessen';

$stmt = $db->prepare('INSERT INTO `books`(name, author) VALUES(?, ?)');
$stmt->execute(array($name, $author));

//Assuming we have 0 entries before...

echo $db->lastInsertId(); // "1"

 

The example above uses prepared statements to safely insert a book entry. Forget about escaping ahead, prepared statements does this for you. The other benefit is that the query only need to be prepared once and can then later be executed multiple times with the same or different parameters. So you could do the following:

 

//Data, provided by a User, from a form, etc.

// A object $db is assumed to have been created earlier

$name = 'In Paradise: A Novel';
$author = 'Peter Matthiessen';

$stmt = $db->prepare('INSERT INTO `books`(name, author) VALUES(?, ?)');
$stmt->execute(array($name, $author));

$name = 'Prayer';
$author = 'Philip Kerr';

$stmt->execute(array($name, $author));

echo $db->lastInsertId(); // "2"

 

The prepare() methods creates a PDOStatement, which described by php.net is "Represents a prepared statement and, after the statement is executed, an associated result set."

So an additonal object is created, the PDOStatement has the parsed (prepared) query and then after the query has been executed, (execute()) we can retrieve a result set in various ways and so forth. We could also do fancy stuff as counting columns and rows. More about that soon.

Did you notice the ?, these are called unnamed parameters. The items in the array within execute() must match the number of ? (parameters) in the query. When using many parameters in a query, it may simplify to instead use named parameters:

 

//Data, provided by a User, from a form, etc.

// A object $db is assumed to have been created earlier

$name = 'In Paradise: A Novel';
$author = 'Peter Matthiessen';

$stmt = $db->prepare('INSERT INTO `books`(name, author) VALUES(:name, :author)');
$stmt->execute(array(
   ':name' => $name,
   ':author' => $author,
));

 

Use named or unnamed parameters as you like, I prefer to use named parameters for queries with many parameters and unnamed parameters for those with few. Beware that you cannot mix the both, so use either.

Read

Now when we have data in our table, let's have a look at it. We have looked at prepared statements, but let's assume we have a static query with no parameters, in this scenario we can use the query() method instead to retrieve data.

 

   $sql = 'SELECT name, author FROM books';
   foreach ($db->query($sql) as $row) {
       echo $row['name'] . "\t";
       echo $row['author'] . "\t";
   }

 

As you can see the the $row is an associtave array, but this depends on how you have configured your PDO object. Let's use a different fetching style.

 

<?php
$sql = 'SELECT name, author FROM books';
$stmt = $db->query($sql);
$stmt->setFetchMode(PDO::FETCH_NUM);
while ($row = $stmt->fetch()) {
   echo $row[0] . "\t" . $row[1] . "\n";
}

 

A quoted list from php.net about fetching styles.

 

 

PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set

PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set

PDO::FETCH_BOUND: returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound with the PDOStatement::bindColumn() method

PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the class. If fetch_style includes PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined from a value of the first column.

PDO::FETCH_INTO: updates an existing instance of the requested class, mapping the columns of the result set to named properties in the class

PDO::FETCH_LAZY: combines PDO::FETCH_BOTH and PDO::FETCH_OBJ, creating the object variable names as they are accessed

PDO::FETCH_NAMED: returns an array with the same form as PDO::FETCH_ASSOC, except that if there are multiple columns with the same name, the value referred to by that key will be an array of all the values in the row that had that column name

PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0

PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set

 

There are two ways you can choose fetching style, one is using setFetchMode() as in the code example above, but you could also pass as parameter to the fetch methods which fetching style you like, like the example just below.

Lets have a final example with using a prepared statement instead, you remember that once a PDOStatement has been executed I said that you can fetch data, this is how:

 

<?php
$stmt = $db->prepare("SELECT name, author FROM books");
$stmt->execute();

$result = $stmt->fetch(PDO::FETCH_ASSOC);

print_r($result);

 

This will contain one row. If you fetch again, you'll have the next one. So, if you haven't already figured it out a loop is at rescue.

 

<?php
$stmt = $db->prepare("SELECT name, author FROM books");
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
   print_r($row);
}

 

Instead of looping, you could use fetchAll() to store the result in an array.

 

<?php
$stmt = $db->prepare("SELECT name, author FROM books");
$stmt->execute();

$rows = $stmt->fetchAll(PDO::FETCH_OBJ); 

print_r($rows);

 

Remember that you must have made a database object before executing all the code above, the examples are simplified, but assumes that you have a $db object created.

Update

Now when we have some data, let's make some updates.

 

<?php
$stmt = $dbh->prepare("UPDATE books SET author = ? WHERE id = 1");
$stmt->execute(array('John Doe'));

echo $stmt->rowCount(); //1

 

Simple enough.

rowCount()

is a method that allows you to count the rows that been affected by DELETE, UPDATE or INSERT.

 

<?php
$stmt = $dbh->prepare("UPDATE books SET author = ?");
$stmt->execute(array('John Doe'));

echo $stmt->rowCount(); //2

 

DELETE

Finally we have DELETE. It is as simple as UPDATE.

 

<?php
$stmt = $dbh->prepare("DELETE FROM books");
$stmt->execute();

echo $stmt->rowCount(); //2

 

Searching

Just add the wild cards!

 

$name = 'John';

$stmt = $db->prepare("SELECT name FROM books WHERE name LIKE ?");
$stmt->execute(array($name . '%'));

while ($row = $stmt->fetch())
{
   echo $row['name']; //John Doe
}

 

bindParam and bindValue

Instead of passing all the data as an array you could use bindParam or bindValue to bind a parameter in a prepared statement. The first argument of both the methods is the parameter name, and the second is the variable.

The third one is a constant that represents types, and is usually used with stored procedures. Have a look at http://www.php.net/manual/en/pdo.constants.php

But what is the different between the two? The difference is that bindParam() binds the variable as reference, which means that the variable can be modified before the PDOStatement is executed, mean while bindValue() binds the variables value and if variable is changed, the bindValue isn't. Here is a good example I found, as I barely use these methods myself.

 

$sex = 'male';
$s = $dbh->prepare('SELECT name FROM students WHERE sex = :sex');
$s->bindParam(':sex', $sex); // use bindParam to bind the variable
$sex = 'female';
$s->execute(); // executed with WHERE sex = 'female'


$sex = 'male';
$s = $dbh->prepare('SELECT name FROM students WHERE sex = :sex');
$s->bindValue(':sex', $sex); // use bindValue to bind the variable's value
$sex = 'female';
$s->execute(); // executed with WHERE sex = 'male'

 

PDO::quote — Quotes a string for use in a query.

quote() is the fallback method if you by some reason need to escape a string to a query. To make sure this doesn't fail you have to set the charset properly, either on server level or at the database connection (depends entirely on your driver, on MySQL it works fine.)

 

/* Simple string */
$string = 'Nice';
print "Unquoted string: $string\n";
print "Quoted string: " . $conn->quote($string) . "\n";

//Results:
//Unquoted string: Nice
//Quoted string: 'Nice'

Remember in almost any case prepared statements are much better to do when dealing with user input being entered to a database, don't rely on escaping but use prepared statements.

Transactions

Transactions allows you to execute multiple queries before actually comitting them. It makes sense if a query failed, you would want to rollback some other query that relies on the latter.

 

<?php
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();

/* Change the database schema and data */
$sth = $dbh->exec("DROP TABLE fruit");
$sth = $dbh->exec("UPDATE dessert
   SET name = 'hamburger'");

/* Recognize mistake and roll back changes */
$dbh->rollBack();

/* Database connection is now back in autocommit mode */
?>

 

The methods are pretty self-explanatory, and so easy to accomplish in PDO, right? In old extension you would have to manually execute a query that sets to transaction mode.

Conclusion

As you can see working with PDO is quite simple, instead of escaping data, use a prepared statement to ensure a secure query. You have a lot of different fetching styles that you can use, it's all about preferences mostly but sometimes there can be useful to specifically say: I need the data in following format. Transactions just became much easier and so did iterating data through.

If you have any questions, feel free to ask them. Any comments and feedback is highly appreciated. And yes, some of the examples are inspired/borrowed from the manual, but modified to be put in context.

Edited April 4, 2014 by john.

[gamble]

Personally i would have also included something like this:

 

$stmt = $db->prepare("SELECT username FROM users");
$stmt->execute();

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

 

Because personally i prefer to work with associative arrays

[KyleMassacre]

I have never even touched the PDO lib/API so I never really looked into it very much but they way you are describing it makes sense to me and should make sense to a lot of people so all I have to say is very nice share. And maybe this will help people get away from the deprecated MYSQL extension

[sniko]

Nice write-up!

I vote for stickie.

[john.]

Personally i would have also included something like this:

 

$stmt = $db->prepare("SELECT username FROM users");
$stmt->execute();

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

 

Because personally i prefer to work with associative arrays

Ah, yes. I updated with text clarifying the ways you could choose a fetching style in the Read section!

 

I have never even touched the PDO lib/API so I never really looked into it very much but they way you are describing it makes sense to me and should make sense to a lot of people so all I have to say is very nice share. And maybe this will help people get away from the deprecated MYSQL extension

Thank you! Hopefully it will, my goal was to let this tutorial serve as introductory as well as some sort of reference for people going to PDO.

 

Nice write-up!

I vote for stickie.

Thanks!