BiggieStylin Evil Genius. Love it/Hate it.

If you're reading this, you've probably heard of PDO in PHP, and are curious about what it is and what you can do with it. To start off, we first have to know what PDO stands for, and that is PHP Data Objects. This means that PDO is completely Object Oriented or OO in nature and does not deal with function calls alone. This tutorial assumes that you already have a good understanding of OO programing and will just teach you how to use the PDO and PDOStatement classes. I recommend checking both of those links before continuing in this tutorial to get a good understanding for each method.

Whenever dealing with a database, we need to make a connection before anything. With PDO, we create the connection as soon as we create our object by passing what is called a Data Source Name, or DSN, to the constructor of the PDO class. If you looked at the manual page for the PDO class, you also know that the constructor takes more arguments than just a DSN. These arguments include the username, the password, and an array of database options to set within our newly created object. I'll get into the options later, but lets look at a basic connection to a MySQL database.

<?php
$db = new PDO('mysql:host=localhost;dbname=test', 'myuser', 'mypass');
?>

This creates an object from the PDO class to the $db variable and the connection is opened to the localhost using the username myuser and password mypass. Now that we've got an object and a connection, we can start using our database! The DSN itself will change depending upon our database type and host that we're connecting to. I will only be working with MySQL in this tutorial, so if you're dealing with any other databases, I suggest you check out the drivers page and see the DSN for your specific database type.

Briefly I will cover the options you can also pass to the constructor. There are a few that can be passed to MySQL specifically and to see them visit the MySQL Driver page. These options help setup our database object and how it interacts with the database.

Now that we've got our database object, lets start looking at how to build queries. First thing we need to look at is parameters and how marvelous they are! If you take a look at the manual it states this: If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible). Yep, that's right... using parameters removes the possibility of injection! I know what your thinking... "wow, thats fantastic!" you're right, it is. Now lets look how to use them.

First we need to get a statement from our database object, and to do that we need the PDO::prepare method. This method will take a SQL command or statement, and return an object of the PDOStatement class that we can then use parameters with. Lets take a look at how prepare works:

<?php
$db = new PDO('mysql:host=localhost;dbname=test', 'myuser', 'mypass');
$sql = 'SELECT field1, field2, field3 FROM mytable';
$stmnt = $db->prepare($sql);
?>

Now we've got our PDOStatement object stored into $stmnt. At this point, nothing has been sent to the database and no queries have been made yet because we haven't told our statement to execute anything. Execution is done by using PDOStatement::execute and we'll take a look at that after a bit. First I want to get into parameters, because more than likely, you're going to want to add some chriteria and those are usually based off of variables or user input, which we know is always BAAAAAAAAAAAAADDDDDDDDDDDDDD!!!!! I know, you hate me for that bold red text, but realizing that user input is always bad and can never be trusted is very important no matter what you're doing, but that's another subject. Anyway... where was I... oh yeah, parameters!

There are several ways to use parameters with our statements so before we get started, I recommend you take a look at a few methods: PDOStatement::bindParam, PDOStatement::bindValue and PDOStatement::execute. The easiest method to pass parameters with, in my opinion, is PDOStatement::execute, so lets take a quick look at that! We're going to write a simple query again, but this time using criteria based upon user input.

<?php
$db = new PDO('mysql:host=localhost;dbname=test', 'myuser', 'mypass');
$sql = 'SELECT id, username, email FROM myusers WHERE username = ? AND password = ?';
$stmnt = $db->prepare($sql);
/* now, execute our statement against the database! */
$stmnt->execute(array($_POST['user'], $_POST['password']));
?>

Now we've used execute and made a query to the database. As you can also see, I've used execute to pass my parameters in to the SQL using an array. This example is similar to what you might do for a login page, of course, I would hope you also encrypt the password! There are two ways to pass parameters to execute like this. The way I've shown here is by using placeholders which you see in the query as ?. Another way is using named parameters, so lets look at the same query with named params.

<?php
$db = new PDO('mysql:host=localhost;dbname=test', 'myuser', 'mypass');
$sql = 'SELECT id, username, email FROM myusers WHERE username = :user AND password = :passwd';
$stmnt = $db->prepare($sql);
/* now, execute our statement against the database! */
$stmnt->execute(array(':user' => $_POST['user'], ':passwd' => $_POST['password']));
?>

You might start thinking named parameters is more typing, but you're only half right. In this simple query, it is easier to use placeholders, but in more complex queries its easier to use named parameters because PDO will reuse the values. In other words, if we changed our query so :user is in more than one spot, we still only need to pass $_POST['user'] once. I know... handy!

Now, you're probably wondering more about PDOStatement::bindParam and PDOStatement::bindValue. I'm not planning on getting into those with this tutorial, but I will mention that they are both very useful and can be used in place of sending an array to PDOStatement::execute. For example, bindParam would work well when inserting several rows within a loop where variables change. Then you can bind it to a variable, and when the loop runs, the variables is updated, and all you have to call is execute!

This tutorial is not yet complete, but I wanted to publish it anyway. I will be working on it to get it complete soon, so please keep checking back for more!