Database query builder for UPDATE statements. See Query Builder for usage and examples.
Class declared in MODPATH/database/classes/Database/Query/Builder/Update.php on line 3.
bool FALSE
bool FALSE
NULL
NULL
array(0) array(0) array(0) array(0) NULL
NULL
NULL
array(0) Set the table for a update.
mixed
$table
= NULL - Table name or [$table, $alias] or object voidpublic function __construct($table = null)
{
if ($table) {
// Set the inital table name
$this->_table = $table;
}
// Start the query with no SQL
return parent::__construct(Database::UPDATE, '');
}Compile the SQL query and return it.
mixed
$db
= NULL - Database instance or name of instance stringpublic function compile($db = null)
{
if (!is_object($db)) {
// Get the database instance
$db = Database::instance($db);
}
// Start an update query
$query = 'UPDATE ' . $db->quote_table($this->_table);
// Add the columns to update
$query .= ' SET ' . $this->_compile_set($db, $this->_set);
if (!empty($this->_where)) {
// Add selection conditions
$query .= ' WHERE ' . $this->_compile_conditions($db, $this->_where);
}
if (!empty($this->_order_by)) {
// Add sorting
$query .= ' ' . $this->_compile_order_by($db, $this->_order_by);
}
if ($this->_limit !== null) {
// Add limiting
$query .= ' LIMIT ' . $this->_limit;
}
$this->_sql = $query;
return parent::compile($db);
}Reset the current builder status.
$thispublic function reset()
{
$this->_table = null;
$this->_set = $this->_where = [];
$this->_limit = null;
$this->_parameters = [];
$this->_sql = null;
return $this;
}Set the values to update with an associative array.
array
$pairs
required - Associative (column => value) list $thispublic function set(array $pairs)
{
foreach ($pairs as $column => $value) {
$this->_set[] = [$column, $value];
}
return $this;
}Sets the table to update.
mixed
$table
required - Table name or [$table, $alias] or object $thispublic function table($table)
{
$this->_table = $table;
return $this;
}Set the value of a single column.
mixed
$column
required - Table name or [$table, $alias] or object mixed
$value
required - Column value $thispublic function value($column, $value)
{
$this->_set[] = [$column, $value];
return $this;
}Creates a new "AND WHERE" condition for the query.
mixed
$column
required - Column name or [$column, $alias] or object string
$op
required - Logic operator mixed
$value
required - Column value $thispublic function and_where($column, $op, $value)
{
$this->_where[] = ['AND' => [$column, $op, $value]];
return $this;
}Closes an open "WHERE (...)" grouping.
$thispublic function and_where_close()
{
$this->_where[] = ['AND' => ')'];
return $this;
}Opens a new "AND WHERE (...)" grouping.
$thispublic function and_where_open()
{
$this->_where[] = ['AND' => '('];
return $this;
}Return up to "LIMIT ..." results
integer
$number
required - Maximum results to return or null to reset $thispublic function limit($number)
{
$this->_limit = ($number === null) ? null : (int) $number;
return $this;
}Creates a new "OR WHERE" condition for the query.
mixed
$column
required - Column name or [$column, $alias] or object string
$op
required - Logic operator mixed
$value
required - Column value $thispublic function or_where($column, $op, $value)
{
$this->_where[] = ['OR' => [$column, $op, $value]];
return $this;
}Closes an open "WHERE (...)" grouping.
$thispublic function or_where_close()
{
$this->_where[] = ['OR' => ')'];
return $this;
}Opens a new "OR WHERE (...)" grouping.
$thispublic function or_where_open()
{
$this->_where[] = ['OR' => '('];
return $this;
}Applies sorting with "ORDER BY ..."
mixed
$column
required - Column name or [$column, $alias] or object string
$direction
= NULL - Direction of sorting $thispublic function order_by($column, $direction = null)
{
$this->_order_by[] = [$column, $direction];
return $this;
}Alias of and_where()
mixed
$column
required - Column name or [$column, $alias] or object string
$op
required - Logic operator mixed
$value
required - Column value $thispublic function where($column, $op, $value)
{
return $this->and_where($column, $op, $value);
}Closes an open "WHERE (...)" grouping.
$thispublic function where_close()
{
return $this->and_where_close();
}Closes an open "WHERE (...)" grouping or removes the grouping when it is empty.
$thispublic function where_close_empty()
{
$group = end($this->_where);
if ($group AND reset($group) === '(') {
array_pop($this->_where);
return $this;
}
return $this->where_close();
}Alias of and_where_open()
$thispublic function where_open()
{
return $this->and_where_open();
}Return the SQL query string.
stringpublic function __toString()
{
try {
// Return the SQL string
return $this->compile(Database::instance());
} catch (Exception $e) {
return Kohana_Exception::text($e);
}
}Returns results as associative arrays
$thispublic function as_assoc()
{
$this->_as_object = false;
$this->_object_params = [];
return $this;
}Returns results as objects
string
$class
= bool TRUE - Classname or true for stdClass array
$params
= NULL - $params $thispublic function as_object($class = true, array $params = null)
{
$this->_as_object = $class;
if ($params) {
// Add object parameters
$this->_object_params = $params;
}
return $this;
}Bind a variable to a parameter in the query.
string
$param
required - Parameter key to replace byref mixed
$var
required - Variable to use $thispublic function bind($param, & $var)
{
// Bind a value to a variable
$this->_parameters[$param] = & $var;
return $this;
}Enables the query to be cached for a specified amount of time.
integer
$lifetime
= NULL - Number of seconds to cache, 0 deletes it from the cache boolean
$force
= bool FALSE - Whether or not to execute the query during a cache hit $thispublic function cached($lifetime = null, $force = false)
{
if ($lifetime === null) {
// Use the global setting
$lifetime = Kohana::$cache_life;
}
$this->_force_execute = $force;
$this->_lifetime = $lifetime;
return $this;
}Execute the current query on the given database.
mixed
$db
= NULL - Database instance or name of instance string
$as_object
= NULL - Result object classname, true for stdClass or false for array array
$object_params
= NULL - Result object constructor arguments object - Database_Result for SELECT queriesmixed - The insert id for INSERT queriesinteger - Number of affected rows for all other queriespublic function execute($db = null, $as_object = null, $object_params = null)
{
if (!is_object($db)) {
// Get the database instance
$db = Database::instance($db);
}
if ($as_object === null) {
$as_object = $this->_as_object;
}
if ($object_params === null) {
$object_params = $this->_object_params;
}
// Compile the SQL query
$sql = $this->compile($db);
if ($this->_lifetime !== null AND $this->_type === Database::SELECT) {
// Set the cache key based on the database instance name and SQL
$cache_key = 'Database::query("' . $db . '", "' . $sql . '")';
// Read the cache first to delete a possible hit with lifetime <= 0
if (($result = Kohana::cache($cache_key, null, $this->_lifetime)) !== null
AND ! $this->_force_execute) {
// Return a cached result
return new Database_Result_Cached($result, $sql, $as_object, $object_params);
}
}
// Execute the query
$result = $db->query($this->_type, $sql, $as_object, $object_params);
if (isset($cache_key) AND $this->_lifetime > 0) {
// Cache the result array
Kohana::cache($cache_key, $result->as_array(), $this->_lifetime);
}
return $result;
}Set the value of a parameter in the query.
string
$param
required - Parameter key to replace mixed
$value
required - Value to use $thispublic function param($param, $value)
{
// Add or overload a new parameter
$this->_parameters[$param] = $value;
return $this;
}Add multiple parameters to the query.
array
$params
required - List of parameters $thispublic function parameters(array $params)
{
// Merge the new parameters in
$this->_parameters = $params + $this->_parameters;
return $this;
}Get the type of the query.
integerpublic function type()
{
return $this->_type;
}Compiles an array of conditions into an SQL partial. Used for WHERE and HAVING.
object
$db
required - Database instance array
$conditions
required - Condition statements stringprotected function _compile_conditions(Database $db, array $conditions)
{
$last_condition = null;
$sql = '';
foreach ($conditions as $group) {
// Process groups of conditions
foreach ($group as $logic => $condition) {
if ($condition === '(') {
if (!empty($sql) AND $last_condition !== '(') {
// Include logic operator
$sql .= ' ' . $logic . ' ';
}
$sql .= '(';
} elseif ($condition === ')') {
$sql .= ')';
} else {
if (!empty($sql) AND $last_condition !== '(') {
// Add the logic operator
$sql .= ' ' . $logic . ' ';
}
// Split the condition
list($column, $op, $value) = $condition;
if ($value === null) {
if ($op === '=') {
// Convert "val = NULL" to "val IS NULL"
$op = 'IS';
} elseif ($op === '!=' OR $op === '<>') {
// Convert "val != NULL" to "valu IS NOT NULL"
$op = 'IS NOT';
}
}
// Database operators are always uppercase
$op = strtoupper($op);
if ($op === 'BETWEEN' AND is_array($value)) {
// BETWEEN always has exactly two arguments
list($min, $max) = $value;
if ((is_string($min) AND array_key_exists($min, $this->_parameters)) === false) {
// Quote the value, it is not a parameter
$min = $db->quote($min);
}
if ((is_string($max) AND array_key_exists($max, $this->_parameters)) === false) {
// Quote the value, it is not a parameter
$max = $db->quote($max);
}
// Quote the min and max value
$value = $min . ' AND ' . $max;
} elseif ((is_string($value) AND array_key_exists($value, $this->_parameters)) === false) {
// Quote the value, it is not a parameter
$value = $db->quote($value);
}
if ($column) {
if (is_array($column)) {
// Use the column name
$column = $db->quote_identifier(reset($column));
} else {
// Apply proper quoting to the column
$column = $db->quote_column($column);
}
}
// Append the statement to the query
$sql .= trim($column . ' ' . $op . ' ' . $value);
}
$last_condition = $condition;
}
}
return $sql;
}Compiles an array of GROUP BY columns into an SQL partial.
object
$db
required - Database instance array
$columns
required - $columns stringprotected function _compile_group_by(Database $db, array $columns)
{
$group = [];
foreach ($columns as $column) {
if (is_array($column)) {
// Use the column alias
$column = $db->quote_identifier(end($column));
} else {
// Apply proper quoting to the column
$column = $db->quote_column($column);
}
$group[] = $column;
}
return 'GROUP BY ' . implode(', ', $group);
}Compiles an array of JOIN statements into an SQL partial.
object
$db
required - Database instance array
$joins
required - Join statements stringprotected function _compile_join(Database $db, array $joins)
{
$statements = [];
foreach ($joins as $join) {
// Compile each of the join statements
$statements[] = $join->compile($db);
}
return implode(' ', $statements);
}Compiles an array of ORDER BY statements into an SQL partial.
object
$db
required - Database instance array
$columns
required - Sorting columns stringprotected function _compile_order_by(Database $db, array $columns)
{
$sort = [];
foreach ($columns as $group) {
list ($column, $direction) = $group;
if (is_array($column)) {
// Use the column alias
$column = $db->quote_identifier(end($column));
} else {
// Apply proper quoting to the column
$column = $db->quote_column($column);
}
if ($direction) {
// Make the direction uppercase
$direction = strtoupper($direction);
if (!in_array($direction, ['ASC', 'DESC'])) {
throw new Database_Exception('Order direction must be "ASC" or "DESC".');
}
$direction = ' ' . $direction;
}
$sort[] = $column . $direction;
}
return 'ORDER BY ' . implode(', ', $sort);
}Compiles an array of set values into an SQL partial. Used for UPDATE.
object
$db
required - Database instance array
$values
required - Updated values stringprotected function _compile_set(Database $db, array $values)
{
$set = [];
foreach ($values as $group) {
// Split the set
list ($column, $value) = $group;
// Quote the column name
$column = $db->quote_column($column);
if ((is_string($value) AND array_key_exists($value, $this->_parameters)) === false) {
// Quote the value, it is not a parameter
$value = $db->quote($value);
}
$set[$column] = $column . ' = ' . $value;
}
return implode(', ', $set);
}