Using an alternate approach with prepared queries - PHP

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.