private function Convert(SqlCommand $command)
{
for ($p = 0; $p < $command->Parameters->Count(); $p++) {
$curParam = $command->Parameters->Items($p);
$value = $curParam->Value;
if (is_array($value)) {
$value = implode("','", $value);
$value = "'{$value}'";
}
$this->_values[str_replace('@', '', $curParam->Name)] = $value;
}
$this->_query = str_replace('@', ':', $command->GetQuery());
}
private function Convert(SqlCommand &$command)
{
$query = $command->GetQuery();
for ($p = 0; $p < $command->Parameters->Count(); $p++) {
$curParam = $command->Parameters->Items($p);
if (is_null($curParam->Value)) {
$query = str_replace($curParam->Name, 'null', $query);
}
if (is_array($curParam->Value)) {
$escapedValues = array();
foreach ($curParam->Value as $value) {
$escapedValues[] = mysqli_real_escape_string($this->_db, $value);
}
$values = implode("','", $escapedValues);
$inClause = "'{$values}'";
$query = str_replace($curParam->Name, $inClause, $query);
} else {
$escapedValue = mysqli_real_escape_string($this->_db, $curParam->Value);
$query = str_replace($curParam->Name, "'{$escapedValue}'", $query);
}
}
$this->_query = $query . ';';
}
public function GetQuery()
{
$baseQuery = $this->baseCommand->GetQuery();
$baseQueryUpper = strtoupper($baseQuery);
$numberOfWheres = substr_count($baseQueryUpper, 'WHERE');
$numberOfSelects = substr_count($baseQueryUpper, 'SELECT');
$hasWhere = $numberOfWheres !== false && $numberOfWheres > 0 && $numberOfWheres == $numberOfSelects;
$hasOrderBy = stripos($baseQuery, 'ORDER BY') !== false;
$hasGroupBy = stripos($baseQuery, 'GROUP BY') !== false;
$newWhere = $this->filter->Where();
if ($hasWhere) {
// get between where and order by, replace with match plus new stuff
$pos = strripos($baseQuery, 'WHERE');
$baseQuery = substr_replace($baseQuery, 'WHERE (', $pos, strlen('WHERE'));
$groupBySplit = preg_split("/GROUP BY/ims", $baseQuery);
$orderBySplit = preg_split("/ORDER BY/ims", $baseQuery);
if (count($groupBySplit) > 1) {
$queryFragment = trim($groupBySplit[0]);
$groupBy = trim($groupBySplit[1]);
$query = "{$queryFragment} ) AND ({$newWhere}) GROUP BY {$groupBy}";
} elseif (count($orderBySplit) > 1) {
$queryFragment = trim($orderBySplit[0]);
$orderBy = trim($orderBySplit[1]);
$query = "{$queryFragment} ) AND ({$newWhere}) ORDER BY {$orderBy}";
} else {
$query = "{$baseQuery}) AND ({$newWhere})";
}
} else {
if ($hasGroupBy) {
$query = str_ireplace('group by', " WHERE {$newWhere} GROUP BY", $baseQuery);
} elseif ($hasOrderBy) {
$query = str_ireplace('order by', " WHERE {$newWhere} ORDER BY", $baseQuery);
} else {
// no where, no order by, just append new where clause
$query = "{$baseQuery} WHERE {$newWhere}";
}
}
return $query;
}
