/**
* Quote a value so it can be savely used in a query.
*
* @param mixed $value
* @param string $type Force SQL type (not supported)
* @param string $empty Return $empty if $value is null
* @return string
*
* @todo Fix the use of $type for Q\DB_MySQL::quote()
*/
public function quote($value, $type = null, $empty = 'NULL')
{
return $this->sqlSplitter->quote($value, $empty);
}
/**
* Build a WHERE statement.
* If $value == null and $compare == '=', $compare becomes 'IS NULL'.
*
* NOTE: This function does not escape $column
* Returns array('where', 'having')
*
* @param mixed $column Column name or expression with placeholders, can also be an array of columns
* @param mixed $value Value or array of values ($column=$value[0] OR $column=$value[1])
* @param string $compare Comparision operator oa. =, !=, >, <, >=, <=, LIKE, LIKE%, %LIKE%, REVERSE LIKE (value LIKE column), IN and BETWEEN
* @return array
*/
public static function buildWhere($column, $value, $compare = "=")
{
// Prepare
$compare = empty($compare) ? '=' : trim(strtoupper($compare));
// Handle some simple and common cases, just to improve performance
if (is_string($column)) {
if (self::countPlaceholders($column) != 0) {
return array('where' => self::parse($column, $value));
} else {
if (isset($value) && !is_array($value) && ($compare === '=' || $compare === '!=' || $compare === '>' || $compare === '<' || $compare === '>=' || $compare === '<=')) {
return array('where' => "{$column} {$compare} " . self::quote($value));
} elseif ($compare === 'IS NULL' || $compare === 'IS NOT NULL') {
if (isset($value) && $value !== '' && (int) $value == 0) {
$compare = $compare === 'IS NULL' ? 'IS NOT NULL' : 'IS NULL';
}
return array('where' => "{$column} {$compare}");
}
}
}
// Prepare
$column = (array) $column;
$value = (array) $value;
if ($compare === 'ANY' || $compare === '=' && sizeof($value) > 1) {
$compare = 'IN';
}
// Only use the non-null values with between, autoconvert to >= or <=
if (($compare === "BETWEEN" || $compare === "NOT BETWEEN") && (isset($value[0]) xor isset($value[1]))) {
$compare = ($compare === "BETWEEN" xor isset($value[0])) ? '<=' : '>=';
$value = isset($value[0]) ? array($value[0]) : array($value[1]);
}
// Quote value. (For LIKE: Apply % for %LIKE% to value)
$matches = null;
if (preg_match('/^(\\%?)(?:REVERSE\\s+)?LIKE(\\%?)$/', $compare, $matches)) {
if (isset($value)) {
foreach ($value as &$val) {
$val = DB_MySQL_SQLSplitter::quote((isset($matches[1]) ? $matches[1] : "") . addcslashes($val, '%_') . (isset($matches[2]) ? $matches[2] : ""));
}
}
$compare = trim($compare, "%");
} elseif (isset($value)) {
foreach ($value as &$val) {
$val = DB_MySQL_SQLSplitter::quote($val);
}
}
unset($val);
// Apply reverse -> value LIKE column, instead of column LIKE value
if (substr($compare, 0, 8) === 'REVERSE ') {
$tmp = $column;
$column = $value;
$value = $tmp;
$compare = trim(substr($compare, 8));
}
// Compare as in any
if ($compare === "IN" || $compare === "NOT IN" || $compare === "ALL") {
$value = array_unique($value);
}
// Create where expression for each column (using if, instead of switch for performance)
$where = null;
$having = null;
if ($compare === "ALL") {
if (!isset($value)) {
throw new Exception("Unable to add '{$compare}' criteria: \$value is not set");
}
if (!empty($value)) {
foreach ($column as $col) {
$having[] = "COUNT(DISTINCT {$col}) = " . sizeof($value);
$where[] = "{$col} IN (" . join(", ", $value) . ")";
}
}
} elseif ($compare === "IN" || $compare === "NOT IN") {
if (!isset($value)) {
throw new Exception("Unable to add '{$compare}' criteria: \$value is not set");
}
if (!empty($value)) {
foreach ($column as $col) {
$where[] = "{$col} {$compare} (" . join(", ", $value) . ")";
}
}
} elseif ($compare === "BETWEEN" || $compare === "NOT BETWEEN") {
if (sizeof($value) != 2) {
throw new Exception("Unable to add '{$compare}' criteria: \$value should have exactly 2 items, but has " . sizeof($value) . " items");
}
foreach ($column as $col) {
$where[] = "{$col} {$compare} " . $value[0] . " AND " . $value[1];
}
} elseif ($compare === "IS NULL" || $compare === "IS NOT NULL") {
if (isset($value) && $value !== '' && (int) $value == 0) {
$compare = $compare === 'IS NULL' ? 'IS NOT NULL' : 'IS NULL';
}
if (!empty($value)) {
foreach ($column as $col) {
$where[] = "{$col} {$compare}";
}
}
} else {
if (!isset($value)) {
throw new Exception("Unable to add '{$compare}' criteria: \$value is not set");
}
if (!empty($value)) {
foreach ($column as $col) {
foreach ($value as $val) {
$where[] = "{$col} {$compare} {$val}";
}
}
}
}
// Return where expression
return array('where' => isset($where) ? join(" OR ", $where) : null, 'having' => isset($having) ? join(" OR ", $having) : null);
}