This is used by subclasses to add extra SQL options to the end of a query.
public static ProcessOptions ( string $p_queryStr, array $p_sqlOptions ) : string | ||
$p_queryStr | string | The current SQL query. |
$p_sqlOptions | array | Available options: 'LIMIT' => [max_rows_to_fetch] 'LIMIT' => array('START'=>[Starting_index],'MAX_ROWS'=>[Max_rows_to_fetch] 'ORDER BY' => [column_name] 'ORDER BY' => array([column_name_1], [column_name_2], ...) 'ORDER BY' => array([column_name_1]=>[ASC|DESC], [column_name_2]=>[ASC|DESC], ...) |
리턴 | string | Original SQL query with the options appended at the end. |
/** * Return all publications as an array of Publication objects. * * @param string $p_name * @param int $p_aliasId * @param array $p_sqlOptions * * @return array */ public static function GetPublications($p_name = null, $p_aliasId = null, $p_sqlOptions = null) { global $g_ado_db; if (is_null($p_sqlOptions)) { $p_sqlOptions = array(); } if (!isset($p_sqlOptions["ORDER BY"])) { $p_sqlOptions["ORDER BY"] = array("Name" => "ASC"); } $tmpPub = new Publication(); $columns = $tmpPub->getColumnNames(true); $queryStr = 'SELECT ' . implode(',', $columns) . ', Aliases.Name as Alias' . ', URLTypes.Name as URLType' . ', Languages.OrigName as NativeName' . ' FROM Publications, Languages, Aliases, URLTypes' . ' WHERE Publications.IdDefaultAlias = Aliases.Id ' . ' AND Publications.IdURLType = URLTypes.Id ' . ' AND Publications.IdDefaultLanguage = Languages.Id '; if (is_string($p_name)) { $queryStr .= " AND Publications.Name=" . $g_ado_db->escape($p_name); } if (is_numeric($p_aliasId)) { $queryStr .= " AND Publications.IdDefaultAlias={$p_aliasId}"; } $queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions); $publications = DbObjectArray::Create('Publication', $queryStr); return $publications; }
/** * @param array $p_sqlOptions * @param boolean $p_update * @param boolean $p_useFilter * filter templates matching setting in SystemPrefs * @param boolean $p_strict * if true, retrieves only template (tpl) files * * @return array */ public static function GetAllTemplates($p_sqlOptions = null, $p_update = true, $p_useFilter = false, $p_strict = false) { if ($p_update) { self::UpdateStatus(); } $queryStr = 'SELECT * FROM Templates'; if ($p_strict) { $queryStr .= ' WHERE Type < 5'; } if ($p_useFilter && ($rexeg = self::GetTemplateFilterRegex(true))) { $queryStr .= $p_strict == false ? ' WHERE ' : ' AND '; $queryStr .= 'Name NOT REGEXP "' . self::GetTemplateFilterRegex(true) . '"'; } if (!is_null($p_sqlOptions)) { $queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions); } else { $queryStr .= ' ORDER BY Level ASC, Name ASC'; } $templates = DbObjectArray::Create('Template', $queryStr); return $templates; }
/** * Get a list of articles. You can be as specific or as general as you * like with the parameters: e.g. specifying only p_publication will get * you all the articles in a particular publication. Specifying all * parameters will get you all the articles in a particular section with * the given language. * * This function differs from GetArticles in that any LIMIT set * in $p_sqlOptions will be interpreted as the number of articles to * return regardless of how many times an article has been translated. * E.g. an article translated three times would be counted as one * article, but counted as three articles in GetArticles(). * * @param int $p_publicationId - * The publication ID. * * @param int $p_issueNumber - * The issue number. * * @param int $p_sectionNumber - * The section number. * * @param int $p_languageId - * The language ID. * * @param int $p_preferredLanguage - * If specified, list the articles in this language before others. * * @param array $p_sqlOptions * * @param boolean $p_countOnly * Whether to run just the number of articles that match the * search criteria. * * @return array * Return an array of Article objects. */ public static function GetArticlesGrouped($p_publicationId = null, $p_issueNumber = null, $p_sectionNumber = null, $p_languageId = null, $p_preferredLanguage = null, $p_sqlOptions = null, $p_countOnly = false) { global $g_ado_db; // Constraints $whereClause = array(); if (!is_null($p_publicationId)) { $whereClause[] = "IdPublication=$p_publicationId"; } if (!is_null($p_issueNumber)) { $whereClause[] = "NrIssue=$p_issueNumber"; } if (!is_null($p_sectionNumber)) { $whereClause[] = "NrSection=$p_sectionNumber"; } if (!is_null($p_languageId)) { $whereClause[] = "IdLanguage=$p_languageId"; } $selectStr = "DISTINCT(Number)"; if ($p_countOnly) { $selectStr = "COUNT(DISTINCT(Number))"; } // Get the list of unique article numbers $queryStr1 = "SELECT $selectStr FROM Articles "; if (count($whereClause) > 0) { $queryStr1 .= ' WHERE '. implode(' AND ', $whereClause); } if ($p_countOnly) { $count = $g_ado_db->GetOne($queryStr1); return $count; } if (is_null($p_sqlOptions)) { $p_sqlOptions = array(); } if (!isset($p_sqlOptions['ORDER BY'])) { $p_sqlOptions['ORDER BY'] = array("ArticleOrder" => "ASC", "Number"=> "DESC"); } $queryStr1 = DatabaseObject::ProcessOptions($queryStr1, $p_sqlOptions); $uniqueArticleNumbers = $g_ado_db->GetCol($queryStr1); // Get the articles $queryStr2 = 'SELECT *'; // This causes the preferred language to be listed first. if (!is_null($p_preferredLanguage)) { $queryStr2 .= ", abs($p_preferredLanguage - IdLanguage) as LanguageOrder "; } $queryStr2 .= ' FROM Articles'; $uniqueRowsClause = ''; if (count($uniqueArticleNumbers) > 0) { $uniqueRowsClause = '(Number=' .implode(' OR Number=', $uniqueArticleNumbers).')'; } // Add the WHERE clause. if ((count($whereClause) > 0) || ($uniqueRowsClause != '')) { $queryStr2 .= ' WHERE '; if (count($whereClause) > 0) { $queryStr2 .= '(' . implode(' AND ', $whereClause) .')'; } if ($uniqueRowsClause != '') { if (count($whereClause) > 0) { $queryStr2 .= ' AND '; } $queryStr2 .= $uniqueRowsClause; } } // ORDER BY clause if (!is_null($p_preferredLanguage)) { $p_sqlOptions['ORDER BY']['LanguageOrder'] = "ASC"; $p_sqlOptions['ORDER BY']['IdLanguage'] = "ASC"; } unset($p_sqlOptions['LIMIT']); $queryStr2 = DatabaseObject::ProcessOptions($queryStr2, $p_sqlOptions); $articles = DbObjectArray::Create('Article', $queryStr2); return $articles; } // fn GetUniqueArticles
/** * Get all the issues in the given publication as return them as an array * of Issue objects. * * @param int $p_publicationId * The publication ID. * * @param int $p_languageId * (Optional) Only return issues with this language. * * @param int $p_issueId * (Optional) Only return issues with this Issue ID. * * @param string $p_urlName * (Optional) Only return issues that match this URL Name. * * @param int $p_preferredLanguage * (Optional) List this language before others. This will override any 'ORDER BY' sql * options you have. * * @param array $p_sqlOptions * * @return array */ public static function GetIssues($p_publicationId = null, $p_languageId = null, $p_issueNumber = null, $p_urlName = null, $p_preferredLanguage = null, $p_publishedOnly = false, $p_sqlOptions = null, $p_skipCache = false) { global $g_ado_db; if (!$p_skipCache && CampCache::IsEnabled()) { $paramsArray['publication_id'] = (is_null($p_publicationId)) ? 'null' : $p_publicationId; $paramsArray['language_id'] = (is_null($p_languageId)) ? 'null' : $p_languageId; $paramsArray['issue_number'] = (is_null($p_issueNumber)) ? 'null' : $p_issueNumber; $paramsArray['url_name'] = (is_null($p_urlName)) ? 'null' : $p_urlName; $paramsArray['preferred_language'] = (is_null($p_preferredLanguage)) ? 'null' : $p_preferredLanguage; $paramsArray['published_only'] = $p_publishedOnly ? 'true' : 'false'; $paramsArray['sql_options'] = (is_null($p_sqlOptions)) ? 'null' : $p_sqlOptions; $cacheListObj = new CampCacheList($paramsArray, __METHOD__); $issuesList = $cacheListObj->fetchFromCache(); if ($issuesList !== false && is_array($issuesList)) { return $issuesList; } } $tmpIssue = new Issue(); $columnNames = $tmpIssue->getColumnNames(true); $queryStr = 'SELECT '.implode(',', $columnNames); if (!is_null($p_preferredLanguage)) { $queryStr .= ", abs(IdLanguage-$p_preferredLanguage) as LanguageOrder"; $p_sqlOptions['ORDER BY'] = array('Number' => 'DESC', 'LanguageOrder' => 'ASC'); } // We have to display the language name so oftern that we might // as well fetch it by default. $queryStr .= ', Languages.OrigName as LanguageName'; $queryStr .= ' FROM Issues, Languages '; $whereClause = array(); $whereClause[] = "Issues.IdLanguage=Languages.Id"; if (!is_null($p_publicationId)) { $whereClause[] = "Issues.IdPublication=$p_publicationId"; } if (!is_null($p_languageId)) { $whereClause[] = "Issues.IdLanguage=$p_languageId"; } if (!is_null($p_issueNumber)) { $whereClause[] = "Issues.Number=$p_issueNumber"; } if (!is_null($p_urlName)) { $whereClause[] = "Issues.ShortName='".$g_ado_db->escape($p_urlName)."'"; } if ($p_publishedOnly) { $whereClause[] = "Issues.Published = 'Y'"; } if (count($whereClause) > 0) { $queryStr .= ' WHERE '.implode(' AND ', $whereClause); } $queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions); $issues = array(); $rows = $g_ado_db->GetAll($queryStr); if (is_array($rows)) { foreach ($rows as $row) { $tmpObj = new Issue(); $tmpObj->fetch($row); $tmpObj->m_languageName = $row['LanguageName']; $issues[] = $tmpObj; } } if (!$p_skipCache && CampCache::IsEnabled()) { $cacheListObj->storeInCache($issues); } return $issues; } // fn GetIssues
/** * Get the logs. * * @param int $p_eventId * @param array $p_sqlOptions * * @return array */ public static function GetLogs($p_eventId = null, $p_sqlOptions = null) { if (is_null($p_sqlOptions) || !isset($p_sqlOptions['ORDER BY'])) { $p_sqlOptions['ORDER BY'] = array('time_created' => 'DESC'); } $tmpLog = new Log(); $columns = $tmpLog->getColumnNames(true); $queryStr = "SELECT " . implode(", ", $columns) . ", INET_NTOA(Log.user_ip) AS user_ip_addr" . ", liveuser_users.Name as full_name" . ", liveuser_users.UName as user_name" . " FROM Log" . " LEFT JOIN liveuser_users" . " ON Log.fk_user_id = liveuser_users.Id"; if (!is_null($p_eventId)) { $queryStr .= " WHERE Log.fk_event_id={$p_eventId}"; } $queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions); $logLines = DbObjectArray::Create('Log', $queryStr); return $logLines; }
/** * Search the Topics table. * * @param int $p_id * @param int $p_languageId * @param string $p_name * @param int $p_parentId * @param array $p_sqlOptions * @return array */ public static function GetTopics($p_id = null, $p_languageId = null, $p_name = null, $p_parentId = null, $p_depth = 1, $p_sqlOptions = null, $p_order = null, $p_countOnly = false, $p_skipCache = false) { global $g_ado_db; if (!$p_skipCache && CampCache::IsEnabled()) { $paramsArray['id'] = is_null($p_id) ? '' : $p_id; $paramsArray['language_id'] = is_null($p_languageId) ? '' : $p_languageId; $paramsArray['name'] = is_null($p_name) ? '' : $p_name; $paramsArray['parent_id'] = is_null($p_parentId) ? '' : $p_parentId; $paramsArray['depth'] = is_null($p_depth) ? '' : $p_depth; $paramsArray['sql_options'] = $p_sqlOptions; $paramsArray['order'] = $p_order; $paramsArray['count_only'] = (int) $p_countOnly; $cacheListObj = new CampCacheList($paramsArray, __METHOD__); $topics = $cacheListObj->fetchFromCache(); if ($topics !== false && is_array($topics)) { return $p_countOnly ? $topics['count'] : $topics; } } if (!is_array($p_order) || count($p_order) == 0) { $p_order = array(array('field' => 'default', 'dir' => 'asc')); } foreach ($p_order as $orderCond) { switch (strtolower($orderCond['field'])) { case 'default': $order['t.node_left'] = $orderCond['dir']; break; case 'byname': $order['tn.name'] = $orderCond['dir']; break; case 'bynumber': $order['t.id'] = $orderCond['dir']; break; } } $p_sqlOptions['ORDER BY'] = $order; $query = new SQLSelectClause(); $query->addColumn('t.id'); $topicObj = new Topic(); $topicNameObj = new TopicName(); if (!is_null($p_languageId) && is_numeric($p_languageId) || !is_null($p_name) || isset($order['tn.name'])) { $query->setTable($topicObj->m_dbTableName . ' AS t LEFT JOIN ' . $topicNameObj->m_dbTableName . ' AS tn ON t.id = tn.fk_topic_id'); } else { $query->setTable($topicObj->m_dbTableName . ' AS t'); } $constraints = array(); if (!is_null($p_id) && is_numeric($p_id)) { $query->addWhere("t.id = '{$p_id}'"); } if (!is_null($p_languageId) && is_numeric($p_languageId)) { $query->addWhere("tn.fk_language_id = '{$p_languageId}'"); } if (!is_null($p_name)) { $query->addWhere("tn.name = " . $g_ado_db->escape($p_name)); } if (!is_null($p_parentId)) { $subtopicsQuery = self::BuildSubtopicsQuery($p_parentId, $p_depth, 1); $query->addTableFrom('(' . $subtopicsQuery->buildQuery() . ') AS in_query'); $query->addWhere("t.id = in_query.id"); } $queryStr = $query->buildQuery(); $queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions); if ($p_countOnly) { $queryStr = "SELECT COUNT(*) FROM ({$queryStr}) AS topics"; $topics['count'] = $g_ado_db->GetOne($queryStr); } else { $topics = array(); $rows = $g_ado_db->GetAll($queryStr); foreach ($rows as $row) { $topics[] = new Topic($row['id']); } } if (!$p_skipCache && CampCache::IsEnabled()) { $cacheListObj->storeInCache($topics); } return $topics; }
/** * Get the comments and their associated articles. * * @param string $p_status * Can be 'approved' or 'unapproved'. * @param boolean $p_getTotal * If TRUE, return the number of comments that match the search * criteria and not the actual records. * @param string $p_searchString * A string to search for. * @param array $p_sqlOptions * See DatabaseObject::ProcessOptions(). * @return array */ public static function GetComments($p_status = 'approved', $p_getTotal = false, $p_searchString = '', $p_sqlOptions = null) { global $PHORUM; global $g_ado_db; $messageTable = $PHORUM['message_table']; $selectClause = "*"; if ($p_getTotal) { $selectClause = "COUNT(*)"; } $baseQuery = "SELECT $selectClause FROM ($messageTable" ." LEFT JOIN ArticleComments " ." ON $messageTable". ".thread=ArticleComments.fk_comment_id)" ." LEFT JOIN Articles ON ArticleComments.fk_article_number=Articles.Number" ." AND ArticleComments.fk_language_id=Articles.IdLanguage"; $whereQuery = "$messageTable.message_id != $messageTable.thread"; if ($p_status == 'approved') { $whereQuery .= " AND status > 0"; } elseif ($p_status == 'unapproved') { $whereQuery .= " AND status < 0"; } if (!empty($p_searchString)) { $p_searchString = mysql_real_escape_string($p_searchString); if (!empty($whereQuery)) { $whereQuery .= " AND "; } $search_columns = array( 'subject', 'body', 'email', 'author', 'ip', 'Name', ); $whereAry = array(); foreach ($search_columns as $column) { $whereAry[] = $column . " LIKE '%$p_searchString%'"; } $whereQuery .= '(' . implode(' OR ', $whereAry) . ')'; } if (!empty($whereQuery)) { $baseQuery .= " WHERE ".$whereQuery; } // Default ORDER BY clause if (is_null($p_sqlOptions) || !isset($p_sqlOptions['ORDER BY'])) { $baseQuery .= " ORDER BY ".$PHORUM['message_table'].".message_id"; } //echo $baseQuery."<br><br>"; if ($p_getTotal) { $numComments = $g_ado_db->GetOne($baseQuery); return $numComments; } else { $queryStr = DatabaseObject::ProcessOptions($baseQuery, $p_sqlOptions); $rows = $g_ado_db->GetAll($queryStr); $returnArray = array(); if (is_array($rows)) { foreach ($rows as $row) { $comment = new Phorum_message(); $comment->fetch($row); $article = new Article(); $article->fetch($row); $returnArray[] = array("comment" => $comment, "article" => $article); } } return $returnArray; } } // fn GetComments
/** * Do a simple search. * * @param array $p_columns * Array of arrays of two strings: column name and search value. * @param array $p_sqlOptions * See ProcessOptions(). * * @return array */ public static function Search($p_className, $p_columns = null, $p_sqlOptions = null) { global $g_ado_db; if (!class_exists($p_className)) { return array(); } $tmpObj = new $p_className(); $queryStr = "SELECT * FROM " . $tmpObj->m_dbTableName; if (is_array($p_columns) && count($p_columns) > 0) { $contraints = array(); foreach ($p_columns as $item) { if (count($item) == 2) { list($columnName, $value) = $item; $contraints[] = $g_ado_db->escapeKeyVal($columnName, $value); } } $queryStr .= " WHERE " . implode(" AND ", $contraints); } $queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions); $dbObjects = DbObjectArray::Create($p_className, $queryStr); return $dbObjects; }