Prepared Statements¶

The database driver supports prepared statements via the prepare method. This will return a mef\Db\Statement\StatementInterface object. A query can bind values in one of two ways (but not both at the same time):

0-based indexed via ? placeholders.
named via :name placholders

$st = $driver->prepare('SELECT * FROM foo WHERE id=?');
$st = $driver->prepare('SELECT * FROM foo WHERE id=:id');

Setting up parameters¶

By value¶

Multiple parameters can be passed by value as the second argument to prepare():

// indexed
$st = $driver->prepare('SELECT * FROM foo WHERE id=?', [42]);

// named
$st = $driver->prepare('SELECT * FROM foo WHERE id=:id', [':id' => 42]);

Or they can be passed later via setParameter.

// indexed
$st->setParameter(0, 42, Statement::INTEGER);

// named
$st->setParameter(':id', 42, Statement::INTEGER);

The third parameter is optional. If present, it must be one of the following constants (AUTOMATIC is the default):

Statement::AUTOMATIC
Statement::NULL
Statement::BOOLEAN
Statement::INTEGER
Statement::STRING
Statement::BLOB

By reference¶

Parameters can also be bound by reference.

// indexed
$st->bindParameter(0, $val, Statement::INTEGER);

// named
$st->bindParameter(':id', $val, Statement::INTEGER);

This is useful when you need to execute the same statement many times with different data.

$st = $driver->prepare('UPDATE t1 SET value=? WHERE key=?');
$st->bindParameter(0, $value);
$st->bindParameter(1, $key);

foreach (getArray() as $key => $value)
{
    $st->execute();
}

Note

Binding array keys may not work as expected.

$st->bindParameter(0, $a['value']);
$st->bindParameter(1, $a['key']);

$a = ['key' => 1, 'value' => 'foo'];
$st->execute(); // will not work

This won’t work as $a has been replaced by a completely new array that is not being referenced by the bindParameter() call. But if individual elements of $a are updated, then it would work:

$a['key'] = 1;
$a['value'] = 'foo';

$st->execute(); // will work

Updating parameters in bulk¶

Parameters can also be sent in bulk via setParameters (by value) or bindParameters (by reference). The third parameter is an array of types; any omitted will be Statement::AUTOMATIC.

// by value
$st->setParameters([$a, $b]);
$st->setParameters([$a, $anInteger], [1 => Statement::INTEGER]);

$st->setParameters([':a' => $a, ':b' => $b]);
$st->setParameters([':a' => $a, ':b' => $anInteger], [':b' => Statement::INTEGER]);

// by reference
$st->bindParameters([&$a, &$b]);
$st->bindParameters([&$a, &$anInteger], [1 => Statement::INTEGER]);

$st->bindParameters([':a' => &$a, ':b' => &$b]);
$st->bindParameters([':a' => &$a, ':b' => &$anInteger], [':b' => Statement::INTEGER]);

Note

When using bindParameters(), you must add a reference & to each value, otherwise it will not work.

Running a prepared statement¶

After setting or binding all parameters, call execute() or query() as appropriate. execute() will return the number of affected rows, while query() will return a recordset.

The same prepared statement can be ran multiple times. Only the parameters that change need to be reset.