PHP Database Access Repeating Queries Efficiently

PHP Database Access

Repeating Queries Efficiently

Problem

You want to run the same query multiple times, substituting in different values each time.

Solution

Set up the query with PDO::prepare() and then run it by calling execute() on the prepared statement that prepare() returns. The placeholders in the query passed to prepare() are replaced with data by execute().

Example  Running prepared statements

       // Prepare
       $st = $db->prepare("SELECT sign FROM zodiac WHERE element LIKE ?");
       // Execute once
       $st->execute(array('fire'));
       while ($row = $st->fetch()) {
              print $row[0] . "<br/>\n";
       }
       // Execute again
       $st->execute(array('water'));
       while ($row = $st->fetch()) {
              print $row[0] . "<br/>\n";
       }

Discussion

The values passed to execute() are called bound parameters—each value is associated with (or “bound to”) a placeholder in the query. Two great things about bound parameters are security and speed. With bound parameters, you don’t have to worry about SQL injection attacks. PDO appropriately quotes and escapes each parameter so that special characters are neutralized. Also, upon prepare(), many database backends do some parsing and optimizing of the query, so each call to execute() is faster than calling exec() or query() with a fully formed query in a string you’ve built yourself.

In the first execute() runs the query SELECT sign FROM zodiac WHERE element LIKE 'fire'. The second execute() runs SELECT sign FROM zodiac WHERE element LIKE 'water'.

Each time, execute() substitutes the value in its second argument for the ? placeholder. If there is more than one placeholder, put the arguments in the array in the order they should appear in the query. 

Example  Multiple placeholders

      $st = $db->prepare(
             "SELECT sign FROM zodiac WHERE element LIKE ? OR planet LIKE ?");

      // SELECT sign FROM zodiac WHERE element LIKE 'earth' OR planet LIKE 'Mars'
      $st->execute(array('earth','Mars'));

In addition to the ? placeholder style, PDO also supports named placeholders. If you’ve got a lot of placeholders in a query, this can make them easier to read. Instead of ?, put a placeholder name (which has to begin with a colon) in the query, and then use those placeholder names (without the colons) as keys in the parameter array you pass to execute().

Example  Using named placeholders

      $st = $db->prepare(
             "SELECT sign FROM zodiac WHERE element LIKE :element OR planet LIKE :planet");
      // SELECT sign FROM zodiac WHERE element LIKE 'earth' OR planet LIKE 'Mars'
      $st->execute(array('planet' => 'Mars', 'element' => 'earth'));
      $row = $st->fetch();

With named placeholders, your queries are easier to read and you can provide the values to execute() in any order. Note, though, that each placeholder name can only appear in a query once. If you want to provide the same value more than once in a query, use two different placeholder names and include the value twice in the array passed to execute().

Aside from ? and named placeholders, prepare() offers a third way to stuff values into queries: bindParam(). This method automatically associates what’s in a variable with a particular placeholder. Shows how to use bindParam().

Example Using bindParam()

      $pairs = array('Mars' => 'water',
                                 'Moon' => 'water',
                                 'Sun' => 'fire');
      $st = $db->prepare(
             "SELECT sign FROM zodiac WHERE element LIKE :element AND planet LIKE
             :planet");
      $st->bindParam(':element', $element);
      $st->bindparam(':planet', $planet);
      foreach ($pairs as $planet => $element) {
             // No need to pass anything to execute() --
             // the values come from $element and $planet
             $st->execute();
             var_dump($st->fetch());
      }

In there’s no need to pass any values to execute(). The two calls to bindParam() tell PDO “whenever you execute $st, use whatever’s in the $element variable for the :element placeholder and whatever’s in the $planet variable for the :planet placeholder.” The values in those variables when you call bindParam() don’t matter—it’s the values in those variables when execute() is called that counts. Because the foreach statement puts array keys in $planet and array values in $element, the keys and values from $pairs are substituted into the query.

If you use ? placeholders with prepare(), provide a placeholder position as the first argument to bindParam() instead of a parameter name. Placeholder positions start at 1, not 0.

bindParam() takes its cue on how to deal with the provided value based on that value’s PHP type. Force bindParam() to treat the value as a particular type by passing a type constant as a third argument. The type constants that bindParam() understands are listed in Table.

Table  PDO::PARAM_* constants

Constant                      Type              

PDO::PARAM_NULL   NULL

PDO::PARAM_BOOL   boolean

PDO::PARAM_INT       integer

_________________________

Constant                     Type               

PDO::PARAM_STR     string
PDO::PARAM_LOB    “large object”
_________________________

The PDO::PARAM_LOB type is particularly handy because it treats the parameter as a stream. It makes for an efficient way to stuff the contents of files (or anything that can be represented by a stream, such as a remote URL) into a database table. 

Example  Putting file contents into a database with PDO::PARAM_LOB

     $st = $db->prepare('INSERT INTO files (path,contents) VALUES (:path,:contents)');
     $st->bindParam(':path',$path);
     $st->bindParam(':contents',$fp,PDO::PARAM_LOB);
     foreach (glob('/usr/local/*') as $path) {
            // Get a filehandle that PDO::PARAM_LOB can work with
            $fp = fopen($path,'r');
            $st->execute();
     }

Using PDO::PARAM_LOB effectively depends on your underlying database. For example, with Oracle your query must create an empty LOB handle and be inside a transaction. The “Inserting an image into a database: Oracle” example of the PDO manpage shows the proper syntax to do this.