Home Documentation GitHub Support

Mastering Kraken

Kraken is Glowie's powerful query builder and database ORM toolkit. It's time to you to master this incredible component and start working with databases in a way you've never done before.

Connecting to a database

To start working with Kraken, you must create an instance of Glowie\Core\Database\Kraken class. Within Kraken constructor, you can optionally pass the table name you want to use as default for your queries (see also: Models).

Example

use Glowie\Core\Database\Kraken;

$db = new Kraken('glowie'); // Sets "glowie" table as default

The second parameter passed in this constructor is the database connection you want to use. If you leave this empty, Glowie will connect to your application globally defined database (the one you've set in app/config/Config.php). If you want to connect to a different database, pass an array with the connection settings as the second parameter. The array must follow the same structure as in Config.php database setting (see App configuration).

Example

use Glowie\Core\Database\Kraken;

$db = new Kraken('glowie', [
    'host' => 'localhost',
    'username' => 'root',
    'password' => '',
    'db' => 'glowie',
    'port' => 3306
]);

Changing the default table and database connection

If you want to change the default table or database connection without creating a new Kraken instance, use $db->table() and $db->database() functions. The parameters within this functions are exactly the same for the constructor.

Setting the database charset

By default, Kraken uses the utf8 character set to decode data from your database. If you want to use a different setting, use $db->charset() method, passing the charset name as the first parameter. It must be a supported character set from your MySQL server.

Example

$db->charset('latin1');

Escaping data

When working with any kind of user-input data, it's highly recommended to escape the data to avoid SQL injection attacks. Every Kraken default method will already escape the data for you, excepts for raw methods. In this case, you must use $db->escape() method, passing the desired data as the first parameter. This method will return the escaped data as a string.

Example

$db->escape($this->post->email); // returns the "email" input properly escaped

In order to ignore the default data escaping, use the static Kraken::raw() method.

Example

Kraken::raw($this->post->email); // Returns the "email" input unescaped

Selecting data

In order to prepare a SELECT query, use $db->select() function. In this function you can pass a single field name or an array of fields to use in your query. You can also use a raw SELECT statement.

Use $db->from() to set a table name if you are not using the default table.

Example

$db->select(); // Produces SELECT * FROM glowie
$db->select(['id', 'name'])->from('users'); // Produces SELECT id, name FROM users
$db->select('COUNT(ID) AS total'); // Produces SELECT COUNT(ID) AS total FROM glowie

If you want to append columns to an existing SELECT query, use $db->addSelect() method. Parameters are the same.

Example

$db->addSelect('status'); // Adds the column "status" to the SELECT statement

To perform a SELECT DISTINCT query, use $db->distinct().

Example

$db->select('id')->distinct(); // Produces SELECT DISTINCT id FROM glowie

Fetching SELECT data

In order to fetch data from a SELECT query, use $db->fetchAll(). This function returns an array with each row from the query result as an Element. You can also use $db->fetchRow() to get only the first resulting row from the query.

Example

$result = $db->select()->fetchAll();

If you pass a true option as the first parameter of these methods, the results will be returned as associative arrays instead of objects.

WHERE conditions

In order to add a WHERE condition to your query, you can use the $db->where() method and its variations.

Basic chaining
Passing the column name as the first parameter and the value as the second will produce a basic equal comparison. If you want to use another operator, pass it as the second parameter and the value as the third parameter.

Example

$db->where('id', 1); // Produces WHERE id = "1"
$db->where('status', '!=', 0); // Produces WHERE status != "0"
$db->where('name', 'LIKE', '%gabriel%'); // Produces WHERE name LIKE "%gabriel%"

Subsequent WHERE calls will chain the conditions with AND parameters.

Example

$db->where('id', 1)
    ->where('status', '!=', 0);
// Produces WHERE id = "1" AND status != "0"

You can also pass an array of multiple WHERE conditions as the first parameter only. Each condition must be another array with at least two parameters (the same used in $db->where() method).

Example

$db->where([
    ['id', 1],
    ['status', '!=', 0],
    ['name', 'LIKE', '%gabriel%']
]);
// Produces WHERE id = "1" AND status != "0" AND name LIKE "%gabriel%"

OR comparisons
In order to create an OR chaining to your WHERE conditions, use the $db->orWhere() method. Parameters are the same from $db->where().

Example

$db->where('id', 1)
   ->orWhere('id', 2);
// Produces WHERE id = "1" OR id = "2"

Documentation session under development... Contribute!