Using PHP Data Objects:
First Steps
In the previous chapter, we had a brief overview of what PDO is, how to connect to
your favourite database using PDO, how to issue simple queries and how to handle
errors. Now that you are convinced that PDO is a good thing and are thinking of
using it actively, we will be delving into all the features it has to offer.
In this chapter, we will look more closely at creating connections to a database using
PDO and connection strings (data source names), the PDOStatement class, and how
to traverse result sets. We will also create a small library management application,
which will allow us to manage a collection of books of your home library. The
application will be able to list books and authors as well as add and edit them.
We will start by having a look at connection strings, since without them, we will not
be able to connect to any database. We will then create a sample database, on which
all the examples in this book will be based.
We will depart from the simplistic, imaginary cars' database and create a real
working database with several tables. However, now we will be dealing with the
classical example of books and authors. We chose this example because such entities
are more common. The relational model will be relatively simple, so that you will
be able to follow the examples easily, if you have already come across such a
database elsewhere.
Connection Strings
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 defi ned in a confi guration fi le and that fi le gets
processed by your application. Should your database (data source) change, you just
edit that confi guration fi le 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 prefi x, 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:host=localhost;dbname=cars
sqlite:/path/to/cars.db
pgsql:host=localhost dbname=cars
As we can see, the prefi x (the substring before the fi rst semicolon) always keeps the
name of the PDO driver. Since we don't have to use different functions to create a
connection with PDO, this prefi x tells us which internal driver should be used. The
rest of the string is parsed by that driver to further initiate the connection. In these
cases we supplied the database name; for MySQL and PostgreSQL; we also supplied
the host name on which the server runs. (As SQLite is a local database engine, such a
parameter would not make sense.)
If you want to specify additional parameters, you should consult your database
manual (www.php.net/pdo is always a good place to start). For example, the MySQL
PDO driver understands the following parameters:
host - the hostname on which the server runs (localhost in our example)
port - the port number where the database server is listening
(defaults to 3306)
dbname - the name of the database (cars in our example)
unix_socket - the MySQL UNIX socket (instead of host and/or port).
As you might have noticed, different drivers use different character to delimit the
parameters—such as a semicolon in MySQL and space in PostgreSQL.
Creating the Sample Database
Suppose that you have a good library at home and you want your computer to help
you manage it. You decide to create a web-based database using PHP and, of course,
PDO. From now on, the examples will be for MySQL and SQLite databases.
The Data Model
As our database is really simple, we will just have two entities in it: authors and
books. Hence, we will be creating two tables with the same names. Now, let's think
what properties each of these entities will have.
Authors will have their fi rst name, their last name, and a short biography. The table
will need to have a primary key which we will call id. We will use it to refer to an
author from the books table.
Books are written by authors. (Sometimes they are written by more than one author,
but we will consider books written by only one author here.) So we will need a fi eld
for the author’s ID, as well as the book’s title, ISBN number, publisher name, and
year of publication. Also, we will include a short summary of what the book is about.
We need for a separate table for authors, because an author might have written
more than one book. Also, our example would be really simple otherwise! Thus, we
opted for a two-table database structure. If we were to consider books written by
more than one author, we would need three tables, which would make the example
very complicated.
Creating the MySQL Database
After you have launched your MySQL command line client, you will see the mysql>
prompt, where you will be able to issue commands to create the database and the
tables in it:
mysql> create database pdo;
Query OK, 1 row affected (0.05 sec)
mysql> use pdo;
Database changed
mysql> create table books(
-> id int primary key not null auto_increment,
-> author int not null,
-> title varchar(70) not null,
-> isbn varchar(20),
-> publisher varchar(30) not null,
-> year int(4) not null,
-> summary text(2048));
Query OK, 0 rows affected (0.17 sec)
mysql> create table authors(
-> id int primary key not null auto_increment,
-> firstName varchar(30) not null,
-> lastName varchar(40) not null,
-> bio text(2048));
Query OK, 0 rows affected (0.00 sec)
As you can see, we have created a database and called it pdo. We also created two
tables: books and authors, just as we had planned. Now let's see how we do that in
SQLite. As we cannot create the database inside the SQLite command line client, we
launch it like this:
> sqlite3 pdo.db
sqlite> create table books(
...> id integer primary key,
...> author integer(11) not null,
...> title varchar(70) not null,
...> isbn varchar(20),
...> publisher varchar(30) not null,
...> year integer(4) not null,
...> summary text(2048));
sqlite> create table authors(
...> id integer(11) primary key,
...> firstName varchar(30) not null,
...> lastName varchar(40) not null,
...> bio text(2048));
As you can see, the SQL is slightly different for SQLite—the primary keys are
declared without the NOT NULL and auto_increment options. In SQLite, a column
declared as INTEGER PRIMARY KEY is automatically incremented. Now let's insert
some values into our database. The syntax will be the same for MySQL and SQLite
so here we will just present the MySQL command line client example. We will start
with authors, because we will need their primary key values for inserting into the
books table:
mysql> insert into authors(firstName, lastName, bio) values(
-> 'Marc', 'Delisle', 'Marc Delisle is a member of the MySQL
Developers Guide');
Query OK, 1 row affected (0.14 sec)
mysql> insert into authors(firstName, lastName, bio) values(
-> 'Sohail', 'Salehi', 'In recent years, Sohail has contributed
to over 20 books, mainly in programming and computer graphics');
Query OK, 1 row affected (0.00 sec)
mysql> insert into authors(firstName, lastName, bio) values(
-> 'Cameron', 'Cooper', 'J. Cameron Cooper has been playing
around on the web since there was not much of a web with which to
play around');
Query OK, 1 row affected (0.00 sec)
Now that we have inserted three authors, let's add some books. But before we do, we
should know which author has which id. A simple SELECT query will help us:
mysql> select id, firstName, lastName from authors;
+----+-----------+----------+
| id | firstName | lastName |
+----+-----------+----------+
| 1 | Marc | Delisle |
| 2 | Sohail | Salehi |
| 3 | Cameron | Cooper |
+----+-----------+----------+
3 rows in set (0.03 sec)
Now we can fi nally use this information to add three books, each written by one of
these authors:
mysql> insert into books(author, title, isbn, publisher, year,
summary) values(
-> 1, 'Creating your MySQL Database: Practical Design Tips and
Techniques', '1904811302', 'Packt Publishing Ltd', '2006',
-> 'A short guide for everyone on how to structure your data and
set-up your MySQL database tables efficiently and easily.');
