Database Basics

Running Raw SQL Queries

To run any database query, use \Koldy\Db class and simply execute:

Db::query('SELECT * FROM users')->exec();

Note that the above statement will be executed on database, but exec() won't return anything. To get the results from SELECT statement, use:

$users = Db::query('SELECT * FROM users')->fetchAll();

Or:

$users = Db::query('SELECT * FROM users')->fetchAllObj();

In first example, $users will be array of arrays of all records from users table, while in second example, it'll be array of stdClass instances.

Koldy framework provides query builders for select, insert, update and delete statements so you should avoid writing raw queries, unless there's no other way.

Named Bindings

To add your own variable values in queries, you should pass them as second parameter using named bindings:

Db::query('SELECT * FROM users WHERE id = :id', ['id' => 5])->fetchAll();

To prevent SQL injection, you should never do it directly with string concatenation.

Using Multiple Database Connections

If you take a look on this configuration example and you want to execute query on admin database, you should do it like this:

Db::query('DROP TABLE users')->setAdapterConnection('admin')->exec();

Or shorter:

Db::getAdapter('admin')->query('DROP TABLE users')->exec();

Database Transactions

To do a database transaction, you should put it in try/catch block:

try {
    Db::beginTransaction();
    Db::query('DROP TABLE users')->exec();
    Db::commit();
} catch (\Koldy\Db\Exception $e) {
    Db::rollBack();
}

Or if you do it on other connection:

try {
    Db::getAdapter('admin')->beginTransaction();
    Db::getAdapter('admin')->query('DROP TABLE users')->exec();
    Db::getAdapter('admin')->commit();
} catch (\Koldy\Db\Exception $e) {
    Db::getAdapter('admin')->rollBack();
}