Database - Queries - Query Builder 1

Creating queries dynamically using objects and methods allows queries to be written very quickly in an agnostic way. Query building also adds identifier (table and column name) quoting, as well as value quoting.

At this time, it is not possible to combine query building with prepared statements.

Select

Each type of database query is represented by a different class, each with their own methods. For instance, to create a SELECT query, we use DB::select which is a shortcut to return a new Database_Query_Builder_Select object:

?

$query = DB::select();

Query Builder methods return a reference to itself so that method chaining may be used. Select queries ussually require a table and they are referenced using the from() method. The from() method takes one parameter which can be the table name (string), an array of two strings (table name and alias), or an object (See Subqueries in the Advanced Queries section below).

?

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

Now take a minute to look at what this method chain is doing. First, we create a new selection object using the DB::select method. Next, we set table(s) using the from() method. Last, we search for a specific records using the where() method. We can display the SQL that will be executed by casting the query to a string:

---