Utility Database Class Methods

table() method

The method for selecting a table you want to perform an operation on (whether selecting, updating, inserting, or deleting) is to use table(). However, in order to let developers construct queries in the same format they are used to when writing raw SQL, there are several aliases to this method.

Purpose:
The table() method is for selecting the table(s) you want to perform an action on.

Format:

table(table_name | array(table_name1, table_name2, ...))

Usage and Aliases:
Anywhere you would use a from(), into(), or update() method you can use this method instead.

Examples:

// A SELECT query
$db ->table('users')
    ->select('*')
    ->join('members', [['users.user_id', '=', 'members.user_id']])
    ->orderBy('users.user_id', 'ASC')
    ->exec();

// An UPDATE query
$db ->table('users')
    ->set('name', 'John')
    ->where('name', 'Randy', '>')
    ->getQuery();        

// An INSERT query
$db ->table('users')
    ->insert('name, email, type')
    ->values([['bob', 'bob@gmail.com', 'admin'], ['john', 'john@gmail.com', 'admin']])
    ->values(['bob', 'bob@gmail.com', 'admin'])
    ->exec();

getQuery() method

Purpose:
The getQuery() method returns the query that will be executed on the database. This is useful when trying to troubleshoot if you want to see what Cora's Database class is actually executing. Format:

getQuery()

Examples:

// Constuct a CREATE table query, then echo it out (does not execute it)
echo $db->create('locations')
        ->field('id', 'int', 'NOT NULL AUTO_INCREMENT')
        ->field('name', 'varchar(255)')
        ->field('address', 'varchar(255)')
        ->field('user_id', 'int')
        ->primaryKey('id')
        ->foreignKey('user_id', 'users', 'id')
        ->getQuery();

exec() method

Purpose:
The exec() method executes a query, resets the database class by calling reset(), and returns any data if there's data to return.

Format:

exec()

Examples:

// Constuct a CREATE table query, then execute it.
$db ->create('locations')
    ->field('id', 'int', 'NOT NULL AUTO_INCREMENT')
    ->field('name', 'varchar(255)')
    ->field('address', 'varchar(255)')
    ->field('user_id', 'int')
    ->primaryKey('id')
    ->foreignKey('user_id', 'users', 'id')
    ->exec();

reset() method

Purpose:
The reset() method clears out the stored data in the database class so that a new query can then be constructed. Format:

reset()

Examples:

// Constuct a CREATE table query, then echo it out (does not execute it)
echo $db->create('locations')
        ->field('id', 'int', 'NOT NULL AUTO_INCREMENT')
        ->field('name', 'varchar(255)')
        ->field('address', 'varchar(255)')
        ->field('user_id', 'int')
        ->primaryKey('id')
        ->foreignKey('user_id', 'users', 'id')
        ->getQuery();

/* 
    Before we can start working on a 2nd query, we either need to execute the one we just constructed
    (which clears out the stored data), OR we need to manually clear out the data if we don't plan
    on executing the query.
*/
// Clear out the stored data from the first query we constructed.
$db->reset()

// Start constructing a 2nd query...
echo $db->select('*')
        ->from('users')
        ->join('members', [['users.user_id', '=', 'members.user_id']])
        ->orderBy('users.user_id', 'ASC')
        ->getQuery();