Handling Result Sets and More with PDO Objects in PHP 5 - Fetching database rows and columns
(Page 3 of 4 )
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.
Next: Counting affected rows and columns >>
More PHP Articles
More By Alejandro Gervasio
