PHP Tutorial: An Introduction to PDO

July 5th, 2011 Leave a comment
Like the article?
PHP PDO

Since its inception, PHP has moved from a procedural language to an object-oriented one; some of its procedural roots, however, still linger. One of these procedural remnants is an antique method of accessing SQL databases; many programmers still use the older mysql and postgresql procedural modules to access databases, and even those who have switched to newer methods, like mysqli, might still be hampered by the fact that such a module only works with mysqli. PHP has since implemented better methods, but many procedural PHP developers are unsure of how to use newer, object-oriented MySQL connection methods. Let’s delve into one of them, PDO, to see how it works!

Why Use PDO?

This one could be a book in and of itself, but the short answer is this: Many PHP programmers are still using legacy mysql or mysqli connections; PDO (which stands for “PHP Data Object”) was designed to offer an object-oriented, database-generic way of accessing and manipulating databases. It offers the best of both worlds; it’s object-oriented, unlike the procedural mysql module, and it’s database-agnostic, unlike the mysqli module, making it a great solution for a PHP program that interfaces with databases, MySQL or otherwise.

Step 1: Make sure your PHP install supports PDO

This can be found out by running a phpinfo page. If you see "pdo_mysql" on the page, then you’re good to go; most installations above PHP 5.3 will have it enabled by default. If not, follow these steps:

Windows

Uncomment the following line in your php.ini:

extension=php_pdo.dll

Linux

Compile PHP with the following arguments:

--enable-pdo   --with_pdo_mysql

Step 2: Connecting to A Database

Now the next part is connecting to the database. Fairly straightforward here:

$hostname = 'dbhost';
$username = 'user';
$password = 'supersecretpass';
$dbconn = new PDO("mysql:host=$hostname;dbname=testdb", $username, $password);

For previous PHP/MySQL users, that looks quite a bit different, but in fact it’s not all that hard to follow; those programmers used to procedural languages, however, might be a little lost. What’s happening here is instead of connecting using mysql_connect, as you might be used to, you are instead creating an object (properly called a database handle object)whose functions you will call to perform queries on the database. It’s an object-oriented interface through which you’ll manipulate the database.

By the way, PDO is in fact database-agnostic. It has many different drivers for different databases, including PostgreSQL, Firebird, Oracle, ODBC, SQLite, and more. All you have to do to migrate to a different database is change the database driver type, like so:

$dbconn = new PDO("mssql:host=$hostname;dbname=testdb", $username, $password);

You’ll notice now that it’s using Microsoft SQL! Very easy, and far more interchangeable than mysql or mysqli. Connecting to SQLite databases is a little different, since SQLite databases are actually local files stored on the machine. To connect to one of those, use this line:

$dbconn = new PDO("sqlite:path/to/database/database.db");

This interchangeability makes PDO enormously useful when writing a PHP program that needs to be able to interface with different databases, and not just MySQL. All you have to do is change the database type in your original connect call and you’re set!

Step 3: Communicating with the database: exec and query

A connection with the database is all well and good, but it’s not going to be much use to us if we can’t manipulate it at all. PDO’s method of handling queries in SQL is to have two functions: PDO::exec and PDO::query; PDO::exec is used for running queries that do not return information (like INSERT or UPDATE queries, for example) while PDO::query is used for queries that return information (like SELECT).

For example, let’s say that we wanted to insert a new person into the database. After connecting to the database as shown above, we’d run:

$count = $dbconn->exec("INSERT INTO employees(firstname, lastname) VALUES ('John', 'Smith')");

PDO::exec just returns the count of rows affected. For queries, we’d run:

$sql = "SELECT * FROM employees";
foreach ($dbconn->query($sql) as $row) {
  print $row['firstname'] . ' - ' . $row['lastname'] . '
'; }

As you can see, the method for fetching the rows from a query is a little different from the procedural MySQL. Instead of all that business with mysql_get_results, the method is implemented in a nice little for loop.

So what’s the difference between these two? Well, PDO::exec doesn’t return results; it’s simply used to run queries that affect rows and don’t require output returned to them. PDO::query can technically be used in place of PDO::exec for running queries that just affect rows, but PDO::exec is much faster and useful for that purpose.

Step 4: PDO::quote

Some more zealous coders among you might notice that the queries above are quite vulnerable to SQL injection and other malicious strings. PDO offers a function to help with that as well called PDO::quote. PDO::quote works by quoting the input string and escapes while putting single quotes within the input string, stopping your SQL server from happily running queries it wasn’t designed to:

$address_str = '307 Wiltshire Blvd';
$address = $dbconn->quote($address_str);
$sql = $dbconn->exec("INSERT INTO employees (address) VALUES ($address)");

Conclusion

The above tutorial has shown you a little bit of PDO, how to use it, and why it’s attractive to developers. It is, of course, far deeper than the very basic introduction tutorial given above: PDO has prepared statements, fetching abilities, transactional capabilities, and more. Those are outside the scope of this article (though not outside the scope of a future article, potentially). The point of this article was more to introduce PDO to programmers who have been using the mysql or mysqli modules, and give them an idea of why they might want to switch over to PDO!

Help us spread the word!
  • Twitter
  • Facebook
  • LinkedIn
  • Pinterest
  • Delicious
  • DZone
  • Reddit
  • Sphinn
  • StumbleUpon
  • Google Plus
  • RSS
  • Email
  • Print
If you liked this article, consider enrolling in one of these related courses:
Don't miss another post! Receive updates via email!

Comment