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()
orexecute()
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 transactioncommit()
- Commits the current transactionrollBack()
- 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();