Query_Builder_Select class

(extends Query_Builder_Where)

The Query_Builder_Select class handles all the select operations for the query building process. It extends the Query_Builder_Where class, so all the methods are inherited.

distinct($value = true)

The distinct method sets whether to select distinct values.

Static No
Parameters
Param Type Default Description
$value bool 
true
 set to false if you don't want to select distinct values
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select('name')->from('users');

// Set it to distinct
$query->distinct();

// SELECT DISTINCT `name` FROM `users`

select($columns = null)

The select method appends columns to select.

Static No
Parameters
Param Type Default Description
$columns mixed 
true
 column name or array($column, $alias)
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select('name')->from('users');

// Append other columns to select
$query->select('surname', 'email');

// Supply an array to use an alias
$query->select(
	array('is_active', 'active'),
	'birthdate'
);

// SELECT `name`, `surname`, `email`, `is_active` AS `active`, `birthdate` FROM `users`

select_array(array $columns, $reset = false)

The select_array method appends columns to select.

Static No
Parameters
Param Type Default Description
$columns array required an array containing column names
$reset bool 
false
 If true, do not append, but overwrite
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select('name')->from('users');

// Append other columns to select
$query->select('surname', 'email');

// Supply an array to use an alias
$query->select_array(array(
	array('is_active', 'active'),
	'birthdate'
));

// produces SELECT `name`, `surname`, `email`, `is_active` AS `active`, `birthdate` FROM `users` ...

// Supply an array to use an alias and reset the previous select
$query->select_array(array(
	array('surname', 'name'),
	'birthdate'
), true);

// produces SELECT `surname` AS `name`, `birthdate` FROM `users` ...

from($tables)

The from method appends tables to select from.

Static No
Parameters
Param Type Default Description
$tables mixed required table names or array($table, $alias)
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select()->from('users');

// Append tables to select from
$query->from(
	'admins',
	array('comments', 'cmmnts')
);

// SELECT * FROM `users`, `admins`, `comments` cmmnts

join($table, $type = null)

The join method appends tables to join.

Static No
Parameters
Param Type Default Description
$table mixed required table name or array($table, $alias)
$type mixed 
true
 join type (LEFT, RIGHT, INNER, etc)
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select()->from('users');

// Join a table
$query->join('profiles');

// SELECT * FROM `users` JOIN `profiles`

// or...

$query = DB::select()->from('users');

// Join another table
$query->join('pictures', 'RIGHT OUTER');

// SELECT * FROM `users` RIGHT OUTER JOIN `pictures`

on($c1, $op, $c2)

The on method adds "ON ..." conditions for the last created JOIN statement.

Static No
Parameters
Param Type Default Description
$c1 mixed required table name or array($table, $alias)
$op string required logical operator
$c2 mixed required table name or array($table, $alias)
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select()->from('users');

// Join a table
$query->join('profiles');
$query->on('users.id', '=', 'profiles.user_id');

// SELECT * FROM `users` JOIN `profiles` ON `users`.`id` = `profiles`.`user_id`

and_on()

The and_on method is an alias for on.

or_ on($c1, $op, $c2)

The on method adds "OR ON ..." conditions for the last created JOIN statement.

Static No
Parameters
Param Type Default Description
$c1 mixed required table name or array($table, $alias)
$op string required logical operator
$c2 mixed required table name or array($table, $alias)
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select()->from('users');

// Join a table
$query->join('profiles');
$query->on('users.id', '=', 'profiles.user_id');

// Add an OR ON condition
$query->or_on('users.id', '=', 'profiles.other_id');

// SELECT * FROM `users` JOIN `profiles` ON `users`.`id` = `profiles`.`user_id` OR `users`.`id` = `profiles`.`other_id`

on_open()

The on_open method adds an open bracket for the last created JOIN statement.

Static No
Parameters None
Returns Returns the current instance.
Example See below

on_close()

The on_close method adds an closing bracket for the last created JOIN statement.

Static No
Parameters None
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select()->from('users');

// Join a table
$query->join('profiles');
$query->on('users.id', '=', 'profiles.user_id');

// Add some nested conditions
$query->on_open()
  ->on('profiles.status', '=', DB::expr('A')
  ->or_on('profiles.status', '=', DB::expr('B')
->on_close();

// SELECT * FROM `users` JOIN `profiles` ON `users`.`id` = `profiles`.`user_id`
//          AND (`profiles`.`status` = "A" OR  `profiles`.`status` = "B"))

and_on()

The and_on method is an alias for on.

group_by($columns)

The group_by method creates a "GROUP BY ..." filter.

Static No
Parameters
Param Type Default Description
$columns mixed required table name
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select()->from('articles');

// Group by 'genre'
$query->group_by('genre', 'category');

// SELECT * FROM `articles` GROUP BY `genre`, `category`

having($column, $op, $value = NULL)

The having method is an alias for and_having.

and_having($column, $op, $value = NULL)

The and_having method appends a "AND HAVING" statement.

Static No
Parameters
Param Type Default Description
$column string required Column name or array($column, $alias), object or callback
$op string 
null
 Logic operator: =, !=, IN, BETWEEN and LIKE.
$value mixed 
null
 Column value
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select('*')->from('users');

// Set the first HAVING statement
$query->having('name', '!=', 'John');

// and append a new condition
$query->and_having('surname', '=', 'Doe');

// create a group using a callback
$query->and_having(function($query){
	$query->having('email', 'info@example.com');
	$query->or_having('email', 'second@example.com');
});

// Resulting in:
// SELECT * FROM `users` HAVING `name` != "John" AND `surname` = "Doe"
// AND (`email` = "info@example.com" OR `email` = "second@example.com")

or_having($column, $op, $value = NULL)

The or_having method appends a "OR HAVING" statement.

Static No
Parameters
Param Type Default Description
$column string required Column name or array($column, $alias), object or callback
$op string 
null
 Logic operator: =, !=, IN, BETWEEN and LIKE.
$value mixed 
null
 Column value
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select('*')->from('users');

// Set the first HAVING statement
$query->having('name', '!=', 'John');

// and append a new condition
$query->or_having('surname', '=', 'Doe');

// create a group using a callback
$query->or_having(function($query){
	$query->having('email', 'info@example.com');
	$query->and_having('email', 'second@example.com');
});

// Resulting in:
// SELECT * FROM `users` HAVING `name` != "John" OR `surname` = "Doe"
// OR (`email` = "info@example.com" AND `email` = "second@example.com")

and_having_open()

The and_having_open method opens an SQL closure and appends it using AND.

Static No
Parameters None
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select('*')->from('users');

// Set a HAVING statement
$query->having('name', '=', 'john')

// Open a closure
$query->and_having_open();

// SELECT * FROM `users` HAVING `name` = "John" AND (

or_having_open()

The or_having_open method opens an SQL closure and appends it using OR.

Static No
Parameters None
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select('*')->from('users');

// Set a HAVING statement
$query->having('name', '=', 'john')

// Open a closure
$query->or_having_open();

// SELECT * FROM `users` HAVING `name` = "John" OR (

and_having_close()

The and_having_close method closes an SQL closure.

Static No
Parameters None
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select('*')->from('users');

// Open a closure
$query->and_having_open();

// Set a HAVING statement
$query->having('name', '=', 'john');

$query->and_having_close();
// SELECT * FROM `users` HAVING (`name` = "John")

or_having_close()

The or_having_close method closes an SQL closure.

Static No
Parameters None
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select('*')->from('users');

// Set a HAVING statement
$query->having('email', 'like', '%@example.com');

// Open a closure
$query->or_having_open();

// Set a HAVING statement
$query->having('name', '=' 'John');
$query->and_having('surname', '=', 'Doe');

$query->or_having_close();
// SELECT * FROM `users` HAVING `email` LIKE "%example.com" OR (`name` = "John" AND `surname` = "Doe")

offset($number)

The offset method sets row number to start from when selecting/updating/deleting.

Static No
Parameters
Param Type Default Description
$number int required The row number to start from
Returns Returns the current instance
Example 
// prepare a select statement
$query = DB::select('*')->from('users');

// Set the limit
$query->limit(10);

$query->offset(5);

// SELECT * FROM `users` LIMIT 10 OFFSET 5

compile(\Database_Connection$db)

The compile method returns the select SQL query as a string.

Static No
Parameters
Param Type Default Description
$db object required A database connection
Returns Returns the SQL query as a string.
Example 
// prepare a select statement
$query = DB::select('*')->from('users');

// Set the limit and offset
$query->limit(10);
$query->offset(5);

// Get the database connection
$connection = Database_Connection::instance();

// Get the sql query
$sql = $query->compile($connection);

reset()

The reset method resets all values of the current instance.

Static No
Parameters None
Returns Returns the current instance.
Example 
// prepare a select statement
$query = DB::select('*')->from('users');

// Set some conditions
$query->where('name', 'bob');
$query->where('surname', 'unknown');

// Reset it
$query->reset();

// Set the new values
$query->select('email')->from('admins')->where('role', 'superadmin');

// The SQL query will now be:
// SELECT `email` FROM `admins` WHERE `role` = "superadmins"