Database

Simplify Db is composed of three main components defined by these interfaces:

• \Simplify\Db\DatabaseInterface: represents the DBMS
• \Simplify\Db\QueryObjectInterface: defines a fluent interface for writing and executing queries
• \Simplify\Db\QueryResultInterface: defines an interface for retrieving the result of a query

Together, they provide a fluent interface that makes it easier to write database queries.

Simplify implements these interfaces using PDO.

Configuration

$config = \Simplify::config();

$config['database'] = array(
    'default' => array(
        // global
        '*' => array(
            'host' => 'localhost',
            'username' => 'simplify',
            'password' => 's1mpl1fy',
            'name' => 'simplify',
            'charset' => 'utf8'
        ),

        // production
        'production' => array(
            'name' => 'simplify_prod',
            'matchHost' => 'www.simplify.com'
        ),

        // dev
        'dev' => array(
            'name' => 'simplify_dev',
            'matchHost' => 'dev.simplify.com'
        ),
    )
);

Basic usage example

$params = array(
    'email' => \Simplify::request()->post('email'),
    'password' => \Simplify::request()->post('password'),
);

$user = \Simplify::db()
    ->query()
    ->from('users')
    ->where('user_email = :email')
    ->where('user_password = :password')
    ->execute($params)
    ->fetchRow();

The query object

Most methods in QueryObjectInterface can be called in multiple ways:

// add 'field' to the query
$query->select('field');

// add 'field_one' and 'field_two' to the query
$query->select(array('field_one', 'field_two'));

// remove 'field' to query
$query->select('field', false);

// remove 'field_one' and 'field_two' from the query
$query->select(array('field_one', 'field_two'), false);

// get all fields from the query
$query->select();

// clear all fields from the query
$query->select(false);

The same notation works with most methods, such as QueryObjectInterface::data(), QueryObjectInterface::from(), QueryObjectInterface::where() and QueryObjectInterface::groupBy(). To make sure check the source code.

Select query

$user = \Simplify::db()->query()->from('users')->where('user_email = :email')->execute($params)->fetchRow();

Insert query

$inserted = \Simplify::db()->insert('users', $data)->execute($data)->numRows();

Update query

$updated = \Simplify::db()->update('users', $data, 'user_email = :user_email')->execute($data)->numRows();

Delete query

$deleted = \Simplify::db()->delete('users', 'user_email = :user_email')->execute($data)->numRows();

Raw query

$user = \Simplify::db()->query('SELECT user_email FROM users LIMIT 1')->executeRaw()->fetchOne();

Where expressions

The QueryObjectInterface::where() method accepts both a string or an array as parameter. These parameters can be nest to create logical expressions. The first level is an AND, second level is an OR, and so forth.

Examples:

$query->where('a = 1')->where('b = 1');
// or
$query->where(array('a = 1', 'b = 1'));
// both result in 'a = 1 AND b = 1'

$query->where(array('a = 1', array('b = 1', 'c = 1')));
// results in 'a = 1 AND (b = 1 OR c = 1)'

Prepared statements

Queries are executed using prepared statements.

Bound parameters can be passed to the QueryObjectInterface::execute() method either by using the parameter names or by using a wildcard (this works when there is only one parameter).

Example using parameter names:

$params = array(
    'email' => \Simplify::request()->post('email'),
    'password' => \Simplify::request()->post('password'),
);

$user = \Simplify::db()
    ->query()
    ->from('users')
    ->where('user_email = :email')
    ->where('user_password = :password')
    ->execute($params)
    ->fetchRow();

Example using wildcard:

$email = \Simplify::request()->post('email');

$user = \Simplify::db()
    ->query()
    ->from('users')
    ->where('user_email = ?')
    ->execute($email)
    ->fetchRow();

Fetching query results

$result = \Simplify::db()
    ->query()
    ->from('users')
    ->select('user_email')
    ->select('user_first_name')
    ->select('user_last_name')
    ->execute();

// fetch the first row in result set (associative array)
$user = $result->fetchRow();

// fetch all rows in result set (array of associative arrays)
$users = $result->fetchAll();

// fetch the first column in the result set (flat array)
$emails = $result->fetchCol();

// fetch the first column of the first row in the result set
$email = $result->fetchOne();