$query = DB::select();?$query = DB::select()->from('users');Limiting the results of queries is done using the where(), and_where() and or_where() methods. These methods take three parameters: a column, an operator, and a value.?$query = DB::select()->from('users')->where('username', '=', 'john');Multiple where() methods may be used to string together multiple clauses connected by the boolean operator in the method's prefix. The where() method is a wrapper that just calls and_where().?$query = DB::select()->from('users')->where('username', '=', 'john')->or_where('username', '=', 'jane');You can use any operator you want. Examples include IN, BETWEEN, >, =<, !=, etc. Use an array for operators that require more than one value.?$query = DB::select()->from('users')->where('logins', '<=', 1); $query = DB::select()->from('users')->where('logins', '>', 50); $query = DB::select()->from('users')->where('username', 'IN', array('john','mark','matt')); $query = DB::select()->from('users')->where('joindate', 'BETWEEN', array($then, $now));By default, DB::select will select all columns (SELECT * ...), but you can also specify which columns you want returned by passing parameters to DB::select:?$query = DB::select('username', 'password')->from('users')->where('username', '=', 'john');?echo Kohana::debug((string) $query);// Should display:// SELECT `username`, `password` FROM `users` WHERE `username` = 'john'?$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 - DISTINCTUnique column values may be turned on or off (default) by passing TRUE or FALSE, respectively, to thedistinct() method.?$query = DB::select('username')->distinct(TRUE)->from('posts');This query would generate the following SQL:?SELECT DISTINCT `username` FROM `posts`Select - LIMIT & OFFSETWhen 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?$query = DB::select()->from(`posts`)->order_by(`published`, `DESC`);This query would generate the following SQL:?SELECT * FROM `posts` ORDER BY `published` DESC?$query = DB::insert('users', array('username', 'password'))->values(array('fred', 'p@5sW0Rd'));This query would generate the following SQL:?INSERT INTO `users` (`username`, `password`) VALUES ('fred', 'p@5sW0Rd')?$query = DB::update('users')->set(array('username' => 'jane'))->where('username', '=', 'john');This query would generate the following SQL:?UPDATE `users` SET `username` = 'jane' WHERE `username` = 'john'?$query = DB::delete('users')->where('username', 'IN', array('john', 'jane'));This query would generate the following SQL:?DELETE FROM `users` WHERE `username` IN ('john', 'jane')?// This query will find all the posts related to "smith" with JOIN$query = DB::select('authors.name', 'posts.content')->from('authors')->join('posts')->on('authors.id', '=', 'posts.author_id')->where('authors.name', '=', 'smith');This query would generate the following SQL:?SELECT `authors`.`name`, `posts`.`content` FROM `authors` JOIN `posts` ON (`authors`.`id` = `posts`.`author_id`) WHERE `authors`.`name` = 'smith'If you want to do a LEFT, RIGHT or INNER JOIN you would do it like this join('colum_name', 'type_of_join'):?// This query will find all the posts related to "smith" with LEFT JOIN$query = DB::select()->from('authors')->join('posts', 'LEFT')->on('authors.id', '=', 'posts.author_id')->where('authors.name', '=', 'smith');This query would generate the following SQL:?SELECT `authors`.`name`, `posts`.`content` FROM `authors` LEFT JOIN `posts` ON (`authors`.`id` = `posts`.`author_id`) WHERE `authors`.`name` = 'smith'?$query = DB::select(array('COUNT("username")', 'total_users'))->from('users');?SELECT COUNT(`username`) AS `total_users` FROM `users`?$query = DB::select()->from('users') ->join('posts')->on('posts.username', '=', 'users.username') ->where('users.active', '=', TRUE) ->where('posts.created', '>=', $yesterday); $total = clone $query;$total->select(array('COUNT( DISTINCT "username")', 'unique_users'));$query->select('posts.username')->distinct();?$query = DB::select('username', array('COUNT("id")', 'total_posts') ->from('posts')->group_by('username')->having('total_posts', '>=', 10);This will generate the following query:?SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10?$sub = DB::select('username', array('COUNT("id")', 'total_posts') ->from('posts')->group_by('username')->having('total_posts', '>=', 10); $query = DB::select('profiles.*', 'posts.total_posts')->from('profiles') ->join(array($sub, 'posts'), 'INNER')->on('profiles.username', '=', 'posts.username');This will generate the following query:?SELECT `profiles`.*, `posts`.`total_posts` FROM `profiles` INNER JOIN( SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10 ) AS postsON `profiles`.`username` = `posts`.`username`Insert queries can also use a select query for the input values?$sub = DB::select('username', array('COUNT("id")', 'total_posts') ->from('posts')->group_by('username')->having('total_posts', '>=', 10); $query = DB::insert('post_totals', array('username', 'posts'))->select($sub);This will generate the following query:?INSERT INTO `post_totals` (`username`, `posts`) SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10 $query = DB::select()->from('users') ->where_open() ->or_where('id', 'IN', $expired) ->and_where_open() ->where('last_login', '<=', $last_month) ->or_where('last_login', 'IS', NULL) ->and_where_close() ->where_close() ->and_where('removed','IS', NULL);This will generate the following query:?SELECT * FROM `users` WHERE ( `id` IN (1, 2, 3, 5) OR ( `last_login` <= 1276020805 OR `last_login` IS NULL ) ) AND `removed` IS NULL ?$query = DB::update('users')->set(array('login_count' => DB::expr('login_count + 1')))->where('id', '=', $id);This will generate the following query, assuming $id = 45:?UPDATE `users` SET `login_count` = `login_count` + 1 WHERE `id` = 45
