Quickstart¶

This page provides a brief introduction to mef\Db. It only covers a few basic use cases. For a more comprehensive look into the various components, read the individual sections. Refer to the source code for documentation on the API itself.

Connect to database¶

To connect to a database, you must first instantiate a driver. mef\Db comes with two drivers: PDO and mysqli. Because the driver just acts as a decorator, you must first create the underlying connection.

PDO

$pdo = new PDO('sqlite::memory:');
$driver = new mef\Db\Driver\PdoDriver($pdo);
echo $driver->query('SELECT "Hello, World!"')->fetchValue(), PHP_EOL;

MySqli

$mysqli = new mysqli($host, $user, $password);
$driver = new mef\Db\Driver\MySqliDriver($mysqli);
echo $driver->query('SELECT "Hello, World!"')->fetchValue(), PHP_EOL;

The above examples will both output the same thing:

Hello, World!

Run queries¶

There are two ways to execute a query:

query(string $sql) : mef\RecordSet\RecordSetInterface - Run a select query from a raw SQL string and return a record set.
execute(string $sql) : integer - Run a data-modifying query from a raw SQL string and return the number of affected results.

If a query fails, some exception descending from mef\Db\Exception will be thrown.

Note

The record set that is returned by query() conforms to the mef\Db\RecordSet\RecordSetInterface interface and contains many methods that return data in various forms. This guide will only cover a few of them.

query()¶

When selecting data from the database, you must use the query() method. It will return a record set object. To access the data row by row, just iterate over it:

foreach ($driver->query('SELECT * FROM city') as $city)
{
    echo $city['name'], ' has a population of ', $city['population'], PHP_EOL;
}

The foreach value ($city) is a single row represented by an associative array with the name of the column being the key. The foreach key (not used in this example) would be the row number, beginning with 0.

Warning

If multiple columns have the same name, then only one of them will be accessible. The exact behavior is driver-dependent, so you should avoid this situation by using SQL aliases for the columns you want to access. You may inadvertently encounter this if you are joining multiple tables with a SELECT * query.

To collect all of the results into an array, use the RecordSetInterface::fetchAll() method.

$results = $driver->query('SELECT * FROM city')->fetchAll();

if (count($results) > 0)
{
    echo $results[0]['name'], ' has a population of ', $results[0]['population'], PHP_EOL;
}

Note

Do not use fetchAll() if you only need to iterate over the results one time. This will unnecessarily buffer the entire results into a PHP array.

execute()¶

When modifying data (e.g., UPDATE, INSERT, and DELETE), you must use the execute() method. It will execute the query and return the number of affected rows.

$affectedRows = $driver->execute('DELETE city WHERE population=666');

echo 'Number of devlish cities encountered: ', $affectedRows, PHP_EOL;

Prepare statements¶

Any query that uses data from untrusted sources (e.g., data entered on a web form) must use prepared statements to avoid SQL injection attacks.

Prepared statements use placeholders for places where user data will later be filled in. There are two ways to do this:

Indexed parameters. Use a ? symbol to denote a placeholder. Reference them by their 0-based index.
Named parameters. Use any alphanumeric name preceeded by a colon (e.g., :name) to denote a placeholder.

To use prepared statements:

Call prepare($sql) to create a statement.
Set or bind all of the parameters for the statement.
Call query() or execute() on the statement.

Note

prepare() returns a new object that conforms to the mef\Db\Statement\StatementInterface interface. You must use this object (not the driver) when setting the parameters and finally running the query.

Indexed Parameters

$st = $driver->prepare('SELECT * FROM city WHERE population > ?');
$st->setParameter(0, 1000000);

echo 'Here are some cities with one million people: ', PHP_EOL;
foreach ($st->query() as $city)
{
    echo $city, PHP_EOL;
}

Named Parameters

$st = $driver->prepare('SELECT * FROM city WHERE population > :population');
$st->setParameter(':population', 1000000);

echo 'Here are some cities with one million people: ', PHP_EOL;
foreach ($st->query() as $city)
{
    echo $city, PHP_EOL;
}

Note

It is not valid to mix indexed and named parameters within the same query.

Use transactions¶

Transactions are supported by objects implementing mef\Db\Transaction\TransactionDriver. The most feature complete driver is the mef\Db\TransactionDriver\NestedTransactionDriver; in order to use it, the underlying database engine must support save points and transactions.

Both the PDO and mysqli driver require that you inject the transaction driver into it.

$transactionDriver = new mef\Db\TransactionDriver\NestedTransactionDriver($driver);
$driver->setTransactionDriver($transactionDriver);

There are three methods on the driver object that power transactions:

startTransaction() - Starts a transaction
commit() - Commits the current transaction
rollBack() - Rolls back the current transaction

With the NestedTransactionDriver, inner (nested) transactions are fully supported.

$driver->startTransaction();
$driver->execute('DELETE FROM t1');
$driver->startTransaction();
$driver->execute('INSERT INTO t1 VALUES (1)'); // will not be committed
$driver->rollBack();
$driver->commit();