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

Preparing queries and fetching database rows - 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

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.



 
 
>>> 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: