Query_Builder class

The Query_Builder class is the parent class for all Query_Builder classes. It contains the main code for compiling and executing a query. It also handles queries that are handcrafted, and constructed using DB::query.

type()

The type method returns the type of query defined by the instance, as defined by the DB class constants SELECT, INSERT, UPDATE or DELETE.

Static No
Parameters None.
Returns Integer. The type of query this object defines.
Example 
// check if this is a DELETE
$query = \DB::query('DELETE * FROM table', \DB::DELETE);
if ($query->type() == \DB::DELETE)
{
    // do something
}

If no type is given when constructing a query, or if the query is generated, this method will return null. In that case, the type will be determined at execution time.

cached($lifetime = null, $cache_key = null, $cache_all = true)

The cached method enables the result of the query to be cached for a specified amount of time. If the exact same query is executed again within the lifetime defined, the cached result is returned instead.

Static No
Parameters
Param Type Default Description
$lifetime integer null number of seconds to cache or null for the default cache timeout.
$cache_key string null name of the cache key to be used or null for an automatically generated cache key.
$cache_all boolean true if true, all results will be cached, if false, only non-empty results will be cached.
Returns Database_Query
Example 
// Cache the result from this query for 60 seconds
$table = 'table';
$query = DB::query('SELECT * FROM :tablename', DB::SELECT);
$result = $query->as_object()->cached(60, 'AllFromTable', false)->execute();

as_assoc()

When called, the result is return as an associative array, instead of as objects. This is the default.

Static No
Parameters None
Returns Indexed array of assoc arrays
Example 
// return StdClass objects
$table = 'table';
$query = DB::query('SELECT * FROM :tablename', DB::SELECT);
$result = $query->as_assoc()->execute();
foreach ($result as $row)
{
    echo $row['id'];
}

as_object($class = true)

When called, the result is return as an array of objects. If no argument is passed, of if the default value true is passed, the rows in the result will be returned as instances of StdClass. If you pass a class name, instances of that class will be returned instead.

This is particularly useful if you want a custom query to return instances of Model_Crud or an Orm Model. Note that is these cases, the result MUST contain the complete primary key!

Static No
Parameters
Param Type Default Description
$class string true The name of the class to be used for result objects.
Returns Indexed array of objects
Example 
// return StdClass objects
$table = 'table';
$query = DB::query('SELECT * FROM :tablename', DB::SELECT);
$result = $query->as_object()->execute();
foreach ($result as $row)
{
    echo $row->id;
}

// have ORM model objects return instead
$result = $query->as_object('Model_Tablename')->execute();

set_connection($db)

The set_connection method allows you to execute the query against a specific database connection. If not specified, the query runs against the connection defined as "default" in your database configuration. It is mainly used to be able to dynamically swap the database used to run the query, independent of the code that actually executes the query.

Static No
Parameters
Param Type Default Description
$db string null The database connection name.
Returns Database_Query
Example 
// assign a value to a query parameter
$table = 'table';
$query = DB::query('DELETE * FROM :tablename', DB::DELETE);
$query->param('tablename', $table);

// set the query to run on an alternate connection
$query->set_connection('2nd-db');
$result = $query->execute();

// equivalent to
$result = $query->execute('2nd-db');

compile($db = null)

The compile method will compile the SQL defined within the Query object, and return it. It will return the SQL in the dialect defined by the database connection passed, the database connection set, or the default connection. The SQL dialect used will depend of the driver the connection defines.

Static No
Parameters
Param Type Default Description
$db string null The database connection name.
Returns string, the compiled SQL statement
Example 
// assign a value to a query parameter
$table = 'table';
$query = DB::query('DELETE * FROM :tablename', DB::DELETE);
$query->param('tablename', $table);

// compile the query, returns: DELETE * FROM table
$sql = $query->compile();

execute($db = null)

The execute method will executed the compiled SQL as defined within the Query object, and return its results. It will use the connection passed, the connection set, or the default connection to find the driver to execute it, in this order.

Static No
Parameters
Param Type Default Description
$db string null The database connection name.
Returns Database_Query_Result for SELECT type queries
Database_Query_Cached for SELECT type query results that are returned from cache
The insert id for INSERT type queries (if an auto_increment key is used)
The number of affected rows for UPDATE type queries
Example 
// assign a value to a query parameter
$table = 'table';
$query = DB::query('DELETE * FROM :tablename', DB::DELETE);
$query->param('tablename', $table);

// execute the query
$query->execute();

Query parameters and parameter binding

When you want to code SQL queries by hand, it is strongly advised not to work with string concatenations, but use parameters and parameter replacement instead. It will keep your code much cleaner and more secure, your queries easier to update, and the values in your query easier to change or update. For examples see the usage page. 

// set some variables
$table = 'table';
$id = 123;
$name = 'John';

// don't use
$query = DB::query('SELECT * FROM '.$table.'. WHERE id = '.$id.' AND name = "'.$name.'"');

// but use
$query = DB::query('SELECT * FROM :tablename WHERE id = :id AND name = :name');
$query->parameters(array(
    'tablename' => $table,
    'id' => $id,
    'name' => $name
});

Using parameter replacement makes sure your variables are properly quoted, preventing SQL injection.

param($param, $value)

The param method allows you to set the value of a parameter in the query.

Static No
Parameters
Param Type Default Description
$param string required the parameter name
$value string required the value to assign to the parameter
Returns Database_Query
Example 
// assign a value to a query parameter
$table = 'table';
$query = DB::query('DELETE * FROM :tablename', DB::DELETE);
$query->param('tablename', $table);

// update the variable
$table = 'newtable';

// DELETE * FROM `table`;
$sql = $query->compile();

parameters(array $params)

The parameters method allows you to set multiple parameter values in the query.

Static No
Parameters
Param Type Default Description
$params array required assoc array of parameter/value combinations
Returns Database_Query
Example 
// assign values to query parameters
$table = 'table';
$query = DB::query('DELETE * FROM :tablename WHERE `id` = :id', DB::DELETE);
$query->parameters(array('tablename' => $table, 'id' => 1));

// DELETE * FROM `table` WHERE `id` = 1;
$sql = $query->compile();

bind($param, & $var)

The bind method allows you to bind a parameter to a variable in your code.

Static No
Parameters
Param Type Default Description
$param string required the parameter name
$var string required the variable to bind the parameter to
Returns Database_Query
Example 
// bind a query parameter
$table = 'tablename';
$query = DB::query('DELETE * FROM :tablename', DB::DELETE);
$query->bind('tablename', $table);

// update the variable
$table = 'newtable';

// DELETE * FROM `newtable`;
$sql = $query->compile();