Ümit Tunç
625 lines
28 KiB
PHP
625 lines
28 KiB
PHP
<?php
|
|
namespace App\DevExtreme;
|
|
class DbSet {
|
|
private static $SELECT_OP = "SELECT";
|
|
private static $FROM_OP = "FROM";
|
|
private static $WHERE_OP = "WHERE";
|
|
private static $ORDER_OP = "ORDER BY";
|
|
private static $GROUP_OP = "GROUP BY";
|
|
private static $ALL_FIELDS = "*";
|
|
private static $LIMIT_OP = "LIMIT";
|
|
private static $INSERT_OP = "INSERT INTO";
|
|
private static $VALUES_OP = "VALUES";
|
|
private static $UPDATE_OP = "UPDATE";
|
|
private static $SET_OP = "SET";
|
|
private static $DELETE_OP = "DELETE";
|
|
private static $MAX_ROW_INDEX = 2147483647;
|
|
private $dbTableName;
|
|
private $tableNameIndex = 0;
|
|
private $lastWrappedTableName;
|
|
private $resultQuery;
|
|
private $mySQL;
|
|
private $lastError;
|
|
private $groupSettings;
|
|
private $customWhereClause = null;
|
|
public function __construct($mySQL, $table) {
|
|
if (!is_a($mySQL, "\mysqli") || !isset($table)) {
|
|
throw new \Exception("Invalid params");
|
|
}
|
|
$this->mySQL = $mySQL;
|
|
$this->dbTableName = $table;
|
|
$this->resultQuery = sprintf("%s %s %s %s",
|
|
self::$SELECT_OP,
|
|
self::$ALL_FIELDS,
|
|
self::$FROM_OP,
|
|
$this->dbTableName);
|
|
}
|
|
public function GetLastError() {
|
|
return $this->lastError;
|
|
}
|
|
|
|
public function getTableName() {
|
|
return $this->dbTableName;
|
|
}
|
|
private function _WrapQuery() {
|
|
$this->tableNameIndex++;
|
|
$this->lastWrappedTableName = "{$this->dbTableName}_{$this->tableNameIndex}";
|
|
$this->resultQuery = sprintf("%s %s %s (%s) %s %s",
|
|
self::$SELECT_OP,
|
|
self::$ALL_FIELDS,
|
|
self::$FROM_OP,
|
|
$this->resultQuery,
|
|
AggregateHelper::AS_OP,
|
|
$this->lastWrappedTableName);
|
|
}
|
|
private function _PrepareQueryForLastOperator($operator) {
|
|
$operator = trim($operator);
|
|
$lastOperatorPos = strrpos($this->resultQuery, " ".$operator." ");
|
|
if ($lastOperatorPos !== false) {
|
|
$lastBracketPos = strrpos($this->resultQuery, ")");
|
|
if (($lastBracketPos !== false && $lastOperatorPos > $lastBracketPos) || ($lastBracketPos === false)) {
|
|
$this->_WrapQuery();
|
|
}
|
|
}
|
|
}
|
|
public function Select($expression) {
|
|
Utils::EscapeExpressionValues($this->mySQL, $expression);
|
|
$this->_SelectImpl($expression);
|
|
return $this;
|
|
}
|
|
private function _SelectImpl($expression, $needQuotes = true) {
|
|
if (isset($expression)) {
|
|
$fields = "";
|
|
if (is_string($expression)) {
|
|
$expression = explode(",", $expression);
|
|
}
|
|
if (is_array($expression)) {
|
|
foreach ($expression as $field) {
|
|
$fields .= (strlen($fields) ? ", " : "").($needQuotes ? Utils::QuoteStringValue(trim($field)) : trim($field));
|
|
}
|
|
}
|
|
if (strlen($fields)) {
|
|
$allFieldOperatorPos = strpos($this->resultQuery, self::$ALL_FIELDS);
|
|
if ($allFieldOperatorPos == 7) {
|
|
$this->resultQuery = substr_replace($this->resultQuery, $fields, 7, strlen(self::$ALL_FIELDS));
|
|
}
|
|
else {
|
|
$this->_WrapQuery();
|
|
$this->_SelectImpl($expression);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
public function Filter($expression) {
|
|
Utils::EscapeExpressionValues($this->mySQL, $expression);
|
|
if (isset($expression) && is_array($expression)) {
|
|
$result = FilterHelper::GetSqlExprByArray($expression);
|
|
if (strlen($result)) {
|
|
$this->_PrepareQueryForLastOperator(self::$WHERE_OP);
|
|
$this->resultQuery .= sprintf(" %s %s",
|
|
self::$WHERE_OP,
|
|
$result);
|
|
}
|
|
}
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Add custom raw SQL WHERE clause
|
|
* This is used for complex filters that cannot be expressed via FilterHelper
|
|
* @param string $rawWhereClause Raw SQL WHERE clause (without WHERE keyword)
|
|
* @return $this
|
|
*/
|
|
public function AddCustomWhere($rawWhereClause) {
|
|
if (isset($rawWhereClause) && strlen(trim($rawWhereClause)) > 0) {
|
|
$this->_PrepareQueryForLastOperator(self::$WHERE_OP);
|
|
if (stripos($this->resultQuery, " " . self::$WHERE_OP . " ") !== false) {
|
|
$this->resultQuery .= sprintf(" AND (%s)",
|
|
trim($rawWhereClause));
|
|
if ($this->customWhereClause === null) {
|
|
$this->customWhereClause = trim($rawWhereClause);
|
|
} else {
|
|
$this->customWhereClause .= " AND (" . trim($rawWhereClause) . ")";
|
|
}
|
|
} else {
|
|
$this->resultQuery .= sprintf(" %s %s",
|
|
self::$WHERE_OP,
|
|
trim($rawWhereClause));
|
|
$this->customWhereClause = trim($rawWhereClause);
|
|
}
|
|
}
|
|
return $this;
|
|
}
|
|
public function Sort($expression) {
|
|
Utils::EscapeExpressionValues($this->mySQL, $expression);
|
|
if (isset($expression)) {
|
|
$result = "";
|
|
if (is_string($expression)) {
|
|
$result = trim($expression);
|
|
}
|
|
if (is_array($expression)) {
|
|
$fieldSet = AggregateHelper::GetFieldSetBySelectors($expression);
|
|
$result = $fieldSet["sort"];
|
|
}
|
|
if (strlen($result)) {
|
|
$this->_PrepareQueryForLastOperator(self::$ORDER_OP);
|
|
$this->resultQuery .= sprintf(" %s %s",
|
|
self::$ORDER_OP,
|
|
$result);
|
|
}
|
|
}
|
|
return $this;
|
|
}
|
|
public function SkipTake($skip, $take) {
|
|
$skip = (!isset($skip) || !is_int($skip) ? 0 : $skip);
|
|
$take = (!isset($take) || !is_int($take) ? self::$MAX_ROW_INDEX : $take);
|
|
if ($skip != 0 || $take != 0) {
|
|
$this->_PrepareQueryForLastOperator(self::$LIMIT_OP);
|
|
$this->resultQuery .= sprintf(" %s %0.0f, %0.0f",
|
|
self::$LIMIT_OP,
|
|
$skip,
|
|
$take);
|
|
}
|
|
return $this;
|
|
}
|
|
|
|
/**
|
|
* Extract WHERE clause from resultQuery
|
|
* @return string WHERE clause without WHERE keyword, or empty string
|
|
*/
|
|
private function _ExtractWhereClause() {
|
|
$whereClause = "";
|
|
$wherePos = stripos($this->resultQuery, " " . self::$WHERE_OP . " ");
|
|
if ($wherePos !== false) {
|
|
$afterWhere = substr($this->resultQuery, $wherePos + strlen(" " . self::$WHERE_OP . " "));
|
|
// Find the end of WHERE clause by looking for ORDER BY, GROUP BY, or LIMIT
|
|
// Use regex to find the first occurrence of these keywords
|
|
$endPattern = '/\s+(ORDER\s+BY|GROUP\s+BY|LIMIT)\s+/i';
|
|
if (preg_match($endPattern, $afterWhere, $matches, PREG_OFFSET_CAPTURE)) {
|
|
$whereClause = trim(substr($afterWhere, 0, $matches[0][1]));
|
|
} else {
|
|
$whereClause = trim($afterWhere);
|
|
}
|
|
}
|
|
return $whereClause;
|
|
}
|
|
|
|
/**
|
|
* Get distinct values for a specific column
|
|
* @param string $columnName Column name to get distinct values from
|
|
* @return array Array of distinct values
|
|
*/
|
|
public function GetDistinctValues($columnName) {
|
|
$result = array();
|
|
if ($this->mySQL && isset($columnName) && strlen($columnName) > 0) {
|
|
Utils::EscapeExpressionValues($this->mySQL, $columnName);
|
|
$quotedColumn = Utils::QuoteStringValue($columnName);
|
|
|
|
// Build the distinct query - use WHERE clause from resultQuery if exists
|
|
$distinctQuery = sprintf("%s DISTINCT %s %s %s",
|
|
self::$SELECT_OP,
|
|
$quotedColumn,
|
|
self::$FROM_OP,
|
|
$this->dbTableName);
|
|
|
|
// Extract WHERE clause from resultQuery (after WHERE keyword)
|
|
$whereClause = "";
|
|
$wherePos = stripos($this->resultQuery, " " . self::$WHERE_OP . " ");
|
|
if ($wherePos !== false) {
|
|
// Get everything after WHERE
|
|
$afterWhere = substr($this->resultQuery, $wherePos + strlen(" " . self::$WHERE_OP . " "));
|
|
|
|
// Remove ORDER BY, GROUP BY, LIMIT if they exist (find first occurrence)
|
|
$endPattern = '/\s+(ORDER\s+BY|GROUP\s+BY|LIMIT)\s+/i';
|
|
if (preg_match($endPattern, $afterWhere, $matches, PREG_OFFSET_CAPTURE)) {
|
|
$whereClause = trim(substr($afterWhere, 0, $matches[0][1]));
|
|
} else {
|
|
$whereClause = trim($afterWhere);
|
|
}
|
|
|
|
if (strlen($whereClause) > 0) {
|
|
$distinctQuery .= " " . self::$WHERE_OP . " " . $whereClause;
|
|
}
|
|
}
|
|
|
|
// Also add customWhereClause if exists (from AddCustomWhere)
|
|
if ($this->customWhereClause !== null) {
|
|
if (strlen($whereClause) > 0) {
|
|
$distinctQuery .= " AND (" . $this->customWhereClause . ")";
|
|
} else {
|
|
$distinctQuery .= " " . self::$WHERE_OP . " " . $this->customWhereClause;
|
|
}
|
|
}
|
|
|
|
// Add ORDER BY for sorted results
|
|
$distinctQuery .= sprintf(" %s %s",
|
|
self::$ORDER_OP,
|
|
$quotedColumn);
|
|
|
|
// Limit results to prevent memory issues with very large distinct value sets
|
|
// If there's a WHERE clause (filtered), we can return more results
|
|
// If no filter, limit to first 1000 to prevent UI freezing
|
|
$hasFilter = ($wherePos !== false && strlen($whereClause) > 0) || ($this->customWhereClause !== null);
|
|
if (!$hasFilter) {
|
|
$distinctQuery .= sprintf(" %s 1000",
|
|
self::$LIMIT_OP);
|
|
}
|
|
|
|
$this->lastError = NULL;
|
|
$sanitizedQuery = $this->_sanitizeDateValues($distinctQuery);
|
|
|
|
// Debug: Log the query for troubleshooting
|
|
error_log("GetDistinctValues SQL: " . $sanitizedQuery);
|
|
error_log("GetDistinctValues resultQuery: " . $this->resultQuery);
|
|
|
|
$queryResult = $this->mySQL->query($sanitizedQuery);
|
|
if (!$queryResult) {
|
|
$this->lastError = $this->mySQL->error;
|
|
error_log("GetDistinctValues query error: " . $this->lastError);
|
|
}
|
|
else {
|
|
while ($row = $queryResult->fetch_array(MYSQLI_NUM)) {
|
|
$value = $row[0];
|
|
if ($value !== null && $value !== '') {
|
|
$result[] = $value;
|
|
}
|
|
}
|
|
$queryResult->close();
|
|
error_log("GetDistinctValues found " . count($result) . " distinct values");
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Check if there are blank (NULL or empty) values for a specific column
|
|
* @param string $columnName Column name to check
|
|
* @return bool True if blanks exist, false otherwise
|
|
*/
|
|
public function HasBlanks($columnName) {
|
|
$result = false;
|
|
if ($this->mySQL && isset($columnName) && strlen($columnName) > 0) {
|
|
Utils::EscapeExpressionValues($this->mySQL, $columnName);
|
|
$quotedColumn = Utils::QuoteStringValue($columnName);
|
|
|
|
// Build the blank check query
|
|
$blankQuery = sprintf("%s COUNT(1) %s %s",
|
|
self::$SELECT_OP,
|
|
self::$FROM_OP,
|
|
$this->dbTableName);
|
|
|
|
// Extract WHERE clause from resultQuery
|
|
$wherePos = stripos($this->resultQuery, " " . self::$WHERE_OP . " ");
|
|
$blankCondition = sprintf("(%s IS NULL OR %s = '')",
|
|
$quotedColumn,
|
|
$quotedColumn);
|
|
|
|
if ($wherePos !== false) {
|
|
// Get everything after WHERE
|
|
$afterWhere = substr($this->resultQuery, $wherePos + strlen(" " . self::$WHERE_OP . " "));
|
|
|
|
// Remove ORDER BY, GROUP BY, LIMIT if they exist
|
|
$endPattern = '/\s+(ORDER\s+BY|GROUP\s+BY|LIMIT)\s+/i';
|
|
if (preg_match($endPattern, $afterWhere, $matches, PREG_OFFSET_CAPTURE)) {
|
|
$whereClause = trim(substr($afterWhere, 0, $matches[0][1]));
|
|
} else {
|
|
$whereClause = trim($afterWhere);
|
|
}
|
|
|
|
if (strlen($whereClause) > 0) {
|
|
$blankQuery .= " " . self::$WHERE_OP . " " . $whereClause . " AND " . $blankCondition;
|
|
} else {
|
|
$blankQuery .= " " . self::$WHERE_OP . " " . $blankCondition;
|
|
}
|
|
} else {
|
|
// No WHERE clause, add blank condition
|
|
$blankQuery .= " " . self::$WHERE_OP . " " . $blankCondition;
|
|
}
|
|
|
|
$this->lastError = NULL;
|
|
$sanitizedQuery = $this->_sanitizeDateValues($blankQuery);
|
|
$queryResult = $this->mySQL->query($sanitizedQuery);
|
|
if (!$queryResult) {
|
|
$this->lastError = $this->mySQL->error;
|
|
}
|
|
else if ($queryResult->num_rows > 0) {
|
|
$row = $queryResult->fetch_array(MYSQLI_NUM);
|
|
$result = Utils::StringToNumber($row[0]) > 0;
|
|
$queryResult->close();
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
private function _CreateGroupCountQuery($firstGroupField, $skip = NULL, $take = NULL) {
|
|
$groupCount = $this->groupSettings["groupCount"];
|
|
$lastGroupExpanded = $this->groupSettings["lastGroupExpanded"];
|
|
if (!$lastGroupExpanded) {
|
|
if ($groupCount === 2) {
|
|
$this->groupSettings["groupItemCountQuery"] = sprintf("%s COUNT(1) %s (%s) AS %s_%d",
|
|
self::$SELECT_OP,
|
|
self::$FROM_OP,
|
|
$this->resultQuery,
|
|
$this->dbTableName,
|
|
$this->tableNameIndex + 1);
|
|
if (isset($skip) || isset($take)) {
|
|
$this->SkipTake($skip, $take);
|
|
}
|
|
}
|
|
}
|
|
else {
|
|
$groupQuery = sprintf("%s COUNT(1) %s %s %s %s",
|
|
self::$SELECT_OP,
|
|
self::$FROM_OP,
|
|
$this->dbTableName,
|
|
self::$GROUP_OP,
|
|
$firstGroupField);
|
|
$this->groupSettings["groupItemCountQuery"] = sprintf("%s COUNT(1) %s (%s) AS %s_%d",
|
|
self::$SELECT_OP,
|
|
self::$FROM_OP,
|
|
$groupQuery,
|
|
$this->dbTableName,
|
|
$this->tableNameIndex + 1);
|
|
if (isset($skip) || isset($take)) {
|
|
$this->groupSettings["skip"] = isset($skip) ? Utils::StringToNumber($skip) : 0;
|
|
$this->groupSettings["take"] = isset($take) ? Utils::StringToNumber($take) : 0;
|
|
}
|
|
}
|
|
}
|
|
public function Group($expression, $groupSummary = NULL, $skip = NULL, $take = NULL) {
|
|
Utils::EscapeExpressionValues($this->mySQL, $expression);
|
|
Utils::EscapeExpressionValues($this->mySQL, $groupSummary);
|
|
$this->groupSettings = NULL;
|
|
if (isset($expression)) {
|
|
$groupFields = "";
|
|
$sortFields = "";
|
|
$selectFields = "";
|
|
$lastGroupExpanded = true;
|
|
$groupCount = 0;
|
|
if (is_string($expression)) {
|
|
$selectFields = $sortFields = $groupFields = trim($expression);
|
|
$groupCount = count(explode(",", $expression));
|
|
}
|
|
if (is_array($expression)) {
|
|
$groupCount = count($expression);
|
|
$fieldSet = AggregateHelper::GetFieldSetBySelectors($expression);
|
|
$groupFields = $fieldSet["group"];
|
|
$selectFields = $fieldSet["select"];
|
|
$sortFields = $fieldSet["sort"];
|
|
$lastGroupExpanded = AggregateHelper::IsLastGroupExpanded($expression);
|
|
}
|
|
if ($groupCount > 0) {
|
|
if (!$lastGroupExpanded) {
|
|
$groupSummaryData = isset($groupSummary) && is_array($groupSummary) ? AggregateHelper::GetSummaryInfo($groupSummary, $this->dbTableName) : NULL;
|
|
$selectExpression = sprintf("%s, %s(1)%s",
|
|
strlen($selectFields) ? $selectFields : $groupFields,
|
|
AggregateHelper::COUNT_OP,
|
|
(isset($groupSummaryData) && isset($groupSummaryData["fields"]) && strlen($groupSummaryData["fields"]) ?
|
|
", ".$groupSummaryData["fields"] : ""));
|
|
$groupCount++;
|
|
$this->_WrapQuery();
|
|
$this->_SelectImpl($selectExpression, false);
|
|
$this->resultQuery .= sprintf(" %s %s",
|
|
self::$GROUP_OP,
|
|
$groupFields);
|
|
$this->Sort($sortFields);
|
|
}
|
|
else {
|
|
$this->_WrapQuery();
|
|
$selectExpression = "{$selectFields}, {$this->lastWrappedTableName}.*";
|
|
$this->_SelectImpl($selectExpression, false);
|
|
$this->resultQuery .= sprintf(" %s %s",
|
|
self::$ORDER_OP,
|
|
$sortFields);
|
|
}
|
|
$lastGroupExpanded = true;
|
|
$this->groupSettings = array();
|
|
$this->groupSettings["groupCount"] = $groupCount;
|
|
$this->groupSettings["lastGroupExpanded"] = $lastGroupExpanded;
|
|
$this->groupSettings["summaryTypes"] = !$lastGroupExpanded ? $groupSummaryData["summaryTypes"] : NULL;
|
|
$firstGroupField = explode(",", $groupFields)[0];
|
|
$this->_CreateGroupCountQuery($firstGroupField, $skip, $take);
|
|
}
|
|
}
|
|
return $this;
|
|
}
|
|
public function GetTotalSummary($expression, $filterExpression = NULL) {
|
|
Utils::EscapeExpressionValues($this->mySQL, $expression);
|
|
Utils::EscapeExpressionValues($this->mySQL, $filterExpression);
|
|
$result = NULL;
|
|
if (isset($expression) && is_array($expression)) {
|
|
$summaryInfo = AggregateHelper::GetSummaryInfo($expression, $this->dbTableName);
|
|
$fields = $summaryInfo["fields"];
|
|
if (strlen($fields) > 0) {
|
|
$filter = "";
|
|
if (isset($filterExpression)) {
|
|
if (is_string($filterExpression)) {
|
|
$filter = trim($filterExpression);
|
|
}
|
|
if (is_array($filterExpression)) {
|
|
$filter = FilterHelper::GetSqlExprByArray($filterExpression);
|
|
}
|
|
}
|
|
|
|
$combinedFilter = "";
|
|
if (strlen($filter) > 0 && $this->customWhereClause !== null) {
|
|
$combinedFilter = "(" . $filter . ") AND (" . $this->customWhereClause . ")";
|
|
} else if (strlen($filter) > 0) {
|
|
$combinedFilter = $filter;
|
|
} else if ($this->customWhereClause !== null) {
|
|
$combinedFilter = $this->customWhereClause;
|
|
}
|
|
|
|
$totalSummaryQuery = sprintf("%s %s %s %s %s",
|
|
self::$SELECT_OP,
|
|
$fields,
|
|
self::$FROM_OP,
|
|
$this->dbTableName,
|
|
strlen($combinedFilter) > 0 ? self::$WHERE_OP." ".$combinedFilter : "");
|
|
$this->lastError = NULL;
|
|
$queryResult = $this->mySQL->query($totalSummaryQuery);
|
|
if (!$queryResult) {
|
|
$this->lastError = $this->mySQL->error;
|
|
}
|
|
else if ($queryResult->num_rows > 0) {
|
|
$result = $queryResult->fetch_array(MYSQLI_NUM);
|
|
foreach ($result as $i => $item) {
|
|
$result[$i] = Utils::StringToNumber($item);
|
|
}
|
|
}
|
|
if ($queryResult !== false) {
|
|
$queryResult->close();
|
|
}
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
public function GetGroupCount() {
|
|
$result = 0;
|
|
if ($this->mySQL && isset($this->groupSettings) && isset($this->groupSettings["groupItemCountQuery"])) {
|
|
$this->lastError = NULL;
|
|
$queryResult = $this->mySQL->query($this->groupSettings["groupItemCountQuery"]);
|
|
if (!$queryResult) {
|
|
$this->lastError = $this->mySQL->error;
|
|
}
|
|
else if ($queryResult->num_rows > 0) {
|
|
$row = $queryResult->fetch_array(MYSQLI_NUM);
|
|
$result = Utils::StringToNumber($row[0]);
|
|
}
|
|
if ($queryResult !== false) {
|
|
$queryResult->close();
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
public function GetCount() {
|
|
$result = 0;
|
|
if ($this->mySQL) {
|
|
$countQuery = sprintf("%s %s(1) %s (%s) %s %s_%d",
|
|
self::$SELECT_OP,
|
|
AggregateHelper::COUNT_OP,
|
|
self::$FROM_OP,
|
|
$this->resultQuery,
|
|
AggregateHelper::AS_OP,
|
|
$this->dbTableName,
|
|
$this->tableNameIndex + 1);
|
|
$this->lastError = NULL;
|
|
$countQuery = $this->_sanitizeDateValues($countQuery);
|
|
$queryResult = $this->mySQL->query($countQuery);
|
|
if (!$queryResult) {
|
|
$this->lastError = $this->mySQL->error;
|
|
}
|
|
else if ($queryResult->num_rows > 0) {
|
|
$row = $queryResult->fetch_array(MYSQLI_NUM);
|
|
$result = Utils::StringToNumber($row[0]);
|
|
}
|
|
if ($queryResult !== false) {
|
|
$queryResult->close();
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
private function _sanitizeDateValues($query) {
|
|
return preg_replace("/'0NaN-NaN-NaN'|'\d*NaN-NaN-NaN'/", "NULL", $query);
|
|
}
|
|
public function AsArray() {
|
|
$result = NULL;
|
|
if ($this->mySQL) {
|
|
$this->lastError = NULL;
|
|
$sanitizedQuery = $this->_sanitizeDateValues($this->resultQuery);
|
|
$queryResult = $this->mySQL->query($sanitizedQuery);
|
|
if (!$queryResult) {
|
|
$this->lastError = $this->mySQL->error;
|
|
}
|
|
else {
|
|
if (isset($this->groupSettings)) {
|
|
$result = AggregateHelper::GetGroupedDataFromQuery($queryResult, $this->groupSettings);
|
|
}
|
|
else {
|
|
$result = $queryResult->fetch_all(MYSQLI_ASSOC);
|
|
}
|
|
$queryResult->close();
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
public function Insert($values) {
|
|
Utils::EscapeExpressionValues($this->mySQL, $values);
|
|
$result = NULL;
|
|
if (isset($values) && is_array($values)) {
|
|
$fields = "";
|
|
$fieldValues = "";
|
|
foreach ($values as $prop => $value) {
|
|
$fields .= (strlen($fields) ? ", " : "").Utils::QuoteStringValue($prop);
|
|
$fieldValues .= (strlen($fieldValues) ? ", " : "").Utils::QuoteStringValue($value, false);
|
|
}
|
|
if (strlen($fields) > 0) {
|
|
$queryString = sprintf("%s %s (%s) %s(%s)",
|
|
self::$INSERT_OP,
|
|
$this->dbTableName,
|
|
$fields,
|
|
self::$VALUES_OP,
|
|
$fieldValues);
|
|
$this->lastError = NULL;
|
|
if ($this->mySQL->query($queryString) == true) {
|
|
$result = $this->mySQL->affected_rows;
|
|
}
|
|
else {
|
|
$this->lastError = $this->mySQL->error;
|
|
}
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
public function Update($key, $values) {
|
|
Utils::EscapeExpressionValues($this->mySQL, $key);
|
|
Utils::EscapeExpressionValues($this->mySQL, $values);
|
|
$result = NULL;
|
|
if (isset($key) && is_array($key) && isset($values) && is_array($values)) {
|
|
$fields = "";
|
|
foreach ($values as $prop => $value) {
|
|
$templ = strlen($fields) == 0 ? "%s = %s" : ", %s = %s";
|
|
$fields .= sprintf($templ,
|
|
Utils::QuoteStringValue($prop),
|
|
Utils::QuoteStringValue($value, false));
|
|
}
|
|
if (strlen($fields) > 0) {
|
|
$queryString = sprintf("%s %s %s %s %s %s",
|
|
self::$UPDATE_OP,
|
|
$this->dbTableName,
|
|
self::$SET_OP,
|
|
$fields,
|
|
self::$WHERE_OP,
|
|
FilterHelper::GetSqlExprByKey($key));
|
|
$this->lastError = NULL;
|
|
if ($this->mySQL->query($queryString) == true) {
|
|
$result = $this->mySQL->affected_rows;
|
|
}
|
|
else {
|
|
$this->lastError = $this->mySQL->error;
|
|
}
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
public function Delete($key) {
|
|
Utils::EscapeExpressionValues($this->mySQL, $key);
|
|
$result = NULL;
|
|
if (isset($key) && is_array($key)) {
|
|
$queryString = sprintf("%s %s %s %s %s",
|
|
self::$DELETE_OP,
|
|
self::$FROM_OP,
|
|
$this->dbTableName,
|
|
self::$WHERE_OP,
|
|
FilterHelper::GetSqlExprByKey($key));
|
|
$this->lastError = NULL;
|
|
if ($this->mySQL->query($queryString) == true) {
|
|
$result = $this->mySQL->affected_rows;
|
|
}
|
|
else {
|
|
$this->lastError = $this->mySQL->error;
|
|
}
|
|
}
|
|
return $result;
|
|
}
|
|
}
|