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.