mef\Db¶

Requirements¶

• PHP 5.6.0
• mysqli or PDO extension

Example¶

$driver = new mef\Db\Driver\PdoDriver(new PDO('sqlite::memory:'));
$driver->execute('CREATE TABLE test (
    "id" INTEGER PRIMARY KEY,
    "key" TEXT, "value" TEXT
)');

$driver->prepare(
    'INSERT INTO test VALUES (:id, :key)',
    [':id' => 1, ':key' => 'apple']
)->execute();

foreach ($driver->query('SELECT * FROM test') as $row)
{
    echo $row['id'], ' => ', $row['key'], PHP_EOL;
}

More complete examples are available in the examples directory.

Installation¶

The recommended way to install mef\Db is with Composer.

php composer.phar require mefworks/db:~1.0

Alternatively, you can download the source from BitBucket. It is compatible with PSR-4 class loaders.

git clone git@bitbucket.org:leverton/mefworks-db.git

License¶

Licensed using the MIT license.

Copyright (C) 2006-2014 Matthew Leverton

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

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:

1. query(string $sql) : mef\RecordSet\RecordSetInterface - Run a select query from a raw SQL string and return a record set.
2. 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.

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

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

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

$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:

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

To use prepared statements:

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

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;
}

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();

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

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.

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

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

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

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

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.

Setting up parameters¶

// indexed
$st = $driver->prepare('SELECT * FROM foo WHERE id=?', [42]);

// named
$st = $driver->prepare('SELECT * FROM foo WHERE id=:id', [':id' => 42]);

// indexed
$st->setParameter(0, 42, Statement::INTEGER);

// named
$st->setParameter(':id', 42, Statement::INTEGER);

// indexed
$st->bindParameter(0, $val, Statement::INTEGER);

// named
$st->bindParameter(':id', $val, Statement::INTEGER);

$st = $driver->prepare('UPDATE t1 SET value=? WHERE key=?');
$st->bindParameter(0, $value);
$st->bindParameter(1, $key);

foreach (getArray() as $key => $value)
{
    $st->execute();
}

$st->bindParameter(0, $a['value']);
$st->bindParameter(1, $a['key']);

$a = ['key' => 1, 'value' => 'foo'];
$st->execute(); // will not work

$a['key'] = 1;
$a['value'] = 'foo';

$st->execute(); // will work

// by value
$st->setParameters([$a, $b]);
$st->setParameters([$a, $anInteger], [1 => Statement::INTEGER]);

$st->setParameters([':a' => $a, ':b' => $b]);
$st->setParameters([':a' => $a, ':b' => $anInteger], [':b' => Statement::INTEGER]);

// by reference
$st->bindParameters([&$a, &$b]);
$st->bindParameters([&$a, &$anInteger], [1 => Statement::INTEGER]);

$st->bindParameters([':a' => &$a, ':b' => &$b]);
$st->bindParameters([':a' => &$a, ':b' => &$anInteger], [':b' => Statement::INTEGER]);

Running a prepared statement¶

After setting or binding all parameters, call execute() or query() as appropriate. execute() will return the number of affected rows, while query() will return a recordset.

The same prepared statement can be ran multiple times. Only the parameters that change need to be reset.

Naming conventions¶

There are many different methods associated with the recordset, depending on how you want the data returned (associative array, indexed array, etc) and if you want an array or an iterator.

The methods are named after the following conventions:

• fetch returns data. When combined with All, Column, or Keyed, it will return the remainder of the resultset as an array. Otherwise, it is only returning something from the next row.
• get*Iterator returns an iterator that returns one row at a time. This is the best way to get data if you simply need to process records.
• The “default” row type is an associative array.
• The methods with an explicit Array return an indexed array.
• The “keyed” methods return some sort of associative array where the key comes from data in the column.

All of the methods are listed below, followed by an overview of the main differences between them. Then the keyed methods and callbacks are covered in more detail.

Fetching data¶

The most direct way to fetch data is via the default iterator:

foreach ($driver->query($sql) as $row)
{
    echo $row['columnName'], PHP_EOL;
}

This is equivalent to:

foreach ($driver->query($sql)->getIterator() as $row) ...

$value = $driver->query('SELECT COUNT(*) FROM t1')->fetchValue();

$row = $driver->query($sql)->fetchRow();
echo $row['columnName'], PHP_EOL;

$row = $driver->query($sql)->fetchRowAsArray();
echo $row[0], PHP_EOL;

$rs = $driver->query($sql);

while ($rs->fetchInto($row))
{
    echo $row['columnName'], PHP_EOL;
}

$rs = $driver->query($sql);

$object = new stdClass;

while ($rs->fetchIntoObject($object))
{
    echo $object->columName, PHP_EOL;
}

$results = $driver->query($sql)->fetchAll();

$q = $driver->query($sql);

$firstRow = $q->fetchRow();
$remainingRows = $q->fetchAll();

foreach ($driver->query($sql)->getArrayIterator() as $row)
{
    echo $row[0], PHP_EOL;
}

foreach ($driver->query($sql)->getColumnIterator() as $value)
{
    echo $value, PHP_EOL;
}

Keyed arrays¶

All of the keyed methods allow you to specify a column to use as the key, as opposed to a zero-based array. Imagine a dataset that looks like:

With SELECT * and fetchAll, you would get an array just like that:

$driver->query('SELECT * FROM t1')->fetchAll()

[
  0 => ['n' => '1', 'en' => 'one', 'sp' => 'uno'],
  1 => ['n' => '2', 'en' => 'two', 'sp' => 'dos']
]

But say you want the n to be the key of the array. In that case, you need to use one of the keyed methods.

$driver->query('SELECT * FROM t1')->fetchKeyed()

[
  1 => ['en' => 'one', 'sp' => 'uno'],
  2 => ['en' => 'two', 'sp' => 'dos']
]

The keyed value is removed from the row. If there is only one value left, then a simple key => value array is returned.

$driver->query('SELECT n,en FROM t1')->fetchKeyed()

[
  1 => 'one',
  2 => 'two'
]

If there are no more columns left, then the true is used for the value.

$driver->query('SELECT en FROM t1')->fetchKeyed()

[
  'one' => true,
  'two' => true
]

The keyed iterator works the same way:

foreach ($driver->query('SELECT n,en')->getKeyedIterator() as $n => $en)
{
    echo $n, ': ', $en, PHP_EOL;
}

Callbacks¶

For more flexibility in how the records are returned, use a callback. Note that there is no way to directly return an object. This introduces complexities (constructor parameters, injecting dependencies, etc) that are better handled by a callback.

Callbacks always receive the entire row as an associative array. They are free to return anything.

The following illustrates using a callback to handle custom object creation:

$cb = function (array $row) {
    $myObject = new MyObject;
    $myObject->setFirstName($row['first_name']);
    $myObject->setLastName($row['last_name']);
    return $myObject;
};

foreach ($driver->query($sql)->getCallbackIterator($cb) as $myObject)
{
    echo $myObject->getFirstName(), PHP_EOL;
}

Or to retrieve them all in an array:

$myObjects = $driver->query($sql)->fetchAllWithCallback($cb);

$myObject = $cb($driver->query($sql)->fetchRow());