Learning PHP Data Objects: A Beginner's Guide to PHP Data Objects, Database Connection Abstraction Library for PHP 5

Connection strings, or data source names (abbreviated DSN) as they are called in the PDO documentation, are PHP strings that carry such information as the name of the database management system and of the database itself, as well as other connection parameters.

Their advantage over using traditional methods of creating database connection is that you don't have to modify your code if you change the database management system. A connection string can be defined in a configuration file and that file gets processed by your application. Should your database (data source) change, you just edit that configuration file and the rest of your code is kept intact.

The connection strings used in PDO differ due to the existence of different database management systems. However, they always have a common prefix, which denotes the underlying database driver. Remember the MySQL, SQLite, and PostgreSQL examples in the Chapter 1. The three connection strings looked like the following:

mysql...

Good application architecture is another key factor of an application, besides the correct data model. As the application that we are going to develop in this chapter, is relatively small, this task is not very complicated. First, we will create two pages that will list books and authors. To begin with, we should think about how these pages would look. To make our simple example small and compact, we will present a header on all pages that will contain links to the books list and the authors list. Later we will add two more pages that will allow us to add an author and a book.

Of course, we should create a common include file that will define the common functions such as the header and footer display and the connection to the database. Our example is really small, so we will not be using any template system or even object-oriented syntax. (Indeed, these topics are beyond the scope of this book.) So, to summarize:

Our examples made use of two of the main classes in PHP Data Objects: the PDO class, which is used to create a connection and issue queries, and the PDOStatement class, which we use to loop through the result set. We will look at the first of these classes in later chapters. Here, we will examine the PDOStatement class to see what other ways of traversing the result set that it has to offer.

As we already know, instances of the PDOStatement class are returned from the call to PDO::query() method. The main purpose of this class is to provide an interface to the result set. In fact, we have already used its most important method to iterate over the result set. We only looked at one fetch style (or mode of the returned row), but PDO offers several styles. This class can also provide additional information about a result set, such as the number of rows and columns, and fetch the whole result set into a two-dimensional array.

Let's begin by looking at some different...

As we have seen in the previous section, the PDOStatement class allows us to retrieve some information about the data contained in the result set. This information is called metadata, and you probably have already used some of it one way or another.

The most important metadata about a result set is, of course, the number of rows it contains. We can use the row count to enhance user experience by, for example, paginating long result sets. Our example library application is still quite small, with only three books so far, but as our database grows, we surely will need some tools to get the total row count for every table displayed and paginate it for easy browsing.

Traditionally, you would use the mysql_num_rows(), sqlite_num_rows() function or the pg_num_rows() function (depending on your database) to get the total number of rows returned by the query. In PDO, the method responsible for retrieving the number of rows is called PDOStatement::rowCount(). However...

In this chapter, we took our first steps with PDO and even created a small working database-driven, dynamic application that runs on two different databases. Now you should be able to connect to any supporting database, using the rules for constructing a connection string. You should then be able to run queries against it, and to traverse and display the result set.

In the next chapter, we will deal with a very important aspect of any database-driven application—error handling. We will also extend our example application by giving it the ability to add and edit books and authors, thus making it more realistic and useful.