Using Databases with PDO. What is PDO?

In the “old days” of the Internet, most web pages were nothing more than text files containing HTML. When people visited your site, your web server simply made the file available to their browsers. This approach started out fine, but as web sites grew, and issues such as design and navigation became more important, developers found that maintaining consistency across hundreds of HTML files was becoming a massive headache. To solve this problem, it became popular to separate variable content (articles, news items, and so on) from the static elements of the site — its design and layout.

If a database is used as a repository to store variable content, a server-side language such as PHP performs the task of fetching that data and placing it within a uniform layout template. This means that modifying the look and feel of a site can be handled as a separate task from the maintenance of content. And maintaining consistency across all the pages in a web site no longer consumes a developer’s every waking hour.

PHP supports all the relational databases worth mentioning, including those that are commonly used in large companies: Oracle, IBM’s DB2, and Microsoft’s SQL Server, to name a few. The three most noteworthy open source alternatives are SQLite, PostgreSQL, and MySQL. PostgreSQL is arguably the best database of the three, in that it supports more of the features that are common to relational databases. SQLite is the perfect choice for smaller applications that still require database capability. MySQL is a popular choice among web hosts that provide support for PHP, and for this reason is typically easier to find than PostgreSQL.

This chapter covers all the common operations that PHP developers perform when working with databases: retrieving and modifying data, and searching and backing up the database. To achieve these tasks, we’ll use the built-in PDO extension, rather than database-specific extensions. The examples we’ll work with will use a single table, so no discussion is made of table relationships here. For a full discussion of that topic, see Kevin Yank’s Build Your Own Database Driven Website Using PHP & MySQL, 3rd Edition (SitePoint, Melbourne, 2006) .

The examples included here work with the MySQL sample database called “world,” though all the interactions we’ll work through can be undertaken with any database supported by PDO. The SQL file for the world database is available at http://dev.mysql.com/doc/#sampledb and the instructions explaining its use can be found at http://dev.mysql.com/doc/world-setup/en/world-setup.html.

What is PDO?

PDO, the PHP Data Objects extension, is a data-access abstraction layer. But what the heck is that? Basically, it’s a consistent interface for multiple databases. No longer will you have to use the mysql_* functions, the sqlite_* functions, or the pg_* functions, or write wrappers for them to work with your database. Instead, you can simply use the PDO interface to work with all three functions using the same methods. And, if you change databases, you’ll only have to change the DSN (or Data Source Name) of the PDO to make your code work.

PDO uses specific database drivers to interact with various databases, so you can’t use PDO by itself. You’ll need to enable the drivers you’ll use with PDO, so be sure to research how to do it for your specific host operating system on the PDO manual page.

PDO is shipped with PHP 5.1 and is available from PECL for PHP 5.0. Unfortunately, as PDO requires the new PHP 5 object oriented features, it’s not available for PHP 4. In this book, all of our interactions with the database will use PDO to interact with the MySQL back end.