Drivers

A driver is the main point of interaction with the database. All drivers 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.

In addition, the mef\Db\Driver\AbstractDecoratorDriver can be used to extend existing drivers with additional methods.

// PDO driver
$pdo = new PDO('mysql:host=localhost;dbname=mydb', $user, $password);
$driver = new mef\Db\Driver\PdoDriver($pdo);

// mysqli driver
$mysqli = new mysqli('localhost', $user, $password, 'mydb');
$driver = new mef\Db\Driver\MysqliDriver($mysqli);

Note

The drivers take no responsibility for configuration of connections. Things like the server time zone or the connection’s character set must be set via the underlying PDO / mysqli object. It’s assumed that once the driver is instantiated, the underlying object will no longer be used – but because the drivers do not alter configuration, it is generally safe to use them outside the context of the driver.

Queries

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).

Note

Any errors during queries will throw exceptions.

If your query contains untrusted data from the user (e.g., from a 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.

Warning

Always use prepared statements with placeholders for user data. Never inject user supplied values directly into queries like this:

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

The above code will subject you to SQL injection attacks.

query()

Use query() to obtain data. It returns an object that implements mef\Db\RecordSet\RecordSetInterface. This object may be directly iterated over; each record will be represented by an associative array.

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

There are many other ways to retrieve data from the recordset. 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.

For more details, see Prepared Statements.

Transactions

Transactions are used so that you can commit “all or nothing.” Generally they adhere to the following pattern:

$driver->startTransaction();

try
{
    $driver->execute($sql1);
    $driver->execute($sql2);
    $driver->commit();
}
catch (Exception $e)
{
    $driver->rollBack();
    throw $e;
}

The API here is stateful: when you commit or roll back, you are doing so to the currently open transaction. (It is an error to try to commit or roll back when there is no open transaction.)

Note

Before you can use transactions with the PDO and MySqli drivers, you must first set up a transaction driver.

For more details, see Transactions.

Miscellaneous

quoteValue() can be used to build a safe SQL string.

$sql = "SELECT * FROM t1 WHERE x='" . $driver->quoteValue($unsafeData) . "'";

It is not recommended to use this unless you really need to get a raw SQL string. Using prepared statements is a much better solution when you are only interested in executing some SQL with user data.

Warning

The results of this method are not guaranteed to be safe for all connections due to different character sets. It is important that you properly set your database’s character set before calling this method, and that you don’t use the SQL string in the future on a different character set. Refer to the PHP documentation for details on how to do this.