Handling Result Sets and More with PDO Objects in PHP 5

Building PHP applications that interact with different database systems can be a daunting task, especially from a developer’s point of view. To tackle this issue with minor hassles, PHP 5.1 comes bundled with a powerful extension called PDO (short for PHP Data Objects), a library that definitely takes database abstraction to the ultimate level.

Introduction

Welcome to the second installment of the series that began with "Using PDO objects in PHP 5." In three approachable tutorials, this series walks you through the implementation of the most important features offered by the PDO extension, and complements its theoretical aspects with concrete examples. In this way you will be able to start quickly using it in your own PHP applications.

Stepping back for a moment to the first article of the series, it’s quite probable that you’ll recall how this educational journey began. In simple terms, I started explaining some basic tasks that can be performed with the PDO extension, such as opening and closing a connection to diverse database systems (MySQL, Oracle, MS SQL, etc.), in addition to running queries against a selected database and handling PDO exceptions.

Also, it’s important to notice here that this PHP library lets you perform all the tasks mentioned above by using an object-oriented notation. This means that if you use this paradigm on a frequent basis when developing your PHP programs, you should take it for granted that you’ll feel really comfortable implementing most of its features.

All in all, at this point you hopefully learned how to use some basic methods included with this PHP extension, but logically you may want to learn a few additional ones, so you can have a more polished picture of the overall functionality of the library in question.

Thus, keeping this in mind, over the course of this second tutorial of the series, I’m going to show you how to use the PDO extension to handle different database result sets in all sort of clever ways, in addition to performing some common tasks, like counting rows and columns as well.

Are you ready to take the next step in this instructive journey? Okay, let’s begin now!

{mospagebreak title=Preparing queries and fetching database rows}

In consonance with the concepts deployed at the beginning of this article, the next group of PDO-related methods that I plan to teach you are those aimed at preparing/executing queries and fetching database rows.

But first I’d like to introduce at least basically the concept of "prepared" queries, since I’m going to use it with many of the next hands-on examples. Summarizing, a prepared query can be considered a SQL statement that was previously compiled by the selected database system, allowing the inclusion of parameters inside the query itself, which is properly analyzed, compiled and finally optimized.

As you can see, this concept can be extremely useful when working with SQL queries that are potentially complex in their definition. Once the query in question has been compiled by the database system, it can be executed repeatedly, without consuming additional computational resources. Pretty efficient, right?

Now that you hopefully grasped the exact meaning of a "prepared" query, let me show you some basic examples of how to execute this type of SQL statement using the PDO extension.

In this case, three brand new methods are provided by the extension, called "prepare()," "execute()" and "fetch()" respectively. The first two are aimed obviously at preparing/running a specified SQL query, while the last one is useful to fetch one database row at a time.

Also, it should be noticed that all the practical examples that I’m going to include in the next few lines won’t use parameters inside the corresponding queries, since this feature will be covered profusely in the next tutorial of the series.

Therefore, at least for now, have a look at the following code samples to learn how to fetch rows from a sample "USERS" database table using the corresponding "fetch()" PDO method.

Here are the examples:

// example using the ‘fetch()’ method with PDO::FETCH_ASSOC
constant (return database table row as an associative array)
try{
   $dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,’user’,’password’);
   $dbh->prepare(‘SELECT * FROM users’);
   $dbh->execute();
   $result=$dbh->fetch(PDO::FETCH_ASSOC);
   print_r($result);
}
catch(PDOException $e){
   echo ‘Error : ‘.$e->getMessage();
   exit();
}

// example using the ‘fetch()’ method with PDO::FETCH_BOTH
constant (return database table row as an associative and numeric
array)
try{
   $dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,’user’,’password’);
   $dbh->prepare(‘SELECT * FROM users’);
   $dbh->execute();
   $result=$dbh->fetch(PDO::FETCH_BOTH);
   print_r($result);
}
catch(PDOException $e){
   echo ‘Error : ‘.$e->getMessage();
   exit();
} 

// example using the ‘fetch()’ method with PDO::FETCH_OBJ
constant (returns an object with column names as properties)
try{
   $dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,’user’,’password’);
   $dbh->prepare(‘SELECT * FROM users’);
   $dbh->execute();
   $result=$dbh->fetch(PDO::FETCH_OBJ);
   echo ‘Name : ‘.$result->NAME.’ Postal Address : ‘.$result-
>ADDRESS.’ Email : ‘.$result->EMAIL;
}
catch(PDOException $e){
   echo ‘Error : ‘.$e->getMessage();
   exit();
}

As you can see, the three examples shown above first use the "prepare()" and "execute()" methods to run a simple SELECT query against a sample database, and then the returned rows are retrieved via the "fetch()" method that I introduced before.

Besides, you should notice that in each case I used three different constants, that is PDO::FETCH_ASSOC, PDO::FETCH_BOTH, and PDO::FETCH_OBJ, precisely to fetch database rows as an associative/numeric array, and finally as an object.

So far, so good. At this point I believe that the previous methods shouldn’t be hard to understand at all, since they behave closely similar to the PHP functions included with the MySQL library that you’ll certainly have used hundreds of times. Therefore, it’s time to move forward and continue exploring more methods that come integrated with the PDO extension.

And speaking of that, in the section to come, I’m going to show you how to use the PDO library to fetch all the rows contained into a given result set, in addition to manipulating separately its respective columns.

To see how all these useful tasks can be performed with PDO objects, go ahead and read the next few lines.

{mospagebreak title=Fetching database rows and columns}

If you’re anything like me, then it’s quite possible that you found yourself defining a PHP function (or eventually a class) that had the capacity for counting the rows and columns of a given result set. In this case, luck is on your side, since the PDO extension performs all these tasks effortlessly, due to the existence of two handy methods: "fetchAll()" and "fetchColumn()."

As you may guess, the first one can be really useful when it comes to retrieving all the rows contained into a returned data set, while the second one simply fetches a specific column.

To demonstrate how these new methods work, below I coded a bunch of examples that should give you a clear idea of their functionality. The corresponding code samples are as follow:

// example using the ‘fetchAll()’ method (fetches all the rows
contained in a result set)
try{
  
$dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,’user’,’password’);
  
$dbh->prepare(‘SELECT * FROM users’);
  
$dbh->execute();
  
$result=$dbh->fetchAll();
  
print_r($result);
}
catch(PDOException $e){
  
echo ‘Error : ‘.$e->getMessage();
  
exit();
} 

// example using the ‘fetchAll()’ method to fetch fourth row in a
result set
try{
  
$dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,’user’,’password’);
  
$dbh->prepare(‘SELECT * FROM users’);
  
$dbh->execute();
  
$result=$dbh->fetchAll(PDO::FETCH_COLUMN,3);
  
print_r($result);
}
catch(PDOException $e) {
   echo ‘Error : ‘.$e->getMessage();
   exit();
}

// example using the ‘fetchColumn()’ method (fetches the first
column in a result set)
try{
  
$dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,’user’,’password’);
  
$dbh->prepare(‘SELECT name,address,email FROM users’);
  
$dbh->execute();
  
$result=$dbh->fetchColumn();
  
echo ‘Name : ‘.$result.'<br />';
  
$result=$dbh->fetchColumn(1);
  
echo ‘Postal Address :’.$result.'<br />';
  
$result=$dbh->fetchColumn(2);
  
echo ‘Email :’.$result.'<br />';
}
catch(PDOException $e) {
  
echo ‘Error : ‘.$e->getMessage();
  
exit();
}

See how easy it is to fetch rows and columns using PDO objects? I bet you’ll agree with me in that concept, after examining the examples listed above. As you can see, in the first two cases, I used the "fecthAll()" method to retrieve all the rows included into a returned result set, and then fetch the fourth database record (note the specification of the PDO::FETCH_COLUMN constant in the last example).

Now, with reference to the other two examples, the "fetchColumn()" method is utilized in these cases to retrieve the first and second columns of the same result set, this time passing to the method in question the corresponding offset.

Okay, having explained at least in a basic way how the previous methods do their business, it’s now a good time to explore a few more additional features that come packaged with the useful PDO extension.

In this specific case, I’m going to teach you how to use a couple of extra methods, obviously included with this PHP library, which come in handy for counting the number of database rows affected after performing INSERT, UPDATE and DELETE statements, in addition to determining the amount of columns contained in a specific result set.

Indeed, these methods can be really helpful, so click on the link below to learn more about them.

{mospagebreak title=Counting affected rows and columns}

As I stated in the prior section, the last two methods that I plan to review in this tutorial are aimed specifically at determining the number of database rows affected after performing an insertion, update and deletion operation, in addition to counting the columns present in a data set.

These usual database-related tasks are carried out by the "rowCount()" and "columnCount()" methods respectively, which are bundled with the PDO extension. But first, let me get rid of their theoretical aspects and show you a pair of examples that should dissipate any possible doubts about the way they work.

Look at the following code samples to see these useful methods in action:

// example using the ‘rowCount()’ method (returns the number of
rows affected by a query)
try{
   $dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,’user’,’password’);
  
$dbh->prepare(‘DELETE * FROM users WHERE id<20′);
  
$dbh->execute();
  
$delrows=$dbh->rowCount();
  
echo ‘Number of deleted rows after executing SQL statement is
as following: ‘.$delrows;
 

   /*
  
displays the following:
  
Number of deleted rows after executing SQL statement is as
following: 10
  
*/ 

}
catch(PDOException $e) {
  
echo ‘Error : ‘.$e->getMessage();
  
exit();
} 

// example using the ‘columnCount()’ method (returns the number
of columns in a result set)
try{
  
$dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,’user’,’password’);
  
$dbh->prepare(‘SELECT * FROM users’);
  
$dbh->execute();
  
$cols=$dbh->columnCount();
   echo ‘Number of columns in result set after executing SQL
statement is as following: ‘.$cols;
 

   /*
  
displays the following:
  
Number of columns in result set after executing SQL statement
is as following: 4
  
*/ 

}
catch(PDOException $e) {
  
echo ‘Error : ‘.$e->getMessage();
  
exit();
}

As you can see, counting affected database rows and columns can be a no-brainer process if you have the assistance of these two methods packaged with the PDO extension. Of course, in this case I’m only demonstrating a rather basic implementation of them, so I recommend that you develop your own testing examples to see more clearly how these methods work.

Final thoughts

Sadly, we’ve come to the end of this article. As you saw, the PDO library comes armed with a neat arsenal of methods for tackling the most common tasks associated with different database systems.

Nonetheless, to be frank, the power of this PHP library hasn’t been completely revealed yet, since there are some remarkable features that need to be reviewed properly, including the implementation of prepared queries with parameters.

All of these interesting topics will be covered in the last part of the series, so I don’t think you want to miss it!

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort