The DB service (or Database) provides access to a SQL database. It extends the PDO class and Provides CRUD (Create Read Update Delete) functionality.

You must configure your DB_DB, DB_USER, DB_PW, DB_HOST, DB_CHARSET, and DB_ENGINE in your app/config/config.php to use the DB service.

These defaults are set on the PDO class:

DB::setAttribute(\PDO::ATTR_PERSISTENT, true);
DB::setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC);
DB::setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

Querying & Variable Binding #

// A basic query
$users = DB::query('SELECT id,name FROM user');

// Iterating through results 
while($user = $users->fetch()){
    // Do something with the user
}


// Variable Binding using question marks
// Pass an array or single value as the second parameter to have it bound into the query
$result = DB::query('SELECT id,name FROM user WHERE age < ? AND hair_color = ?',[
    40,
    'brown'
]);

// Variable binding using named parameters
// Pass an associated array to the query as the second parameter
$result = DB::query('SELECT id FROM user WHERE age=:age AND hair_color=:color',[
    'age' => 40,
    'color' => 'brown'
])

// You can bind them before execution yourself with the set method
$query = DB::set('SELECT id FROM user WHERE age < ?',30);

// If you want to bind a raw SQL function use the raw keyword
$query = DB::set('SELECT id FROM user WHERE id=? AND late_after < ?', [
    15, 
    ['raw' => 'NOW()']
]);

// With named parameters and a raw
$query = DB::set('SELECT id FROM user WHERE id=:id AND late_after < :now', [
    'id' => 15, 
    'now' => ['raw' => 'NOW()']
]);


// Perform an insert, the users id is an auto increment field
DB::query('INSERT INTO user (id,name) VALUES (null,"Dave")');

// Get the generated auto increment id
$userId = DB::lastId();

// Or
$userId = DB::lastInsertId();

// Or use the insert method to get it returned
$userId = DB::insert('INSERT INTO user (id,name) VALUES (null,"Dave")');


//There are also some helper functions to make general querying a little less tedious

// Select
// First parameter is the name of the table
// Second parameter can be an array of fields to select, or a string of fields
// Third parameter is the where condition used to select the user
$user = DB::select('user', ['id','name','email'], ['id' => 33, 'email' => 'user@email.com']);


// Update
// First parameter is the name of the table
// Second parameter are the fields to update
// Third parameter is the where condition used to perform the update
DB::update('user', ['name' => 'Bob'], ['id' => 33]);


// Delete
// First parameter is the name of the table
// Second parameter is the where condition used to perform the delete
DB::delete('user', ['id' => 33]);


// Create (Insert)
// First parameter is the name of the table
// Second parameter are the values to insert into the table
DB::create('user', ['name' => 'Dave', 'age' => 43]);
$id = DB::lastId();

Transactions #

We extended the beginTransactions(), rollback(), and commit() methods so you can safely perform nested transactions with the DB service, you can also call the inTransaction() method to determine if your currently working in a transaction.

// Start a transaction
DB::beginTransaction();

try {
    DB::query('BAD QUERY',['gonna','fail']);
} catch(Disco\exceptions\DBQuery $e){
    // Roll it back
    DB::rollback();
    exit;
}

// Success, commit it
DB::commit();

Multiple Database Connections #

Extend the \Disco\classes\PDO class to create multiple database connections.

// Extend the PDO
// Passing in your secondary credentials manually to the parent constructor
class YourDB extends \Disco\classes\PDO {

    public function __construct(){
        parent::__construct('host','user','password','schema','engine','charset');
    }

}