/**
* Build the SQL WHERE clause from the various condition arrays
*
* @param array $cond The condition array, for example
*
* array(
* 'fieldName1' => $value1,
* 'fieldName2 >=' => $value2,
* 'fieldName3 => NULL
* )
*
* @param string $type The condition type "AND" or "OR"; Default is "AND"
*
* @return string The built condition WHERE clause
*/
public static function buildCondition($cond = array(), $type = 'AND')
{
if (!is_array($cond)) {
return $cond;
}
if (empty($cond)) {
return '';
}
$type = strtoupper($type);
$condition = array();
foreach ($cond as $field => $value) {
$field = trim($field);
$fieldOpr = explode(' ', $field);
$field = trim($fieldOpr[0]);
if (strpos($field, '__QueryBuilder::condition__') !== false) {
$field = substr($field, 0, strpos($field, '__QueryBuilder::condition__'));
}
$opr = count($fieldOpr) === 2 ? trim($fieldOpr[1]) : '=';
# check if any operator is given in the field
if (!in_array($opr, self::$operators)) {
$opr = '=';
}
if (is_numeric($field)) {
# if the field is array index,
# assuming that is a condition built by db_or() or db_and();
$condition[] = '( ' . $value . ' )';
} else {
# if the operator is "between", the value must be array
# otherwise force to "="
if (in_array($opr, array('between', 'nbetween')) && !is_array($value)) {
$opr = '=';
}
$opr = strtolower($opr);
$field = self::quote($field);
if (array_key_exists($opr, self::$likes)) {
$value = str_replace(':likeValue', db_escapeString($value), self::$likes[$opr]);
$condition[] = $field . ' ' . $value;
} elseif (is_numeric($value)) {
$condition[] = $field . ' ' . $opr . ' ' . db_escapeString($value) . '';
} elseif (is_string($value)) {
$condition[] = $field . ' ' . $opr . ' "' . db_escapeString($value) . '"';
} elseif (is_null($value)) {
if (in_array($opr, array('!=', '<>'))) {
$condition[] = $field . ' IS NOT NULL';
} else {
$condition[] = $field . ' IS NULL';
}
} elseif (is_array($value) && count($value)) {
$list = array();
foreach ($value as $v) {
$list[] = is_numeric($v) ? db_escapeString($v) : '"' . db_escapeString($v) . '"';
}
if ($opr === 'between') {
$condition[] = '( ' . $field . ' BETWEEN ' . current($list) . ' AND ' . end($list) . ' )';
} elseif ($opr === 'nbetween') {
$condition[] = '( ' . $field . ' NOT BETWEEN ' . current($list) . ' AND ' . end($list) . ' )';
} elseif ($opr === '!=') {
$condition[] = $field . ' NOT IN (' . implode(', ', $list) . ')';
} else {
$condition[] = $field . ' IN (' . implode(', ', $list) . ')';
}
} else {
$condition[] = $field . ' ' . $opr . ' ' . db_escapeString($value);
}
}
}
if (count($condition)) {
$condition = implode(" {$type} ", $condition);
} else {
$condition = '';
}
return $condition;
}
/**
* Handy MYSQL update operation
*
* @param string $table The table name without prefix
* @param array $data The array of data field names and values
* The first field/value pair will be used as condition if you did not provide the fourth argument
*
* array(
* 'conditionField' => $conditionFieldValue, <===
* 'fieldNameToSlug' => $valueToSlug, <=== if $lc_useDBAutoFields is enabled
* 'fieldName1' => $value1,
* 'fieldName2' => $value2
* )
*
* @param boolean $useSlug TRUE to include the slug field or FALSE to not exclude it
* The fourth argument can be provided here if you want to omit this.
* @param array|string $condition The condition for the UPDATE query. If you provide this,
* the first field of `$data` will not be built for condition
*
* ### Example
*
* array(
* 'fieldName1' => $value1,
* 'fieldName2' => $value2
* )
*
* OR
*
* db_or(array(
* 'fieldName1' => $value1,
* 'fieldName2' => $value2
* ))
*
* @return boolean Returns TRUE on success or FALSE on failure
*/
function db_update($table, $data = array(), $useSlug = true, $condition = null)
{
if (count($data) == 0) {
return;
}
global $_DB;
global $_conn;
global $lc_useDBAutoFields;
if (func_num_args() === 3 && (gettype($useSlug) === 'string' || is_array($useSlug))) {
$condition = $useSlug;
$useSlug = true;
}
$table = ltrim($table, db_prefix());
$table = db_prefix() . $table;
# Invoke the hook db_update_[table_name] if any
$hook = 'db_update_' . strtolower($table);
if (function_exists($hook)) {
return call_user_func_array($hook, array($table, $data, $useSlug, $condition));
}
# if slug is already provided in the data array, use it
if (array_key_exists('slug', $data)) {
$slug = db_escapeString($data['slug']);
$slug = _slug($slug);
$data['slug'] = $slug;
session_set('lastInsertSlug', $slug);
$useSlug = false;
}
$fields = array();
$slug = '';
$cond = '';
$notCond = '';
$i = 0;
$slugIndex = 1;
if ($condition) {
$slugIndex = 0;
}
$dsm = $_DB->schemaManager;
foreach ($data as $field => $value) {
if ($i === 0 && !$condition) {
# $data[0] is for PK condition, but only if $condition is not provided
$cond = array($field => db_escapeString($value));
# for PK condition
$i++;
continue;
}
if (is_object($dsm) && $dsm->isLoaded()) {
$fieldType = $dsm->getFieldType($table, $field);
if (is_array($value) && $fieldType == 'array') {
$value = serialize($value);
} elseif (is_array($value) && $fieldType == 'json') {
$jsonValue = json_encode($value);
$value = $jsonValue ? $jsonValue : null;
} elseif ($fieldType == 'boolean') {
$value = $value ? 1 : 0;
}
}
if (is_null($value)) {
$fields[] = QueryBuilder::quote($field) . ' = NULL';
} else {
$fields[] = QueryBuilder::quote($field) . ' = "' . db_escapeString($value) . '"';
}
if ($i === $slugIndex && $useSlug === true) {
# $data[1] is slug
$slug = db_escapeString($value);
}
$i++;
}
# must have condition
# this prevents unexpected update happened to all records
if ($cond || $condition) {
if ($cond && is_array($cond)) {
$cond = db_condition($cond);
} elseif ($condition && is_array($condition)) {
$cond = db_condition($condition);
} elseif ($condition && is_string($condition)) {
$cond = $condition;
}
if (empty($cond)) {
return false;
}
$notCond = 'NOT ( ' . $cond . ' )';
if (db_tableHasSlug($table, $useSlug)) {
$slug = _slug($slug, $table, $notCond);
session_set('lastInsertSlug', $slug);
$fields[] = '`slug` = "' . $slug . '"';
}
if (db_tableHasTimestamps($table)) {
$fields[] = '`updated` = "' . date('Y-m-d H:i:s') . '"';
}
$fields = implode(', ', $fields);
$sql = 'UPDATE ' . QueryBuilder::quote($table) . '
SET ' . $fields . ' WHERE ' . $cond;
return db_query($sql);
} else {
return false;
}
}