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

Corresponding 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=?';

Furthermore it is also possible to use named placeholders as inspired by Oracle. Using named placeholders also allows using the same placeholder name multiple times inside a single statement:

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

Prepare

To use the features above, you have to do two steps. Step one is to prepare the statement which returns an instance of the MDB2_Statement_Common class. The second step 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 class instance to be used when calling execute().

prepare() can handle different types of placeholders (a.k.a. wildcards). By default all placeholders are handled as strings. However passing an array of data types as the second parameter makes it possible to set a specific type for each placeholder.

Since DML (data manipulation language - INSERT, UPDATE, DELETE) statements have different return values than data fetches the prepare() accepts a third parameter. This parameter should be set to MDB2_PREPARE_MANIP for DML statements. For data reads it should either be set to MDB2_PREPARE_RESULT, an array of data types for each of the columns in the result set or TRUE in order to automatically detect the data types in the result set.

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 one argument a scalar or array with the values to assign.

Example 33-1. Passing scalars to execute() <?php // Once you have a valid MDB2 object named $mdb2... $sth = $mdb2->prepare('INSERT INTO numbers (number) VALUES (?)', array('integer'), MDB2_PREPARE_MANIP); $sth->execute(1); $sth->execute(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 MDB2 object named $mdb2... $types = array('integer', 'text', 'text'); $sth = $mdb2->prepare('INSERT INTO numbers VALUES (?, ?, ?)', $types, MDB2_PREPARE_MANIP); $data = array(1, 'one', 'en'); $affectedRows = $sth->execute($data); ?>

When using named placeholders the data array needs to be an associative array with the keys matching the placeholder names.

Example 33-3. Passing an array to execute() <?php // Once you have a valid MDB2 object named $mdb2... $types = array('integer', 'text', 'text'); $sth = $mdb2->prepare('INSERT INTO numbers VALUES (:id, :text, :lang)'); $data = array('id' => 1, 'name' => 'one', 'lang' => 'en'); $affectedRows = $sth->execute($data); ?>

When using named placeholders the data array needs to be an associative array with the keys matching the placeholder names.

Example 33-4. Passing an array to execute() <?php // Once you have a valid MDB2 object named $mdb2... $sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE id = ?', array('integer'), array('text', 'text')); $result = $sth->execute(1); ?>

ExecuteMultiple

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

Example 33-5. Passing arrays to execute() // Once you have a valid MDB2 object named $mdb2... $alldata = array(array(1, 'one', 'en'), array(2, 'two', 'to'), array(3, 'three', 'tre'), array(4, 'four', 'fire')); $sth = $mdb2->prepare('INSERT INTO numbers VALUES (?, ?, ?)'); foreach ($alldata as $row) { $mdb2->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 example above:

Example 33-6. Using executeMultiple() from the Extended Module instead of execute() // Once you have a valid MDB2 object named $mdb2... $mdb->loadModule('Exended', null, false); $alldata = array(array(1, 'one', 'en'), array(2, 'two', 'to'), array(3, 'three', 'tre'), array(4, 'four', 'fire')); $sth = $mdb2->prepare('INSERT INTO numbers VALUES (?, ?, ?)'); $mdb2->extended->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 MDB2_Result_Common object for queries that return results (such as SELECT queries), integer for queries that manipulate data (such as INSERT queries) or a MDB2_Error object on failure

Data types

MDB2 supports a number of data types across all drivers. These can be set for prepared placeholders as well as result sets in all relevant methods. You can find an overview of the supported data types and their format here.

Freeing Prepared Statements

Once you finish using prepared statements, if your script continues for a while, it's a good idea to save memory by freeing the prepared statement set via Use free().

Example 33-7. Freeing <?php // Once you have a valid MDB2 object named $mdb2... $sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE id = ?', array('integer'), array('text', 'text')); $result = $sth->execute(1); $sth->free(); ?>

Limiting rows and reading from an offset

In order to read/write to only a limited number of rows from a result set and/or to start reading from a specific offset the setLimit() can be called prior to calling prepare(). The limit and offset will only affected the next method call that will issue a query or prepare a statement and will automatically be reset after issueing the query. This also applies to any internal queries issues inside MDB2. Note that limit may not work with DML statements on RDBMS that emulate limit support and no error will be raised.

<?php
// Once you have a valid MDB2 object named $mdb2...
// read 20 rows with an offset of 10
$mdb2->setLimit(20, 10);
$sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE group_id = ?', array('integer'), array('text', 'text'));

?>