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 testingmef\Db\Driver\MySqliDriver
- wraps a mysqli connectionmef\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.