Working with Prepared Queries with PDO Objects in PHP 5

If you’re a PHP developer who builds web applications that interact with different database systems, such as MySQL, Oracle, MS SQL, and so forth, then this group of articles might be what you’re looking for. Welcome to the final part of the series that began with "Using PDO objects in PHP 5." This series shows you how to implement the most important methods that come packaged with the PDO database abstraction layer (short for PHP Data Objects).

Introduction

In the preceding article of the series, I proceeded to cover in detail a few simple methods bundled with the PDO library for performing some common tasks associated with diverse database systems. These tasks included fetching and counting the number rows present in a given result set, retrieving specific columns, and finally determining how many records were affected after executing an INSERT, UPDATE and DELETE command.

Of course, it’s important to stress here that the aforementioned operations, and the ones discussed in the first article of the series, can be easily performed using a great variety of database servers, including the most popular ones, such as MySQL, MS SQL, Oracle. This clearly reveals the real power that stands behind this robust PHP-based database abstraction layer.

Okay, at this point you may be thinking that the functionality brought by PDO objects is limited to connecting to multiple database systems, performing a few basic database-related tasks, and that’s it. Actually, the extension has been provided with many more useful features, including its capability for working with prepared queries.

As you’ll probably recall, I already introduced the concept of prepared queries through the implementation of the "prepare()" and "execute()" methods of the PDO extension, right at the beginning of this series. As I said then, however, it was only an introduction to the topic.

Indeed, this subject is far much complex, and deserves a more detailed analysis. Therefore, in this last tutorial in the series I’m going to show you how to run prepared queries, but in this case we’re going to include named parameters into them. Additionally, I’m going to take a quick look at some additional methods bundled with this extension. These new methods can be really useful for working with transactions, in case you want to use this feature with the database system of your choice.

Hopefully, by the end of this article, you should have a more solid background in how to include these neat characteristics in your own PHP applications with the assistance of the PDO extension.

With the preliminaries out of our way, let’s explore the real power of using prepared queries and transactions with PDO objects. It’s going to be an instructive experience!

{mospagebreak title=Working with prepared queries}

Before I proceed to show you the practical examples concerning the implementation of a prepared query, I’d like to remind you quickly of its intrinsic definition. In layman’s terms, a prepared query can be thought of as a SQL statement that is 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 feature can be quite useful in those cases where complex statements must be executed against one or more databases, since the respective commands will be performed significantly faster.

All right, now that you’re familiar with the definition of a prepared query, let me show you an example in which this type of query is executed along with a couple of named parameters. The example in question is as follows:

// example using the ‘prepare()’ method with named parameters
try{
  
$dbh=new PDO
(‘mysql:host=localhost;dbname=alejandro’,'user’,'password’);
  
$dbh->prepare(‘SELECT * FROM users WHERE name=:name AND
email=:email’);
   $dbh->execute(array
(‘:name’=>’Alejandro’,':email’=>’alejandro@domain.com’));
  
$result=$dbh->fetchAll();
  
// displays data for ‘Alejandro’
  
print_r($result);
  
$dbh->execute(array
(‘:name’=>’John’,':email’=>’john@domain.com’));
  
// display data for ‘John’
  
print_r($result);
}
catch(PDOException $e) {
  
echo ‘Error : ‘.$e->getMessage();
  
exit();
}

As you can see, the previous example demonstrates in a friendly fashion how to run a prepared query with a pair of named parameters associated with it. More specifically speaking, in this case I used the already familiar "prepare()" and "execute()" PDO methods, which were covered in detail in the first article of the series, to run two different SQL statements, even though the query in question has been compiled only once.

Since the "name" and "email" parameters have been attached to the initial query, they’re used to fetch fictional data from a sample "USERS" database table. Quite easy to understand, right?

However, if the previous example illustrates the implementation of a prepared query with named parameters, now let me show you another one that shows how to apply the same concept, this time using question marks.

The pertinent code sample is as follows:

// example using the ‘prepare()’ method with (?) question mark
parameters
try{
  
$dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,'user’,'password’);
  
$dbh->prepare(‘SELECT * FROM users WHERE name=? AND email=?’);
  
$dbh->execute(array(‘Alejandro’,'alejandro@domain.com’));
  
$result=$dbh->fetchAll();
  
// displays data for ‘Alejandro’
  
print_r($result);
  
$dbh->execute(array(‘John’,'john@domain.com’));
  
// display data for ‘John’
  
print_r($result);
}
catch(PDOException $e) {
  
echo ‘Error : ‘.$e->getMessage();
  
exit();
}

As shown above, using question marks instead of named parameters to execute a prepared query is a no-brainer process that can be performed with minor hassles. In this case I also fetched the same database rows that you saw in the previous example, but obviously the question marks have been replaced with real data.

So far, so good. At this stage you hopefully grasped the logic that stands behind the concept of running prepared queries. However, the PDO extension comes equipped with another handy method that can be used for performing this specific type of query as well (and others, by the way). It’s called "bindParam()," and it deserves a closer look.

In the following section I’m going to show you how to use this brand new method, thus click on the link below and keep reading.

{mospagebreak title=Using an alternate approach with prepared queries}

As I expressed in the previous section, the PDO library has another helpful method, called "bindParam()," which can also be used to execute a prepared query. As its name suggests, the method attaches a specified argument to a given SQL statement, following a similar approach to the one shown with named parameters and question marks.

To show more clearly how the "bindParam()" method works, a basic implementation of it is listed below:

// example using ‘bindParam()’ method
try{
  
$dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,'user’,'password’);
  
$sql=$dbh->prepare("INSERT INTO USERS(name,email) VALUES
(:name,:email)");
  
$sql->bindParam(‘:name’,$name);
  
$sql->bindParam(‘:email’,$email);
  
// assign values for bound parameters and insert new row
  
$name=’John Doe’;
  
$email=’john@domain.com’;
  
$dbh->exec();
  
// assign different values for bound parameters and insert new
row
  
$name=’Mary Jackson’;
  
$email=’mary@domain.com’;
  
$dbh->exec();
}
catch(PDOException $e) {
   
$dbh->rollBack();
  
echo ‘Error : ‘.$e->getMessage();
} 

As you can see, in this case the previous "bindParam()" method is used to associate a couple of parameters with a prepared query, which is a process that should already be familiar to you. However, it should be noticed that in this case the "prepare()" method first returns an "$sql" query object, which is then used to invoke "bindParam()."

Taking into account that the above example uses the aforementioned "bindParam()" method with named parameters, below I coded a similar script, but in this occasion utilizing question marks.

Here is corresponding signature for the example in question:

// example using ‘bindParam()’ method with numeric values

try{
  
$dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,'user’,'password’);
  
$sql=$dbh->prepare("INSERT INTO USERS(name,email) VALUES
(?,?)");
  
$sql->bindParam(1,$name);
  
$sql->bindParam(2,$email);
  
// assign values to bound parameters and insert new row
  
$name=’John Doe’;
  
$email=’john@domain.com’;
  
$dbh->exec();
  
// assign different values to bound parameters and insert new
row
  
$name=’Mary Jackson’;
  
$email=’mary@domain.com’;
   $dbh->exec();
}
catch(PDOException $e) {
  
$dbh->rollBack();
  
echo ‘Error : ‘.$e->getMessage();
}

After studying the previous example, certainly you’ll have to agree with me that running prepared queries with the methods bundled with the PDO extension is an effortless process which can be performed by a few lines of code.

Nonetheless, it’s fair to say that all the code samples listed above also handle the eventual PDO exceptions via a brand new method called "rollBack()." In simple terms, this implies that, if an error occurs when connecting to the specified database system, or when running a query, etc., the system will go back to its initial state.

As you may have guessed, "rolling back" a certain SQL operation can be achieved by using transactions, a feature that is also supported by the PDO extension. In the following section (the last of this series, by the way), I’m going to show you a couple of examples of how to use the transactional model with PDO objects.

To learn more on this useful topic, go ahead and read the next few lines. I’ll be there, waiting for you.

{mospagebreak title=Working with transactions}

Any database abstraction layer that fits the requirements of modern web applications must support transactions. The PDO extension is no exception. It comes packaged with a neat set of methods aimed specifically at dealing with this important feature.

Basically, the library includes three simple methods for working with transactions, called "beginTransaction()," "commit()" and "rollBack()" respectively. All of them are very easy to follow. Below I coded two hands-on examples that demonstrate their functionality, at least primitively.

Here are the corresponding code samples:

// example using ‘beginTransaction()’, ‘rollback()’ and ‘commit
()’ methods
try{
  
$dbh=new PDO(‘mysql:host=localhost;dbname=alejandro’,'user’,'password’);
  
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
$dbh->beginTransaction();
  
$dbh->exec("INSERT INTO users (id,name,address,email) VALUES
(NULL,’Lindsay Wagner’,’1234 Binary
Avenue’,'lindsay@domain.com’)");
  
$dbh->exec("INSERT INTO users (id,name,address,email) VALUES
(NULL,’Jeff Wilson’,’666 Parsed Boulevard’,'jeff@domain.com’)");
  
$dbh->commit();
}
catch(PDOException $e) {
  
$dbh->rollBack();
  
echo ‘Error : ‘.$e->getMessage();
}

// additional example using ‘beginTransaction()’, ‘rollback()’
and ‘commit()’ methods
try{
  
$dbh=new PDO
(‘mysql:host=localhost;dbname=alejandro’,'user’,'password’);
  
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
$dbh->beginTransaction();
  
$dbh->exec("INSERT INTO users (id,name,address,email) VALUES
(NULL,’Susan Norton’,’1234 Jointner
Avenue’,'lindsay@domain.com’)");
  
$dbh->exec("UPDATE users SET name=’Jim Smith’,address=’1010
Main Street’,email=’jim@domain.com’ WHERE id=5");
  
$dbh->commit();
}
catch(PDOException $e) {
  
$dbh->rollBack();
  
echo ‘Error : ‘.$e->getMessage();
}

As you can see, the above hands-on examples utilize the group of transaction-related methods that I introduced previously, to execute different queries. In the first case, two insertions are performed against the same "USERS" database table that you saw in earlier sections, while in the second case a new row is added to the table in question, and then an existing record is updated. Quite simple, isn’t it?

All right, at this moment you should have a better idea of how to use the PDO extension that comes bundled with PHP 5.1 and up. As you saw, it features plenty of handy methods that can be used with a decent variety of database systems.

Naturally, you have the liberty to experiment with all the code samples shown here, with the firm purpose of improving your background in this powerful PHP-based database abstraction layer.

Final thoughts

Sadly, we’ve come to the end of this series. As you hopefully learned, the PDO extension can be really helpful if you’re developing web applications that talk to different database systems.

Also, it’s fair to say that this series is intended to be a simple introduction to the library’s main features, and certainly doesn’t cover other additional characteristics. If you’re looking for thorough documentation on it, the PHP official site is the best place to go.

See you in the next PHP tutorial!

Google+ Comments

Google+ Comments