Ou r 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 fi rst 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 fetch styles. We already know the PDO::
FETCH_ASSOC mode that returns an array indexed by column name. The default
operation of the PDOStatement object is to return an array indexed by both an
integer index and a column name, that is the PDO::FETCH_BOTH fetch mode. We
can also request only an integer-indexed array by using the PDO::FETCH_NUM fetch
style. PDO also supports fetching rows as objects with the PDO::FETCH_OBJ mode.
In this case the call to PDO::fetch()method will return an instance of the stdClass
internal class with its properties populated with the row's values. This happens in
the following code:
$q = $conn->query('SELECT * FROM authors ORDER BY lastName,
firstName');
$r = $q->fetch(PDO::FETCH_OBJ);
var_dump($r);
//would print:
object(stdClass)#4 (4)
{
["id"]=>
string(1) "3"
["firstName"]=>
string(7) "Cameron"
["lastName"]=>
string(6) "Cooper"
["bio"]=>
string(112) "J. Cameron Cooper has been playing around on the web
since there was not much of a web with which to play around"
}
The PDOStatement class also allows you to set the fetch mode once for all subsequent
calls to its fetch() method. This is done via the PDOStatement::setFetchMode()
method, which accepts any of the PDO::FETCH_ASSOC, PDO::FETCH_BOTH, PDO::
FETCH_NUM, and PDO::FETCH_OBJ constants. With this in mind, we can rewrite lines
23 and 24 of the authors.php fi le to look like this:
// Now iterate over every row and display it
$q->setFetchMode(PDO::FETCH_ASSOC);
while($r = $q->fetch())
{
You can try it on your copy of the authors.php fi le and refresh the browser to see
that this works.
You may have noticed that the SQLite, MySQL, and pgSQL PHP extensions all
offer similar functionality. Indeed, we can use any of the mysql_fetch_row(),
mysql_fetch_assoc(), mysql_fetch_array(), or mysql_fetch_object()
functions to achieve the same effect. That's why PDO goes further and enables
us to use three additional fetch modes. These three modes can be only set via
PDOStatement::setFetchMode() call, and here they are:
PDO::FETCH_COLUMN allows you to instruct the PDOStatement object
to return the specifi ed column of every row. In this case, PDO::fetch()
will return a scalar value. The columns are numbered starting with 0. This
happens in the following code snippet:
$q = $conn->query('SELECT * FROM authors ORDER BY lastName,
firstName');
$q->setFetchMode(PDO::FETCH_COLUMN, 1);
while($r = $q->fetch())
{
var_dump($r);
}
//would print:
string(7) "Cameron"
string(4) "Marc"
string(6) "Sohail"
This reveals that the call to $q->fetch() does indeed returns scalar values
(not arrays). Note that the column with the index 1 should be the author's
last name, not their fi rst name, if you are simply looking at the page with
authors list. However, our query looks like SELECT * FROM authors, so it also
retrieves the author ids, which are stored into the 0th column. You should be
aware of this, as you may spend hours looking for the source of such a
logical error.
PDO::FETCH_INTO can be used to modify an instance of an object. Let's
rewrite our above example as follows:
$q = $conn->query('SELECT * FROM authors ORDER BY lastName,
firstName');
$r = new stdClass();
$q->setFetchMode(PDO::FETCH_INTO, $r);
while($q->fetch())
{
var_dump($r);
}
//would print something like:
object(stdClass)#3 (4)
{
["id"]=>
string(1) "3"
["firstName"]=>
string(7) "Cameron"
["lastName"]=>
string(6) "Cooper"
["bio"]=>
string(112) "J. Cameron Cooper has been playing around on the
web since there was not much of a web with which to play around"
}
object(stdClass)#3 (4)
{
["id"]=>
string(1) "1"
["firstName"]=>
string(4) "Marc"
["lastName"]=>
string(7) "Delisle"
["bio"]=>
string(54) "Marc Delisle is a member of the MySQL Developer
Guide"
}
object(stdClass)#3 (4)
{
["id"]=>
string(1) "2"
["firstName"]=>
string(6) "Sohail"
["lastName"]=>
string(6) "Salehi"
["bio"]=>
string(101) "In recent years, Sohail has contributed to over 20
books, mainly in programming and computer graphics"
}
PDO::FETCH_CLASS can be used to return objects of a specifi ed class. For
every row, an instance of this class will be created with the properties named
and assigned the values of the result set columns. Note that the class does not
necessarily have these properties declared since PHP allows runtime creation
of object properties. For example:
$q = $conn->query('SELECT * FROM authors ORDER BY lastName,
firstName');
$q->setFetchMode(PDO::FETCH_CLASS, stdClass);
while($r = $q->fetch())
{
var_dump($r);
}
This will print output similar to that for the previous example. Also, this
fetch mode allows you to create instances by passing an array of parameters
to their constructors:
$q->setFetchMode(PDO::FETCH_CLASS, SomeClass, array(1, 2, 3));
(This will work only if the SomeClass class has been defi ned.)
We would recommend using PDOStatement::setFetchMode() as it is more
convenient and easier to maintain (and, of course, has more features).
Describing all of these fetch modes may seem excessive, but each of them
is useful in certain situations. Indeed, you may have noticed that the list of
books is somewhat incomplete. It does not contain the author's name. We will
add this missing column, and to make our example trickier, we will make the
author's name clickable and link it to the author's profi le page (which we will
create). This profi le page needs the author's ID so that we can pass it in the
URL. It will display all the information that we have about the author, as well
as the list of all of their books. Let's begin with this author's profi le page:
query("SELECT * FROM authors WHERE id=$id");
$author = $q->fetch(PDO::FETCH_ASSOC);
$q->closeCursor();
// Now see if the author is valid - if it's not,
// we have an invalid ID
if(!$author) {
showHeader('Error');
echo "Invalid Author ID supplied";
showFooter();
exit;
}
// Display the header - we have no error
showHeader("Author: $author[firstName] $author[lastName]");
// Now fetch all his books
$q = $conn->query("SELECT * FROM books WHERE author=$id ORDER
BY title");
$q->setFetchMode(PDO::FETCH_ASSOC);
// now display everything
?>
Author
| First Name | |
| Last Name | |
| Bio |
Books
fetch()) { ?>| Title | ISBN | Publisher | Year | Summary |
the following chapters. Here we will just note that it's a good idea to call this
method between queries executed on the same connection object and then set
it to null. Our example would not work without it. Also note that we don't
need this after the last query.
We also do simple error handling here: we check whether the author ID is
invalid. If it is invalid, we display an error message and then exit. (See lines
22 to 27.)
On lines 25 and 27, we use the author's ID to create the query and set the
fetch mode to be PDO::FETCH_ASSOC. Then we proceed to the display of data:
fi rst we render the author's details and then all his books.
Now you can return to your browser and point it to the URL:
http://localhost/author.php?id=1.
As you can see, everything is correct on the page: The author's details, which we
fi led fi rst (id=1), and the only book by this author. Now let's see how our application
reacts to an invalid ID submitted. We know that we have only three authors, so any
number other than 1, 2, or 3 is invalid. Also, a non-number parameter will evaluate
to 0, which is invalid. If we change the URL in the address bar to
http://localhost/author.php?id=zzz. We will end up with the following:
You should also switch to SQLite in common.inc.php and see that this page also
works with this database.
Now, let's modify our existing books.php fi le to add an author column with a link
to the author's profi le page. We will have to join the two tables where the book's
author fi eld equals the author's ID fi eld, and select the author's ID, fi rst name, and
last name. So our query will look like this:
SELECT authors.id, authors.firstName, authors.lastName, books.* FROM
authors, books WHERE author=authors.id ORDER BY title;
Before we proceed with the changes, let's run this query in the command line client.
We will also modify this query for the client as its window will not fi t the whole row:
mysql> SELECT authors.id, firstName, lastName, books.id, title FROM
authors, books WHERE books.author=authors.id;
+----+-----------+----------+----+------------------------------+
| id | firstName | lastName | id | title |
+----+-----------+----------+----+------------------------------+
| 1 | Marc | Delisle | 1 | Creating your MySQL... |
| 2 | Sohail | Salehi | 2 | ImageMagick Tricks |
| 3 | Cameron | Cooper | 3 | Building Websites with Plone |
+----+-----------+----------+----+------------------------------+
3 rows in set (0.00 sec)
As you can see, the query is returning two columns called id. This means that we
will not be able to use the PDO::FETCH_ASSOC mode, since there can be only id array
index. Here we have two options: Either use the PDO::FETCH_NUM mode or retrieve
the ID fi elds using aliases.
Let's see how we would code the page using PDO::FETCH_NUM:
query("SELECT authors.id, firstName, lastName, books.*
FROM authors, books WHERE author=authors.id ORDER
BY title");
$q->setFetchMode(PDO::FETCH_NUM);
// Display the header
showHeader('Books');
// now create the table
?>
fetch()) { ?>
| Author | Title | ISBN | Publisher | Year | Summary |
numeric column indexes.
If we navigate back to http://localhost/books.php, we will see a list similar to
the one in this screenshot:
We can click on every author to get to their profi le page. Of course, changing back to
SQLite in common.inc.php should also work.
Another (and much better) option is to use aliases for column names in the SQL
code. If we do this, we will not have to take care of the numeric indexes and change
the code every time we add or remove columns from our tables. We just change the
SQL to the following:
SELECT authors.id AS authorId, firstName, lastName, books.* FROM
authors, books WHERE author=authors.id ORDER BY title;
The fi nal version of books.php will look like this:
query("SELECT authors.id AS authorId, firstName,
lastName, books.* FROM authors, books WHERE
author=authors.id
ORDER BY title");
$q->setFetchMode(PDO::FETCH_ASSOC);
// Display the header
showHeader('Books');
// now create the table
?>
fetch()) { ?>
| Author | Title | ISBN | Publisher | Year | Summary |
lastName, books.* FROM authors, books WHERE
author=authors.id ORDER BY title");
$books = $q->fetchAll(PDO::FETCH_ASSOC);
// Display the header
showHeader('Books');
// now create the table
?>
| Author | Title | ISBN | Publisher | Year | Summary |
line 5 and then iterate over it with a foreach loop on line 21. If you run the modifi ed
page, you will see that we receive the same result. This will also work if we change to
SQLite database in the common.inc.php fi le.
The PDOStatement::fetchAll() method also allows us to select the values of a
single column with the PDO::FETCH_COLUMN mode. If we want to fetch the entire
book titles using the query from the last example, we can do the following (note the
number and ordering of columns):
$q = $conn->query("SELECT authors.id AS authorId, firstName,
lastName, books.* FROM authors, books WHERE
author=authors.id ORDER BY title");
$books = $q->fetchAll(PDO::FETCH_COLUMN, 5);
var_dump($books);
This would give the following output:
array(3)
{
[0]=>
string(28) "Building Websites with Plone"
[1]=>
string(66) "Creating your MySQL Database: Practical Design Tips and
Techniques"
[2]=>
string(18) "ImageMagick Tricks"
}
As you can see, when a single column is requested, this method returns
one-dimensional array.
