PDO Getting Started

Apr 25

Part 2 of this post is now out. It covers PDOStatement. Check it out here!

Introduction

Through my time in development, I’ve used several database solutions, but to be honest the easiest solution for me has always been PDO. It provides powerful solutions while not getting in your way. On top of this it doesn’t have the large learning curve that comes with many of the other full blown solutions.

In this post we will cover the extreme basics of using PDO. We will be covering the database object itself from connecting to the different ways to run queries. In future posts we will cover the other topics we don’t have room for in this post.


DSN

The first part to connecting to your database instance is a DSN. This is the connection string that will tell PDO to connect to your database. Your DSN will change depending on the type of database you are connecting to but in this post I will be giving MySQL examples. Your DSN will include at minimum type, host, and database name.

// Basic MySQL DSN
$dsn = 'mysql:host=localhost;dbname=test';

This will connect to localhost and the test database. The last option (should you need it) is the port number:

// Basic MySQL DSN
$dsn = 'mysql:host=localhost;dbname=test;port=3306';

Now lets connect to the database. When constructing the PDO class you pass in the DSN we just built, along with a username and password. Optionally the constructor takes a file parameter include driver options. This a little more of an advanced topic we will cover later. For now lets just work on connecting to the database:

// Define DSN, username and password
$dsn          = 'mysql:host=localhost;dbname=test';
$username = 'testingUser';
$password  = 'testingPass';

// We try catch to stop app failures
try {
    $db = new PDO($dsn, $username, $password);
} catch(PDOException $e) {
    /**
     * Handle exception here
     */
}

This is a simple example on creating a database connection, but in most cases this will be enough for your project. Lets move on to actually working with the database.

Querying the database

For querying the database you have 3 options that we will cover 1 at a time.

PDO::exec()

The exec function is used exclusively for database calls that don’t have a result row set. This would include inserts, creating or modifying tables, and procedure calls that don’t have row sets. As a side note you shouldn’t use exec if you have data that needs to be sanitized (Solutions for this will be covered in a bit). Lets look at a simple example:

// ... Code from above for connecting to the database
$password = hash('sha512', 'superAdminPassword');
$rowCount = $db->exec("INSERT INTO users(username, password) VALUES('admin', '$password')");
echo "Inserted $rowCount rows";

Again this is a simple example but it covers most use cases. Just remember this function shouldn’t be used for data that needs sanitized.

PDO::query()

PDO::query allows you to run a query against the database that returns a row set. I will note here that I prefer to use the prepare function that is covered later. As with exec this function shouldn’t be used if you have user data to escape. The query function works pretty much like the exec function with exception of the return value. With the query method you will get a PDOStatement return value with your data in it, or a false return value on a failed query(Either no result set or SQL error). Lets look at a simple example:

$result = $db->query('SELECT username FROM users');
foreach($result as $row) {
    echo $row['username'].'<br />'; // Output all usernames from the database.
}

PDO::prepare()

 

I saved this method for last because it deserves (and will probably get) its own post. You will more then likely use this post more then any other one. This is the function that will allow you to escape user data and store a query to run multiple times. Again for this post we are going to cover a very simple example and cover the functionality of PDOStatements further in a future post. Lets look at inserting multiple users:

$stmt = $db->prepare('INSERT INTO users(username, password) VALUES(?, ?)');
$users = array(
    'testUser1' => hash('sha512', 'password1'),
    'testUser2' => hash('sha512', 'password2')
);

foreach($users as $username => $password) {
    $stmt->execute(array($username, $password));
}

Conclusion

 

This concludes the basic functionality you get from PDO. I hope it gives you enough of a grasp of the class to get you started. Check back for more advanced usage of the classes that surround PDO. As always if you have questions or comments I would love to hear them

3 comments

  1. Skythe /

    Thanks for this overview – will continue on to part 2 now. ;)

    Your blog posts really need to show the year they were published in tho.

  2. Skythe /

    The link to part 2 up there doesnt work btw.

  3. Thanks for the heads up. I updated this so now it points correctly.

Leave a Reply