Database - Queries - Query Builder 2

echo Kohana::debug((string) $query);
// Should display:
// SELECT `username`, `password` FROM `users` WHERE `username` = 'john'

Notice how the column and table names are automatically escaped, as well as the values? This is one of the key benefits of using the query builder.

Select - AS (column aliases)

It is also possible to create AS aliases when selecting, by passing an array as each parameter to DB::select

:

?

$query = DB::select(array('username', 'u'), array('password', 'p'))->from('users');

This query would generate the following SQL:

?

SELECT `username` AS `u`, `password` AS `p` FROM `users`

Select - DISTINCT

Unique column values may be turned on or off (default) by passing TRUE or FALSE, respectively, to the distinct() method.

?

$query = DB::select('username')->distinct(TRUE)->from('posts');

This query would generate the following SQL:

?

SELECT DISTINCT `username` FROM `posts`

Select - LIMIT & OFFSET

When querying large sets of data, it is often better to limit the results and page through the data one chunk at a time. This is done using the limit() and offset() methods.

?

$query = DB::select()->from(`posts`)->limit(10)->offset(30);

This query would generate the following SQL:

?

SELECT * FROM `posts` LIMIT 10 OFFSET 30

Select - ORDER BY

Often you will want the results in a particular order and rather than sorting the results, it's better to have the results returned to you in the correct order. You can do this by using the order_by() method. It takes the column name and an optional direction string as the parameters. Multiple order_by() methods can be used to add additional sorting capability.

?

$query = DB::select()->from(`posts`)->order_by(`published`, `DESC`);

This query would generate the following SQL:

?

SELECT * FROM `posts` ORDER BY `published` DESC

For a complete list of methods available while building a select query see Database_Query_Builder_Select.

---