PEAR Manual
PrevNext

Introduction - Execute

Introduction - Execute -- Prepare & Execute/ExecuteMultiple

Description

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 adresses 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 statment 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)

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 20-1. Inserting data into a datebase

...       
$alldata = array(  array(1, 'one', 'en'),
                   array(2, 'two', 'to'),
                   array(3, 'three', 'tre'),
                   array(4, 'four', 'fire'));
$sth = $dbh->prepare("INSERT INTO numbers VALUES(?,?,?)");
foreach ($alldata as $row) {
    $dbh->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 20-2. Using executeMultiple() instead of execute() 

...
$alldata = array(  array(1, 'one', 'en'),
                   array(2, 'two', 'to'),
                   array(3, 'three', 'tre'),
                   array(4, 'four', 'fire'));
$sth = $dbh->prepare("INSERT INTO numbers VALUES(?,?,?)");
$dbh->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.

PrevHomeNext
Introduction - SequencesUpIntroduction - autoPrepare and autoExecute