Home arrow PHP arrow Page 3 - Working with Prepared Queries with PDO Objects in PHP 5

Using an alternate approach with prepared queries - PHP

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).

TABLE OF CONTENTS:
  1. Working with Prepared Queries with PDO Objects in PHP 5
  2. Working with prepared queries
  3. Using an alternate approach with prepared queries
  4. Working with transactions
By: Alejandro Gervasio
Rating: starstarstarstarstar / 8
June 11, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



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