Publishing System Settings Logout Login Register
An Introduction to PDO
TutorialCommentsThe AuthorReport Tutorial
Tutorial Avatar
Rating
Add to Favorites
Posted on September 29th, 2010
2549 views
PHP Coding

An Introduction to PDO

 

PDO: A Better Way to Work With Databases

PDO (PHP Data Objects) is a powerful, yet lightweight, abstraction layer within PHP that allows you to work with a database without caring what system it actually happens to be. In short, through PDO you can write database-driven code that is entirely independent of the underlying server specifics. Through PDO you can work with MySQL, SQLite, PostgreSQL, MS SQL, Oracle, and a few others without having to write separate code for each software. In fact, the only line of software-specific code is the initial initialization of the PDO object. Sounds pretty damn useful already doesn't it?

The obvious advantage behind this is portability. Write once, use anywhere- a very useful concept, don't you agree? This is achieved through one very powerful keyword in programming: abstraction. That is: the act of writing code at the most appropriate level possible. Think of programming layers as a pyramid. The further towards the base of the pyramid you go the more hard-written and specific the code is. Each layer above it gets a little more generic and forgiving. As such it gets more portable and maintainable. I cannot stress to programmers enough just how vital abstracting your code is.

Anyways, enough on the abstraction lecture. How about we take a look at some PDO!

 

A Simple Example

Lets just look at how we'd go about connecting to a MySQL database, and query all of the results from a made up table.

<?php

try
{
	$db = new PDO ("mysql: host=localhost; dbname=DATABASE_NAME", "USERNAME", "PASSWORD");


	$sql = "SELECT * FROM my_table ORDER BY some_col DESC";

	foreach ($db->query ($sql) as $row)
	{
echo "<p>Current row: {$row['id']}</p>";
	}
}
catch (PDOException $err)
{
	die ($err);
}

?>

Lets first look at the fifth line "$db = new PDO". This line here holds the only thing specific to MySQL- the connection string, otherwise known as the Data Source Name (DSN). The PDO constructor takes three parameters, which we gave in the above example. They are:

__construct (DSN, [USERNAME, PASSWORD])

The username and password parameters are optional for PDO, but more likely than not your database software will require them. What actually needs describing here is the DSN. A DSN, aka Data Source Name, is a database-specific connection string. It'll be different for all different database systems, but he format of a MySQL DSN is:

mysql: host=HOST; dbname=DATABASE; port=PORT

The port parameter is optional. You're probably familiar with what thisline does just by looking at it. The DSN specifics we're usingMySQL, andthen goes on to initiate the database location (commonly localhost) andthe name of the database we're to work with.

I've encased this line inside a Try-Catch structure. This is particularly helpful with PDO because anytime there's an error within PDO it'll shoot back PDOException::getCode (), and the Try-Catch structure makes catching that very easy. The details behind the PDOException class aren't very important for this tutorial. Just know that if you typecast a variable with the PDOException class, like we did above in the catch () statement, you'll set the variable to the contents of the error. I strongly recommend looking further into the Try-Catch block.

So once we've initiated the PDO object (unless we killed the process by erring out, of course) we go on to write a simple SQL statement. We'll ignore that, as it's nothing interesting. Instead lets take apart the foreach () statement.

foreach ($db->query ($sql) as $row)
{
	echo "

Current row: {$row['id']}

"; }

The $db variable, naturally, contains our PDO object. And here we're calling its query () method, passing through the SQL statement we defined just above. The query () returns an associative array of our results. Hence why we're using a foreach () statement on it.

 

More PDO

The above is fine and dandy, but there are some down sides to the query () method. First off it doesn't escape statements, thus it's not fit to be used any time there is outside influence on the SQL. Also it's very inefficient for any SQL that you might have to call multiple times. We can kill two birds with one stone by using three new methods: prepare (), execute () and fetchAll ().

$sql = $db->prepare ("SELECT * FROM my_table ORDER BY some_col DESC");
$sql->execute ();

foreach ($sql->fetchAll () as $row)
{
	echo "

Current row: {$row['username']}

"; }

It's a little more complex, but I assure you the above is much more efficient and it's safe. The prepare () statement is a little deceiving. At first you may think that it's job is to escape the statement, thus preparing it for querying. This however isn't the case. It will protect your statements, yes. But what also happens, and more importantly, is it returns a PDOStatement object. The PDOStatement class, a sub-class of PDO, allows you to perform a whole slew of actions against the SQL passed through the prepare () statement.

Notice how on the following lines we're no longer making method calls on our $db variable (the PDO object)? The execute () and fetchAll () methods belong to PDOStatement. You can think of PDOStatement as a SQL statement on steroids, in a suit of armor. Instead of having to interact with the king (PDO), you can interact with the foot soldier that's doing the real work (PDOStatement). Lame analogy, but work with me here.

It should become clear now how this route makes executing the same SQL statement multiple times much more efficient than how we were doing it before. We called the prepare () statement once, and now we can execute that specific PDOStatement object over and over again as we please, without the needed resources for multiple query () calls on the same bit of SQL.

With that said, the execute () statement does just like sounds. It executes (queries) the SQL code that was passed to the PDOStatement object during the prepare () call. In order to access the results of the query we use the fetchAll () method.

foreach ($sql->fetchAll () as $row)
{
	echo "

Current row: {$row['id']}"; }

This foreach () should look familiar to you- it's almost the exact same as the first example, with the query () method. Instead, because we're working through PDOStatement, we use the fetchAll () statement. When we called execute () it stored the results within the PDOStatement class itself. To be specific, fetchAll () returns an array containing both numeric and associative indices. In our example we're choosing to just use the associative index.

 

The Real Power Behind PDOStatement

I'm giving this its own section because I like it just that much. Reading from a database is fine and dandy, but inserting is necessary at some point. And we all know how messy INSERT statements canget. You either have a load of string concatenations, use the sprintf () function, or ignore all security measures and into form-fed variables directly. Either way, it gets messy. PDOStatement to the rescue!

$sql = $db->prepare ("INSERT INTO my_table (col_one, col_two) VALUES (:something, :another_thing)");

$sql->bindValue (":something", "nifty");
$sql->bindValue (":another_thing", "nice and clean!");

$sql->execute ();

Take a look at the INSERT statement. Specifically the VALUES definition. Those two labels, started with the colon (:something and :another_thing) are called place holders. And they do just that- they represent data that's going to be replaced during the execute () statement.

The following two lines show how you replace a place holder with actual data. In a real application this is where you'd feed in form-generated data. Another nice feature of the bindValue () method is the third, but optional, parameter which allows you to designate a specific data type for that field. If you're as much of a stickler about constraints within your code as I am, then your bindValue () statements will always look like this:

$sql->bindValue (":username", $username, PDO_PARAM_STR);
$sql->bindValue (":email_opt_in", $emailOpt, PDO_PARAM_BOOL);
$sql->bindValue (":birthday_month", $birthMonth, PDO_PARAM_INT);

Of course as I mentioned above, that third parameter is entirely optional. But I highly recommend setting it, as it'll prevent any unwanted data types creeping into your columns. There are other ways to work with place holders, But I'll leave that to you to get into, as the above method is generally efficient enough for any given situation.

 

Wrapping Up

I'm going to wrap up this introduction up with some miscellaneous methods that I think you may find interesting. Remember that anything marked as PDO is accessed via the variable you assign to the PDO object, and anything marked PDOStatementis accessed via the variable you assigned to the PDOStatement object (through the prepare () statement, for example).

PDO::quote (Escapes special characters within, and quotes the passed string- useful when using the PDO::Query () method)

$sql = $db->quote ("SELECT * FROM my_table ORDER BY some_col DESC");

foreach ($db->query ($sql) as $row)

PDOStatement->rowCount (Returns the number of rows affected by last statement- can be used to see if an INSERT was successful or how many rows were affected by a DELETE call, for example)

$sql = $db->prepare ("INSERT INTO login (username, password) VALUES (:username, :password)");
$sql->bindValue (":username", "hooah");
$sql->bindValue (":password", "oscarmike");
$sql->execute ();

echo "

{$sql->rowCount ()}

";

PDOStatement->setFetchMode (Allows you to set the mode for fetch, fetchAll, fetchObject- example: strictly associative or strictly numerical)

$sql->setFetchMode (PDO::FETCH_NUM);

foreach ($sql->fetchAll () as $row)
{
	echo "

Current row: {$row[1]}

"; }

Well that about covers all of the basics behind PDO.  Be sure to speak up and point outwhere I wasn't descriptive enough! And as I promised, some useful links for you to look at.

Dig this tutorial?
Thank the author by sending him a few P2L credits!

Send
Bobby Hensley

I'm Bobby Hensley, a 21 year old software engineer from the United States. My primary focuses are C++, Ruby and PHP.
View Full Profile Add as Friend Send PM
Pixel2Life Home Advanced Search Search Tutorial Index Publish Tutorials Community Forums Web Hosting P2L On Facebook P2L On Twitter P2L Feeds Tutorial Index Publish Tutorials Community Forums Web Hosting P2L On Facebook P2L On Twitter P2L Feeds Pixel2life Homepage Submit a Tutorial Publish a Tutorial Join our Forums P2L Marketplace Advertise on P2L P2L Website Hosting Help and FAQ Topsites Link Exchange P2L RSS Feeds P2L Sitemap Contact Us Privacy Statement Legal P2L Facebook Fanpage Follow us on Twitter P2L Studios Portal P2L Website Hosting Back to Top