Introduction - Prepare & Execute

Purpose

prepare() and execute*() give you more power and flexibilty for query execution. Prepare/execute mode is helpful when you have to run the same query several times but with different values in it, such as adding a list of addresses into a database.

Another place prepare/execute is useful is supporting databases which have different SQL syntaxes. Imagine you want to support two databases with different INSERT syntax:

db1: INSERT INTO tbl_name (col1, col2) VALUES (expr1, expr2) db2: INSERT INTO tbl_name SET col1=expr1, col2=expr2

Correspondending to create multi-lingual scripts you can create a array with queries like this:

$statement['db1']['INSERT_PERSON'] = 'INSERT INTO person (surname, name, age) VALUES (?, ?, ?)'; $statement['db2']['INSERT_PERSON'] = 'INSERT INTO person SET surname=?, name=?, age=?';

Prepare

To use the features above, you have to do two steps. Step one is to prepare the statement and the second is to execute it.

To start out, you need to prepare() a generic SQL statment. Create a generic statment by writing the SQL query as usual:

SELECT surname, name, age FROM person WHERE name = 'name_to_find' AND age < age_limit

Then substitute "placeholders" for the literal values which will be provided at run time:

SELECT surname, name, age FROM person WHERE name = ? AND age < ?

Then pass this SQL statement to prepare(), which returns a statement handle to be used when calling execute().

prepare() can handle different types of placeholders (a.k.a. wildcards).

Use backslashes to escape placeholder characters if you don't want them to be interpreted as placeholders:

UPDATE foo SET col=? WHERE col='over \& under'

Execute

After preparing the statement, you can execute the query. This means to assign the variables to the prepared statement. To do this, execute() requires two arguments: the statement handle returned by prepare() and a scalar or array with the values to assign.

Example 33-1. Passing scalars to execute() <?php // Once you have a valid DB object named $db... $sth = $db->prepare('INSERT INTO numbers (number) VALUES (?)'); $db->execute($sth, 1); $db->execute($sth, 8); ?>

When a prepared statement has multiple placeholders, you must use an array to pass the values to execute(). The first entry of the array represents the first placeholder, the second the second placeholder, etc. The order is independent of the type of placeholder used.

Example 33-2. Passing an array to execute() <?php // Once you have a valid DB object named $db... $sth = $db->prepare('INSERT INTO numbers VALUES (?, ?, ?)'); $data = array(1, 'one', 'en'); $db->execute($sth, $data); ?>

ExecuteMultiple

DB contains a process for executing several queries at once. So, rather than having to execute them manually, like this:

Example 33-3. Passing arrays to execute() // Once you have a valid DB object named $db... $alldata = array(array(1, 'one', 'en'), array(2, 'two', 'to'), array(3, 'three', 'tre'), array(4, 'four', 'fire')); $sth = $db->prepare('INSERT INTO numbers VALUES (?, ?, ?)'); foreach ($alldata as $row) { $db->execute($sth, $row); }

which would issue four queries:

INSERT INTO numbers VALUES ('1', 'one', 'en') INSERT INTO numbers VALUES ('2', 'two', 'to') INSERT INTO numbers VALUES ('3', 'three', 'tre') INSERT INTO numbers VALUES ('4', 'four', 'fire')

you can use executeMultiple() to avoid the explicit foreach in the eample above:

Example 33-4. Using executeMultiple() instead of execute() // Once you have a valid DB object named $db... $alldata = array(array(1, 'one', 'en'), array(2, 'two', 'to'), array(3, 'three', 'tre'), array(4, 'four', 'fire')); $sth = $db->prepare('INSERT INTO numbers VALUES (?, ?, ?)'); $db->executeMultiple($sth, $alldata);

The result is the same. If one of the records failed, the unfinished records will not be executed.

execute*() has three possible returns: a new DB_result object for queries that return results (such as SELECT queries), DB_OK for queries that manipulate data (such as INSERT queries) or a DB_Error object on failure