January 30, 2013


PDO is a library for PHP specifically designed for secure database interactions. PDO is an abstract layer, so it allows the use of different RDBMS (Relational Database Management Systems) whilst using a consistent SQL syntax. For example, you can easily switch between MySQL, SQLite, MariaDB etc. This allows for portability with code. Note: if you use any driver-specific SQL functions, they will not be modified. PDO stands for PHP Data Objects.

PDO comes shipped with any PHP installation > 5.1 however may require some custom configuration to work with your preferred database driver. Most should already include MySQL. PDO requires at least PHP 5.0 to run and will not work with older versions as it utilises the new OO features that came with 5.0. PDO is completely object-oriented. There are no procedural functions for its use.

Getting Started

As with anything else, you must first connect to a database before interacting with it. In PDO, you create one object per database you would like to interact with. You do this by instantiating the PDO class with a DSN. This stands for Data(base) Source Name. Inside the DSN, you put the connection details of your DB. Below is syntax/parameter to instantiate the PDO class:

$db = new PDO($dsn, $databaseUsername, $databasePassword);

The DSN should include the type of database you’ll be working with; the host and the database name. Its format:



$dsn = 'mysql:host=localhost;dbname=my_db&charset=utf8';

And the full syntax:

$db = new PDO('mysql:host=localhost;dbname=my_db&charset=utf8', 'user', 'password');

We add the character set parameter for security reasons, it’s pretty important, so don’t forget it!

If the connection fails, however, PDO will throw a PDOException, so we can wrap our connect statement in a try/catch block:

try {
    $db = new PDO('mysql:host=localhost;dbname=my_db&charset=utf8', 'user', 'password');
} catch (PDOException $e) {
    // There was an error with the connection

For development/learning, it’s a good idea to set the PDO error mode attribute PDO::ERRMODE_EXCEPTION. This will throw an exception if there is an error. If you’ve worked with the native MySQL functions, this can be likened to or die(mysql_error()). Also, to completely defend ourselves against SQL injection attacks (expanded upon later), we must set the PDO::EMULATE_PREPARES to false. You can read about why here. We can set both of these attributes by passing an array as the fourth parameter, so your final connection syntax is:

try {
    $db = new PDO('mysql:host=localhost;dbname=my_db&charset=utf8', 'user', 'password', 
} catch (PDOException $e) {
    // There was an error with the connection

“Regular” Queries & Retrieving Results

So, now that we’ve successfully connected to our database, we can start interacting. Firstly, we’ll look at the query() method. This will execute a regular SQL query and return a resource:

$query = $db->query(QUERY);

Easy enough, right? The query() method returns a PDO object. Think of this as a resource, much similar to using your mysql_query. Now we must “fetch” the data. We can do this in one of two ways:

Using the fetch() method will return ONE result from your query, unless you couple it with a while loop, as shown below:

$query = $db->query('SELECT username FROM users');
# One result
$fetch = $query->fetch();
echo $fetch['username'];
# All results
while ($fetch = $query->fetch()) {
	echo $fetch['username'];

This method is similar to fetch(), however it returns ALL the results. Example:

$query = $db->query('SELECT name FROM fruits');
$fetch = $query->fetchAll();
# First result
echo $fetch[0]['name'];
# All results
foreach ($fetch as $fruit) {
	echo $fruit['name'];

By default, both fetch() and fetchAll() will return an associative array of result(s). This can be change in the first parameter of each function – read up on the documentation linked.

Prepared Statements

This is where it starts to get fun. Whenever you interact with a database using user input, you should ALWAYS use prepared statements. But what are they?

A basic explanation: You write a query and state which values inside of the query will “user input”. You then bind the values of the user input to the query, and any malicious code is removed! Like magic :) Okay, so let’s see some examples. First, we must introduce the prepare() method.

Inside of the prepare() method, you put your query. I’m going to show you a couple of ways preparing a statement for user inputted values. First of all, replace any values in your query that would you want to “escape” with :name. That’s a COLON followed by the name. Like the following:

SELECT id FROM fruit
WHERE name = :name

This is can be done multiple times within a query:

SELECT id FROM users
WHERE username = :username AND password = :password

Great! We stick that inside the prepare() method and we’re good to go. Next thing we have to do is execute() the statement. When we call the prepare() method, it will return another PDO object. We then call the execute() function with out user-inputted parameters in them. The execute() takes first parameter array, which is formatted:

$array = array(
	'key' => 'value',
	'foo' => 'bar'

Each array KEY is the same as the ‘key’ we gave in our prepare() statement. So let’s look at a full query, using prepare() and execute():

$query = $db->prepare('SELECT id FROM users WHERE username = :username AND password = :password');
$array = array(
	'username' => 'Carrot',
	'password' => 'sup homie'

The query would look like this:

SELECT id FROM users WHERE username = 'carrot' AND password = 'sup homie'

Awesome! Now we can just do as we did before to retrieve the results:

$fetch = $query->fetch();
echo $fetch['id'];

Now I’m going to show you another method. This is a much, MUCH faster method than above, however it could get confusing if you have a long query. Instead of using :name, we simple use a question mark ( ? ):

$query = $db->prepare('SELECT id FROM users WHERE username = ? AND password = ?');

Now we can use the execute() again, however this time, we can use a regular array and the values wil be replaced in the order of the question marks:

$query = $db->prepare('SELECT id FROM users WHERE username = ? AND password = ?');
$array = array('carrot', 'password');

I think that’s much simpler :) This is called “binding” since we are “binding” data to our query. Let me show you one more example of this:

$query = $db->prepare('INSERT INTO users (username, name, password) VALUES (?, ?, ?)');
# A possibly problematic query!
$array = array('carrot', "John O'Dear", 'password');

Same as:

$query = "INSERT INTO users (username, name, password) VALUES ('carrot', 'John O\'Dear', 'password')";

See how it saved our asses? Without preparing/escaping the data, we would have had an SQL error because of the single quote ( ‘ ) inside “John O’Dear”.  This is escape so our query is safe. So no more SQL injection vulnerabilities! 😀

Binding Datatypes

With the array and execute() method above, all our binded values are wrapped in quotes, so they are all passed as strings. But what if we want to bind data that is of a different data type? Like an integer? Well, we use the bindParam() method.

Let’s use this with our named params. First parameter of the method is name of the parameters, second is the value we want to replace with and the last is the datatype using the PDO::PARAM_* constants. We then use the execute() method without passing any parameters.


$id = 17;
$query = $db->prepare('SELECT name FROM fruits WHERE id = :id');
$query->bindParam(':id', $id, PDO::PARAM_INT);
# Runs the query:
SELECT name FROM fruits WHERE id = 17

You can bind as many parameters as you like with the bindParam() method:

$query = $db->prepare('INSERT INTO people (username, name, age) VALUES (:username, :name, :age)');
# If we leave out the third parameters, it is set to
# PDO::PARAM_STRING by default
$query->bindParam(':username', 'Carrot');
$query->bindParam(':name', 'John');
# Now we bind an integer
$query->bindParam(':age', 17, PDO::PARAM_INT);
# Execute the query

This can also be done with the question mark syntax, however you replace the first parameter with the position of the question mark in the prepared statement:

$query = $db->prepare('INSERT INTO people (username, name, age) VALUES (?, ?, ?)');
$query->bindParam(1, 'Carrot');
$query->bindParam(2, 'John');
$query->bindParam(3, 17, PDO::PARAM_INT);

Counting Results

If we want to count the number of results returned, there’s a method for that! It’s called rowCount()

After using the query() method or executing a prepared statement, we can use the rowCount() method to return the number of results from the query:

$query = $db->query('SELECT id FROM users');
echo $query->rowCount();

Error Handling

Handling errors is a huge part of development. PDO makes it really easy. We’ll be using the errorInfo() method. After you’ve prepared a statement/ran a query, this method will return any errors. The errorInfo() method will return an array with 3 elements:

0 – SQLSTATE error code
1 – Driver-specific error code
2 – Driver-specific error message

If there is no error, the 1st and 2nd element will be blank, however the 0th element will have “00000”. I’m not going to explain these SQLSTATE error messages right now. Anyway, if a query fails, the prepare() and query() methods will return FALSE. So let’s play, shall we?

$query = $db->prepare('INVALID QUERY HERE');
# So, if there's an error
if ($query === false) {

Really simple. Makes debugging your SQL a real doddle. Remember, the errorInfo() method is attached to the PDO statement that is prepared (before being executed) or after using the query() method :) One more example:

$query = $db->query('INVALID SQL');
if ($query !== FALSE) {
	echo 'Query was successful!';
else {
	$error = $query->errorInfo();
	echo 'MySQL Error: ' . $error[2];


To conclude: If you use PDO correctly, there will be a ZERO risk of SQL injections. PDO is great and you should always use it where you can :)

There are no comments posted!

New Comment