/** * Pass in parameter to get query SQL * @param String $sTable * @param array $aCond * @param array $aGroupBy * @param array $aOrderby * @param String $sSelect * @param int $iLimit * @param int $iOffset */ public function getQuery($sTable = "", $aCond = array(), $aGroupBy = null, $aOrderby = null, $sSelect = null, $iLimit = null, $iOffset = 0) { $bDebug = false; $xpdo = FlexiModelUtil::getInstance()->getXPDO(); $sFromQuery = $this->getFromQuerySyntax($sTable); //any auto condition changes here //pass to event handler, throw exception to stop process //$this->onQueryParam($sTable, $aCond, $aGroupBy, $aOrderby, $sSelect, $iLimit, $iOffset); $sSQL = ""; if (empty($sSelect)) { $sSQL = "SELECT * FROM " . $sFromQuery; } else { $sSQL = $sSelect . " FROM " . $sFromQuery; } $aParam = array(); $aCond = $this->getObjectCond($aCond); $aWhere = FlexiModelUtil::parseSQLCond($aCond); if (!empty($aWhere["sql"])) { $sSQL .= " WHERE " . $aWhere["sql"]; $aParam = array_merge($aParam, $aWhere["param"]); } //if ($bDebug) var_dump($aCond); //if ($bDebug) var_dump($aWhere); //if ($bDebug) var_dump($aParam); $sGroupSQL = ""; if (!is_null($aGroupBy)) { foreach ($aGroupBy as $sGroup) { $sGroupSQL .= empty($sGroupSQL) ? "" : ","; $sGroupSQL .= FlexiModelUtil::parseSQLName($sGroup); } $sSQL .= !empty($sGroupSQL) ? " group by " . $sGroupSQL : ""; } if (!is_null($aOrderby)) { $sOrderbySQL = ""; foreach ($aOrderby as $sOrderBy) { $sOrderbySQL .= empty($sOrderbySQL) ? "" : ","; $aOrder = explode(" ", $sOrderBy); $sOrderType = count($aOrder) > 1 ? strtolower($aOrder[1]) == "asc" ? "ASC" : "DESC" : "ASC"; $sOrderbySQL .= FlexiModelUtil::parseSQLName($aOrder[0]) . " " . $sOrderType; } $sSQL .= !empty($sOrderbySQL) ? " order by " . $sOrderbySQL : ""; } if ($iLimit > 0) { $sSQL .= " limit " . $iLimit . " offset " . $iOffset; } if ($bDebug) { echo __METHOD__ . ": " . $sSQL . "<br/>\n"; } $this->onParseQueryBinding($sSQL, $aParam); $sResultSQL = $xpdo->parseBindings($sSQL, $aParam); if ($bDebug) { echo __METHOD__ . ": " . $sResultSQL . "<br/>\n"; } return $sResultSQL; }
function dbUpdate($oRow, $sTable, $mPrimary = "id", $pdo = null) { $pdo = is_null($pdo) ? FlexiModelUtil::getInstance()->getXPDO() : $pdo; $bDebug = false; $aPrimary = !is_array($mPrimary) ? explode(",", $mPrimary . "") : $mPrimary; if ($bDebug) { echo __METHOD__ . ": primary:" . print_r($aPrimary, true) . "\n<br/>"; } $aWhere = FlexiModelUtil::parseSQLCondKeyValue($aPrimary, $oRow); $sFields = ""; $aParam = $aWhere["param"]; foreach ($oRow as $sField => $sValue) { $sFieldRaw = dbCleanName($sField); $sFieldName = FlexiModelUtil::parseSQLName($sField); $sFields .= empty($sFields) ? "" : ","; $sFields .= $sFieldName . "=:_update_" . $sFieldRaw; $aParam[":_update_" . $sFieldRaw] = $oRow[$sField]; } $sSQL = "UPDATE " . FlexiModelUtil::parseSQLName($sTable) . " SET " . $sFields . " WHERE " . $aWhere["sql"]; if ($bDebug) { echo __METHOD__ . ": sql:" . $sSQL . "\n<br/>"; } if ($bDebug) { echo __METHOD__ . ": param:" . print_r($aWhere["param"], true) . "\n<br/>"; } $oStatement = $pdo->prepare($sSQL, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $mResult = $oStatement->execute($aParam); if ($mResult === false) { $aError = $oStatement->errorInfo(); throw new Exception("Query failed: " . $aError[2] . ":" . $sSQL . ":" . print_r($aParam, true)); } return $mResult; }