Working with prepared queries - PHP

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.