Drivers

Drivers must implement the mef\Db\Driver\DriverInterface interface with the following methods:

  • Queries
    • query(string $sql) : mef\Db\RecordSet\RecordSetInterface
    • execute(string $sql) : integer
    • prepare(string $sql, array $parameters = []) : mef\Db\Statement\StatementInterface
  • Transactions
    • startTransaction()
    • commit()
    • rollBack()
  • Miscellaneous
    • quoteValue(string $value) : string

The following drivers are available:

  • mef\Db\Driver\DataProviderDriver - for unit testing
  • mef\Db\Driver\MySqliDriver - wraps a mysqli connection
  • mef\Db\Driver\PdoDriver - wraps a PDO connection

Each of them extend from mef\Db\Driver\AbstractDriver.

Queries

If your query contains untrusted data from the user (e.g., from an web form) always use prepared statements via the prepare() method. While you can use quoteValue() to obtain a safe string for a single value, it is not the recommended way to build queries. It is much more error prone than prepared statements.

// NEVER do this
$driver->query("SELECT * FROM t1 WHERE v='" . $_POST['v'] . "'");

If your query does not contain any untrusted data, then you can safely use either query() or execute(). The former is used when you need to return data (e.g., SELECT). The latter is used for data modifying queries (e.g., INSERT, UPDATE, DELETE).

Any errors during queries will throw exceptions.

query()

Use query() to obtain data. It returns an object that implements mef\Db\RecordSet\RecordSetInterface. This object may be directly iterated over.

$rs = $driver->query('SELECT * FROM data');
foreach ($rs as $row)
{
        echo $row['column1'], PHP_EOL;
}

There are many other ways to retrieve its data. Refer to the RecordSets section for more information.

execute()

Use execute() to modify data. It returns the number of affected rows as an integer. If the driver cannot report this information, it will return 0. It is not intended to be used to indicate errors.

$affectedRows = $driver->execute('DELETE FROM data WHERE v=1');
echo 'Number of rows deleted: ', $affectedRows, PHP_EOL;

prepare()

Use prepare() to safely set up a query with untrusted data. The SQL string contains placeholders that are later filled in. These placeholders can take two forms:

  • indexed - each placeholder is represented by ?
  • named - each placeholder is represented by a name preceded by a colon, e.g. :name

A single SQL statement must be consistent: it can either contain indexed parameters or named parameters, but not both. Indexed parameters are referenced by a zero-based index from left to right; named parameters are referenced by their name, including the leading colon.

Here is an example of the same query built with indexed and named parameters:

$st1 = $driver->prepare('DELETE FROM t1 WHERE v=?');

$st2 = $driver->prepare('DELETE FROM t1 WHERE v=:v');

You can also specify parameters as a second argument to prepare():

$st1 = $driver->prepare('DELETE FROM t1 WHERE v=?', [42]);

$st2 = $driver->prepare('DELETE FROM t1 WHERE v=?', [':v' => 42]);

The return value of prepare() is an object implementing mef\Db\Statement\StatementInterface. There are three things that can be done with a prepared statement:

  1. Bind or set values for the parameters,
  2. Call query() to return a record set, or
  3. Call execute() to return the number of affected rows.
// execute a prepared statement
$st = $driver->prepare('DELETE FROM t1 WHERE v=?');
$st->setParameter(0, 42);
$affectedRows = $st->execute();

// query a prepared statement
$st = $driver->query('SELECT * FROM t1 WHERE v=?');
$st->setParameter(0, 42);
foreach ($st->query() as $t1)
{
        echo $t1['v'], PHP_EOL;
}

For more details, see Prepared Statements.

Transactions

Miscellaneous