Database Class Methods

delete() method

Purpose:
The delete() method tells the database class that you want to delete some records from a database table/collection.

Format:

delete()

Examples:

// Delete all the records from a users table where their type is 'test'
$db ->delete()
    ->from('users')
    ->where('type', 'test')
    ->exec();

from() method

Alias:
This method is an alias of table().

Purpose:
The from() method is for selecting the table(s) you want to return or delete data from.

Format:

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

Examples:

// Grab a single table
$db->from('users')

// OR join multiple tables
$db->from('users')
   ->from('user_profiles')

// OR grab multiple tables by passing in an array
$db->from(['users', 'user_profiles'])

where() method

Purpose:
The where() method is for limiting the records that get returned to only those that pass certain conditions.

Format:

where(column_name, value [, operator] | array(array(column_name, operator, value [, conjunction])))

Examples:

// Only return results where name = 'bob'
$db->where('name', 'bob')

// Only return results where the date is greater than or equal to january first 2014
$db->where('date', '2014-01-01', '>=')

// Only return results where the date is greater than or equal to january first 2014
// and less than january 2016
$db ->where('date', '2014-01-01', '>=')
    ->where('date', '2016-01-01', '<')

// Only return results where name = 'bob' OR name = 'jeff'
$db ->where('name', 'bob')
    ->orWhere('name', 'jeff')

// Pass in an array...
// Only return results where a user's name starts with 's' or was created after Jan 2016,
// and the user is of type 'admin' or 'moderator'
$db ->where([
        ['created_time', '>=', '2016-01-01', 'OR'],
        ['name', 'LIKE', 's%']
    ])
    ->where([
        ['type', '=', 'admin', 'OR'],
        ['type', '=', 'moderator']
    ])  
// Using the MySQL adaptor, the above produces:
// WHERE (created_time >= '2016-01-01' OR name LIKE 's%') AND (type = 'admin' OR type = 'moderator')

orWhere() method

Purpose:
The orWhere() method is for limiting the records that get returned to only those that pass certain conditions.

Similar To:
It works identically to the where() method except any conditions passed to it will be conjoined with 'OR' to any other Where declarations. See the where() section for more examples.

Examples:

// Only return users who are named bob and also admins, or users named jeff
$db ->where([
        ['name', 'bob'],
        ['type', 'admin']
    ])
    ->orWhere('name', 'jeff')
// Using the MySQL adaptor, the above produces:
// WHERE (name = 'bob' AND type = 'admin') OR (name = 'jeff')

in() method

Purpose:
The in() method is for checking if a column's value exists in another set of data.

Format:

in(column, value | string(value1, value2, value3, ...) | array(value1, value2, value3, ...))

Examples:

// Check if column value matches a single value (same as using where statement)
$db ->in('name', 'bob')

// Check if column value is present in a comma separated list
// Note: The list gets translated into an array by the Database class.
$db ->in('name', 'bob, jeff, susan')

// Check if column value is present in an array of values
$db ->in('name', ['bob', 'jeff', 'susan'])

// NOT RECOMMENDED as it breaks cross-database functionality, but possible
$db ->in('name', 'SELECT name FROM users WHERE name LIKE s%')
// The better way of doing the above is to do a query to grab all the names that start with 's',
// then pass the result set as an array to the in() method. This would preserve cross-database
// functionality and not lock you into databases that support your SQL statement.

orderBy() method

Purpose:
The orderBy() method is for ordering the returned results from your query.

Format:

// sort_direction = DESC (descending) | ASC (ascending)
orderBy(column_name, sort_direction)

Examples:

// Specifying a single column to order by...
$db ->orderBy('name', 'ASC')

// Specifying multiple columns to order by...
$db ->orderBy('users.name', 'DESC')
    ->orderBy('locations.name', 'DESC')

limit() method

Purpose:
The limit() method is for limiting the number of returned results from a query.

Format:

limit(number)

Examples:

// Limit the number of result rows to 10
$db ->limit(10)