WeberTrivia.com -> Pear Manual -> Introduction

Purpose

prepare() and execute*() give you more power and flexibilty for query execution. You can use them, if you have to do more than one equal query (i.e. adding a list of addresses to a database) or if you want to support different databases, which have different implementations of the SQL standard.

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.

Prepare() has to be called with the generic statment at least once. It returns a handle for the statment.

To create a generic statment is simple. Write the SQL query as usual, i.e.

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

Now check which parameters should be replaced while script runtime. Substitute this parameters with a placeholder.

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

So, thats all! Now you have a generic statement, required by prepare().

prepare() can handle different types of placeholders or wildcards.

? - (recommended) stands for a scalar value like strings or numbers, the value will be quoted depending of the database

! - stands for a scalar value and will inserted into the statement "as is".

& - requires an existing filename, the content of this file will be included into the statment (i.e. for saving binary data of a graphic file in a database)

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/ ExecuteMultiple

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 of prepare() and an array with the values to assign. The array has to be numerically ordered. The first entry of the array represents the first wildcard, the second the second wildcard etc. The order is independent from the used wildcard char.

Example 21-1. Inserting data into a database

// 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); }

In the example the query is done four times:

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')

executeMultiple() works in the same way, but requires a two dimensional array. So you can avoid the explicit foreach in the eample above.

Example 21-2. 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.

If execute*() fails a DB_Error, else DB_OK will returned.

Introduction - Prepare & Execute

Description

Purpose

Prepare

Execute/ ExecuteMultiple