Database - Queries - Query Builder 3

Insert

To create records into the database, use DB::insert to create an INSERT query, using values() to pass in the data:

?

$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')

For a complete list of methods available while building an insert query see Database_Query_Builder_Insert.

Update

To modify an existing record, use DB::update to create an UPDATE query:

?

$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'

For a complete list of methods available while building an update query see Database_Query_Builder_Update.

Delete

To remove an existing record, use DB::delete to create a DELETE query:

?

$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')

For a complete list of methods available while building a delete query see Database_Query_Builder_Delete.

Advanced Queries

Joins

Multiple tables can be joined using the join() and on() methods. The join() method takes two parameters. The first is either a table name, an array containing the table and alias, or an object (subquery or expression). The second parameter is the join type: LEFT, RIGHT, INNER, etc.

The on() method sets the conditions for the previous join() method and is very similar to the where() method in that it takes three parameters; left column (name or object), an operator, and the right column (name or object). Multiple on() methods may be used to supply multiple conditions and they will be appended with an 'AND' operator.

?

// 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'

When joining multiple tables with similar column names, it's best to prefix the columns with the table name or table alias to avoid errors. Ambiguous column names should also be aliased so that they can be referenced easier.

---