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