Installation
As of PHP 5.1.0 PDO is enabled by default, so it should be already set up. More installation details can be found here.

What is PDO?

PDO is the abbrevation of PHP Data Objects. It is a lightweight system (to be precise, it is rather an interface) to access data from databases with PHP. Lets see what the PHP developers say about PDO:

The PHP Manual said:

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

How to use PDO?

The PDO system consists of 3 classes: PDO, PDOStatement & PDOException.
The PDO class is responsible for maintaining the database connection doing some conection related stuff (like Transactions) and create the instances of the PDOStatement class.
The PDOStatement class is what you work with most of the time. It is handling the SQL queries and the fetching of the results.
The PDOException class is what you need for error handling.

The PDO class

The PDO class is used to create the database connection. For that to work you must pass 3 types of information:

where to find the database

a valid login name

the correct password for the login name

The first piece of information is passed in a specially formatted string, the so-called Data Source Name (DSN). This string slightly differs for each database, but common required informations are: database type (e.g. MySQL), database host location (e.g. localhost), database port and database name.
You will find the DSN for your database described in the PDO Drivers’ section.

For a better understanding, I’ll give some examples for what you need to connect to a MySQL database:

- making a simple connection, passing just the least necessary information.

You may notice a thing or two in this SQL string: First of all, there is a ? where you would normally place the value (e.g. $type or $_GET['type']. This is due to the type of query we’re doing here, which is called "Prepared Statement". You need it to counter SQL Injection attacks. The security of Prepared Statements is based on the fact that it separates the data from the SQL instruction, thus any SQL code that may be passed in the data is treated as data and not as SQL instruction and can therefore do no harm in the database.
To differentiate between data and SQL instruction, a placeholder is put where the data should be pasted later. Of these placeholders, there are two types available in PDO: the question mark placeholder (?) and the named placeholder (a colon followed by a name token). Named placeholders are useful when you need to place one bit of data in several places (this may occur in JOINed tables).

WARNING: you must never place data in Prepared Statements, otherwise you are susceptible to SQL Injection attacks!

Before you place question marks all over your SQL query, bear in mind that table names and field names (i.e. anything that can be quoted by the backtick) are not data and can not be replaced by placeholders.

For a better readability, please refrain from using the wildcard character (*). If you explicitly name the fields you want to retrieve, this relieves you from getting data you don’t need and your query gets more self-explaining. You’ll benefit from that when you have to examine SQL errors. A typo is easier detectable, a result class error can be spotted faster, etc.

The PDOStatement class – part II: pass data

You learned in the previous section that Prepared Statements are immune against SQL Injection attacks. From this it follows that we do not have to escape data anymore – good bye to mysql_real_escape_string() & Co.

Now that we know that, how do we actually pass the data to the Prepared Statement? There is the regular and the lazy method. First I’ll show the lazy method:

In the lazy method, you pass the data into the PDOStatement->execute() method. For that to work you have to follow some constraints:

execute() always expects an array, even if there is only a single value

the data type of the value must only be String1

In this example you got to know one of PDOStatement’s most important methods: execute(). You will need that to actually send data to the database. Or in other words, without calling execute() you won’t get results (but an error).

Another example, demonstrating a user login query with named placeholders.

// note the SQL structure. All we want to know is, whether the
// user exists in the database and if (s)he submitted the correct
// password. For that we only need the number of matching rows
// which we can get by the SQL function COUNT(). There is no need
// to do that on the PHP side.
// The Heredoc Syntax is for demonstrating the SQL structure
$sql = <<<SQL
SELECT
COUNT(*)
FROM
`login_table`
WHERE
`username` = :login
AND
`pwhash` = :pass
SQL;
// create the Prepared Statement
$ps = $pdo->prepare($sql);
// note that there is no input sanitising necessary
$params = array(
'login' => $_GET['username'],
'pass' => hash('ripemd160', $_GET['password'])
);
// both input data are strings, thus the simplified method will do
$ps->execute($params);
// we only have 1 value, so we fetch directly
$loggedIn = (bool) $ps->fetchColumn();

In the regular method passing data to the Prepared Statement we use another method of the PDOStatement class, PDOStatement->bindValue() or PDOStatement->bindParam().

Which one to choose depends on the situation. As a rule of thumb you should use bindValue() when you have to pass data only once and bindParam() if you have to pass multiple data (e.g. all values of an array). An example is the database setup in this PDO tutorial (very first code block, lines #28-29 & #43-46).

The noteable difference between bindValue() and bindParam() is the second parameter. In bindValue() it has to be the value to be passed (you have to know the value at this point) while bindParam() expects a variable (it doesn’t matter if the variable is assigned something yet or even if it has been used before).

Finally, the third parameter defines the data type of the value to be passed. The available values are:

PDO::PARAM_BOOL (for booleans)

PDO::PARAM_NULL (for SQL NULL)

PDO::PARAM_INT (for SQL INTEGER)

PDO::PARAM_STR (for string types)

PDO::PARAM_LOB (for Large OBject types)

PDO::PARAM_STMT (for a recordset type, currently not supported)

PDO::PARAM_INPUT_OUTPUT (for an INOUT parameter of a Stored Procedure)

Most of the time you can use one of the first four types. The the LOB type is mostly used when transferring uploaded data. A detailed explanation would exceed this tutorial, though.

The PDOStatement class – part III: fetch data

Finally we reach the topic that PDO is about—fetching data. For this we need to call PDOStatement->fetch() and we get returned the current row according to our chosen fetch option. When it comes to fetch options, PDO really stands out. Unfortunately this is also the point where you as programmer have to do some thinking beforehand. It would be a waste to use PDO the way you have used mysql_* functions until now. Simply using it for returning an array of fields is like having a truck and pulling the trailer yourself.

This is the list of fetch options available in PDO:

PDO::FETCH_LAZY

PDO::FETCH_ASSOC

PDO::FETCH_NAMED

PDO::FETCH_NUM

PDO::FETCH_BOTH

PDO::FETCH_OBJ

PDO::FETCH_BOUND

PDO::FETCH_COLUMN

PDO::FETCH_CLASS

PDO::FETCH_INTO

PDO::FETCH_FUNC

PDO::FETCH_GROUP

PDO::FETCH_UNIQUE

PDO::FETCH_KEY_PAIR

PDO::FETCH_CLASSTYPE

PDO::FETCH_SERIALIZE

PDO::FETCH_PROPS_LATE

You did count right, there are 17! (in words: seventeen) fetch options in PDO. And that’s not all since some fetch options can (resp. must) be combined. But for the sake of clarity I’ll push in a demonstration example first.

This very simple code already shows PDO’s key feature: when using foreach(), you do not need (or rather must not) call the fetch() method manually. The fetch options are set by a separate method called setFetchMode().

Some options are additional and can not be used alone. i.e. PDO::FETCH_UNIQUE / PDO::FETCH_GROUP go with PDO::FETCH_COLUMN and PDO::FETCH_CLASSTYPE / PDO::FETCH_PROPS_LATE go with PDO::FETCH_CLASS. You combine two options simply by doing a bitwise OR (|).

$ps->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE);

Two of PDO’s fetch options even have a shortcut call (though that is not available in foreach()):

PDOStatement->fetchColumn(): shorthand for PDO::FETCH_COLUMN

PDOStatement->fetchObject(): shorthand for PDO::FETCH_OBJ or PDO::FETCH_CLASS

The last method to mention is PDOStatement->fetchAll(), which will return the complete result set as an Array. Bear in mind that you should only call that if you explicitly need an array (e.g. for using array functions). DO NOT USE IT TO LOOP THROUGH THE RESULTS AFTERWARDS, a PDOStatement can do that better.

Primitive fetch options

PDO::FETCH_ASSOC
Specifies that the fetch method shall return each row as an array indexed by column name as returned in the corresponding result set. If the result set contains multiple columns with the same name, PDO::FETCH_ASSOC returns only a single value per column name.

PDO::FETCH_NAMED
Specifies that the fetch method shall return each row as an array indexed by column name as returned in the corresponding result set. If the result set contains multiple columns with the same name, PDO::FETCH_NAMED returns an array of values per column name.

PDO::FETCH_NUM
Specifies that the fetch method shall return each row as an array indexed by column number as returned in the corresponding result set, starting at column 0.

PDO::FETCH_BOTH
Specifies that the fetch method shall return each row as an array indexed by both column name and number as returned in the corresponding result set, starting at column 0.

Extended fetch options

PDO::FETCH_COLUMN
Specifies that the fetch method shall return only a single requested column from the next row in the result set.

PDO::FETCH_BOUND
Specifies that the fetch method shall return TRUE and assign the values of the columns in the result set to the PHP variables to which they were bound with the PDOStatement->bindParam() or PDOStatement->bindColumn() methods.

PDO::FETCH_KEY_PAIR
Fetch into an array where the 1st column is a key and the 2nd column the value.Throws a PDOException if any but 2 columns are requested in the SQL.

"Operational" fetch options

PDO::FETCH_CLASS
Specifies that the fetch method shall return a new instance of the requested class, mapping the columns to named properties in the class.

PDO::FETCH_OBJ
Same as PDO::FETCH_CLASS using "stdClass"

PDO::FETCH_FUNC2
Returns the results of calling the specified function, using each row's columns as parameters in the call.

PDO::FETCH_INTO
Specifies that the fetch method shall update an existing instance of the requested class, mapping the columns to named properties in the class.

PDO::FETCH_SERIALIZE
Same as PDO::FETCH_INTO but object is provided as a serialized string.

PDO::FETCH_LAZY
Specifies that the fetch method shall return each row as an object with variable names that correspond to the column names returned in the result set. PDO::FETCH_LAZY creates the object variable names as they are accessed.

Combined fetch options

PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE
Executes the class’ constructor before the class members are populated from the DB.

PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE
Determine the class name from the value of first column.

PDO::FETCH_COLUMN | PDO::FETCH_UNIQUE3
Fetch only the unique values of a single column from the result set.

PDO::FETCH_COLUMN | PDO::FETCH_GROUP3
Return an associative array grouped by the values of a specified column.

As demonstrated in the DB Error handling Tutorial, you wrap your whole database code in a try … catch block, so that when an error occurs, all subsequent code that builds upon the successfull execution of the code beforehand will be skipped and therefore does not cause any more errors. You also have the possibility to hide errors from the user.

In contrast to die() (which instantly kills the whole script), this gives you the opportunity to finish the page in an orderly manner.

1 — the data type as specified in the database. Strings are possible for CHAR, VARCHAR, BINARY, VARBINARY, ENUM, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP (although the date and time types may be passed as numbers as well) and all number types except INT (PDO does not know floats and non-integer numerics). Some SQL data types like BINARY, TEXT and BLOB may also use PDO’s Large Object (LOB) type.
2 – this only seems to work as PDOStatement->fetchAll(PDO::FETCH_FUNC, $fn_name);
3 – only makes sense when using PDOStatement->fetchAll()