Home arrow PHP arrow Page 3 - Handling Result Sets and More with PDO Objects in PHP 5

Fetching database rows and columns - PHP

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.

TABLE OF CONTENTS:
  1. Handling Result Sets and More with PDO Objects in PHP 5
  2. Preparing queries and fetching database rows
  3. Fetching database rows and columns
  4. Counting affected rows and columns
By: Alejandro Gervasio
Rating: starstarstarstarstar / 5
June 04, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: