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();
|