Prepare & Execute/ExecuteMultiple

Purpose

prepareQuery() and executeQuery*() 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 ...

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


<?php
$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 prepareQuery the statement and the second is to executeQuery it.

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

To create a generic statement 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 prepareQuery() .

prepareQuery() 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 statement (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, executeQuery() requires two arguments, the statement handle of prepareQuery() 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.

Inserting data into a datebase


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

Using executeMultiple() instead of executeQuery()


<?php
...
$alldata = array(  array(1, 'one', 'en'),
                   array(2, 'two', 'to'),
                   array(3, 'three', 'tre'),
                   array(4, 'four', 'fire'));
$sth = $dbh->prepareQuery("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 executeQuery*() fails a MDB_Error, else MDB_OK will returned.

Introduction - Execute

Description

Purpose

Prepare

Execute/ ExecuteMultiple