Recipe 10.7. Repeating Queries Efficiently

10.7.1. Problem

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

10.7.2. 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( ), as shown in Example 10-17.

Running prepared statements

<?php // 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"; } ?>

10.7.3. 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 Example 10-17, 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 10-18 shows prepare( ) and execute( ) with two placeholders.

Multiple placeholders

<?php $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 10-19 shows named placeholders in action.

Using named placeholders

<?php $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. Example 10-20 shows how to use bindParam( ).

Using bindParam( )

<?php $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 Example 10-20, 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. Since 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 10-2.

Table 10-2. PDO::PARAM_* constants

Constant	Type
PDO::PARAM_NULL	NULL
PDO::PARAM_BOOL	Boolean
PDO::PARAM_INT	Integer
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 10-21 uses glob( ) to slurp the contents of all the files in a directory into a database table.

Putting file contents into a database with PDO::PARAM_LOB

<?php $st = $db->prepare('INSERT INTO files (path,contents) VALUES (:path,:contents)'); $st->bindParam(':path',$path); $st->bindParam(':contents',$fp,PDO::PARAM_LOB); foreach (glob('c:/documents/*.*') 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 at http://www.php.net/PDO shows the proper syntax to do this.

10.7.4. See Also

Documentation on PDO::prepare( ) at http://www.php.net/PDO::prepare, PDOStatement::execute( ) at http://www.php.net/PDOStatement::execute, on PDO::bindParam( ) at http://www.php.net/PDO::bindParam, and on PDO::PARAM_LOB in the "Large Objects" section of http://www.php.net/PDO.