今天，市场部的同事反映Help版块，输入关键词查询时，没有查到结果，页面顶部出现错误：Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/sctwo000/public_html/help/lib/database/mysql.php on line 534
测试了下，发现：输入数字查询时有相关结果，输入其他任意字符都会报错。于是判断出：是查询过程，字符的转换出的问题。
修改了：/includes/classes/class.search.parent.php文件就Ok了。文件代码如下：
<?php
class AKB_SEARCH_PARENT
{
var $searchString = '';
var $searchResults = array();
var $searchResultsIds = array();
var $searchCategories = array();
var $searchType = 1;
var $ignoreWords = array();
var $maxResults = 5;
var $constraintFunctions = array();
var $start = 0;
var $doCustomFields = false;
var $customFieldData = array();
/**
* Constructor
* Setup variables for this class
*
* @return void
*/
function AKB_SEARCH_PARENT()
{
$this->searchType = (int) $GLOBALS['defaultSearchType'];
$this->ignoreWords = explode(',', str_replace(","," ,",$GLOBALS['skipWords']));
if ((is_array($this->ignoreWords)) && (count($this->ignoreWords) > 0)) {
$additionalWords = $this->ignoreWords;
array_walk($additionalWords,array($this, "processExtraIgnoreWords"));
$this->ignoreWords = array_merge($this->ignoreWords,$additionalWords);
}
}
function processExtraIgnoreWords(&$passedItem) {
$passedItem = trim($passedItem).",";
}
// {{{ questFullTextSearch()
/**
* questFullTextSearch
* Perform a full text search and save the results in this->searchResults
*
* @param bool $booleanmode Are we searching using fulltext boolean mode
*
* @return bool Returns false if there are no results
*/
function questFullTextSearch($booleanmode = false)
{
$this->searchString = trim($this->searchString);
// We can't search without something to search for
if ((empty($this->searchString)) && (!$this->doCustomFields)) {
return;
} elseif (strlen(trim($this->searchString)) < 3 && (!$this->doCustomFields)) {
return;
}
$orderBy = ' lastupdated ';
$workflowQry = '';
if (validUrl()) {
$workflowQry = ' AND ( q.workflowstatus = \''.WF_STATUS_APPROVED.'\' OR q.workflowstatus = \''.WF_STATUS_AUTO_APPROVED.'\' ) ';
}
// Enable the quote search for boolean mode set.
if ($booleanmode && $GLOBALS['databaseType'] == 'mysql') {
$this->searchString = addslashes($this->searchString);
}
// Construct the full text search part of the query
$fullTextFields = array ('title', 'answer', 'metakeywords');
$rependingFullTextFields = array ('qh.title', 'qh.answer', 'qh.metakeywords');
$allowed_cats = array();
if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$allowed_cats = $GLOBALS['AKB_CLASS_HELPER']->GetPermittedCats();
}
//Is this inline help searching?
if ((isInlineHelp()) && (isset($_GET['searchOverride']))) {
$_carray = $GLOBALS['AKB_CLASS_HELPER']->tree->GetBranchFrom((int)$_GET['searchOverride'],false);
$_carray[] = (int)$_GET['searchOverride'];
}
$query = 'SELECT questionid, title, '.$GLOBALS['AKB_DB']->SubString('answer', 1, 201).' as answer , lastupdated, workflowstatus, \'articles\' as types, \'Question\' as plugin ,\'_\' as href ';
if (!empty($this->searchString) && $GLOBALS['databaseType'] == 'mysql') {
$query .= ", ".$GLOBALS['AKB_DB']->FullText($fullTextFields, $this->searchString, $booleanmode).' as score';
$orderBy = ' score DESC , '. $orderBy;
}
$query .= "\n FROM ".$GLOBALS['tablePrefix']."questions ";
$query .= "\n WHERE questionid IN ";
$query .= "\n ( ";
$query .= 'SELECT DISTINCT q.questionid';
$query .= "\n FROM ".$GLOBALS['tablePrefix']."categoryassociations a, ".$GLOBALS['tablePrefix']."categories c, ";
$query .= '('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid)';
$query .= ' WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
AND q.visible = 1
'.$workflowQry.'
AND
(
((q.startdate < \''.$GLOBALS['currDatetime']."') AND ((q.enableexpiry = 1) AND q.expirydate > '".$GLOBALS['currDatetime']."'))
OR
((q.startdate < '".$GLOBALS['currDatetime']."') AND (q.enableexpiry = 0))
)
AND (qa.groupid is null ".AKB_AUTH::deriveGroupSQL(false).") ";
$count_query = 'SELECT COUNT(DISTINCT q.questionid, ';
if (!empty($this->searchString) && $GLOBALS['databaseType'] == 'mysql') {
$count_query .= $GLOBALS['AKB_DB']->FullText($fullTextFields, $this->searchString, $booleanmode).') as num';
} else {
$count_query = substr($count_query,0,-2).") as num ";
}
$count_query .= "\n FROM ".$GLOBALS['tablePrefix'].'categoryassociations a, '.$GLOBALS['tablePrefix'].'categories c,';
$count_query .= '('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid)';
$count_query .= ' WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
'.$workflowQry.'
AND q.visible = 1
AND
(
((q.startdate < \''.$GLOBALS['currDatetime']."') AND ((q.enableexpiry = 1) AND q.expirydate > '".$GLOBALS['currDatetime']."'))
OR
((q.startdate < '".$GLOBALS['currDatetime']."') AND (q.enableexpiry = 0))
)
AND (qa.groupid is null ".AKB_AUTH::deriveGroupSQL(false).") ";
//Inline help search option
if ((isInlineHelp()) && (isset($_GET['searchOverride']))) {
$query .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
} else {
if (!empty($this->searchCategories)) {
if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$query .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
} else {
$query .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
}
} else if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$query .= " AND c.categoryid IN (".implode(',', array_unique($allowed_cats)).")";
$count_query .= " AND c.categoryid IN (".implode(',', array_unique($allowed_cats)).")";
}
}
if (!empty($this->constraintFunctions)) {
foreach ($this->constraintFunctions as $f) {
$query .= $this->$f();
$count_query .= $this->$f();
}
}
if (!empty($this->searchString)) {
$query .= ' AND ('.$GLOBALS['AKB_DB']->FullText($fullTextFields, $this->searchString, $booleanmode);
$query .= " OR name like '%".$GLOBALS['AKB_DB']->Quote($this->searchString)."%' ";
$query .= " OR description like '%".$GLOBALS['AKB_DB']->Quote($this->searchString)."%' ";
$query .= " ) ";
}
$query .= $this->customFieldsSQLHelper($this->doCustomFields,$this->customFieldData);
$query .= "\n ) ";
if (!empty($this->searchString)) {
$count_query .= ' AND ('.$GLOBALS['AKB_DB']->FullText($fullTextFields, $this->searchString, $booleanmode);
$count_query .= " OR name like '%".$GLOBALS['AKB_DB']->Quote($this->searchString)."%' ";
$count_query .= " OR description like '%".$GLOBALS['AKB_DB']->Quote($this->searchString)."%' ";
$count_query .= " ) ";
}
$count_query .= $this->customFieldsSQLHelper($this->doCustomFields,$this->customFieldData);
if (validUrl()) {
/** Query for Question History Searching **/
$count_query .= "\n UNION ALL ";
$count_query .= 'SELECT COUNT(DISTINCT qh.questionid, ';
if (!empty($this->searchString) && $GLOBALS['databaseType'] == 'mysql') {
$count_query .= $GLOBALS['AKB_DB']->FullText($rependingFullTextFields, $this->searchString, $booleanmode).') as num';
} else {
$count_query = substr($count_query,0,-2).") as num ";
}
$count_query .= "\n FROM ".$GLOBALS['tablePrefix'].'catassoc_history a, '.$GLOBALS['tablePrefix'].'categories c, '.$GLOBALS['tablePrefix'].'categoryassociations ca, ';
$count_query .= '('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid)';
$count_query .= ', '.$GLOBALS['tablePrefix'].'questions_history qh ';
$count_query .= "\n WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
AND ca.categoryid = a.categoryid
AND ca.questionid = a.questionid
AND q.questionid = qh.questionid
AND (q.workflowstatus = '".WF_STATUS_REPENDING."' ".getShowDisapprovedArticleString('OR', 'q.').")
AND ( qh.workflowstatus = '".WF_STATUS_APPROVED."' OR qh.workflowstatus = '".WF_STATUS_AUTO_APPROVED."')
AND q.visible = 1
AND
(
((q.startdate < '".$GLOBALS['currDatetime']."') AND ((q.enableexpiry = 1) AND q.expirydate > '".$GLOBALS['currDatetime']."'))
OR
((q.startdate < '".$GLOBALS['currDatetime']."') AND (q.enableexpiry = 0))
)
AND (qa.groupid is null ".AKB_AUTH::deriveGroupSQL(false).") ";
// pre execute inner query
$innerQuery = 'SELECT MAX(qh.versionid) as versionid';
$innerQuery .= "\n FROM ".$GLOBALS['tablePrefix']."catassoc_history a, ".$GLOBALS['tablePrefix']."categories c, ".$GLOBALS['tablePrefix']."categoryassociations ca, ";
$innerQuery .= '('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid)';
$innerQuery .= ', '.$GLOBALS['tablePrefix'].'questions_history qh ';
$innerQuery .= "\n WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
AND ca.categoryid = a.categoryid
AND ca.questionid = a.questionid
AND qh.questionid = q.questionid
AND ( q.workflowstatus = '".WF_STATUS_REPENDING."' ".getShowDisapprovedArticleString('OR', 'q.').")
AND ( qh.workflowstatus = '".WF_STATUS_APPROVED."' OR qh.workflowstatus = '".WF_STATUS_AUTO_APPROVED."' )
AND q.visible = 1
AND
(
((q.startdate < '".$GLOBALS['currDatetime']."') AND ((q.enableexpiry = 1) AND q.expirydate > '".$GLOBALS['currDatetime']."'))
OR
((q.startdate < '".$GLOBALS['currDatetime']."') AND (q.enableexpiry = 0))
)
AND (qa.groupid is null ".AKB_AUTH::deriveGroupSQL(false).") ";
//Inline help search option
if ((isInlineHelp()) && (isset($_GET['searchOverride']))) {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
} else {
if (!empty($this->searchCategories)) {
if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
} else {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
}
} else if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$innerQuery .= " AND c.categoryid IN (".implode(',', array_unique($allowed_cats)).")";
$count_query .= " AND c.categoryid IN (".implode(',', array_unique($allowed_cats)).")";
}
}
if (!empty($this->searchString)) {
$innerQuery .= ' AND ('.$GLOBALS['AKB_DB']->FullText($rependingFullTextFields, $this->searchString, $booleanmode) . ' ) ';
}
if (!empty($this->searchString)) {
$count_query .= ' AND ('.$GLOBALS['AKB_DB']->FullText($rependingFullTextFields, $this->searchString, $booleanmode) . ' ) ';
}
$innerQuery .= ' GROUP BY qh.questionid ';
$versionIds = array();
$innerResults = $GLOBALS['AKB_DB']->Query($innerQuery);
while ($innerRow = $GLOBALS['AKB_DB']->Fetch($innerResults)) {
$versionIds[] = $innerRow['versionid'];
}
$versionIdsString = "''";
if (sizeof($versionIds)) {
$versionIdsString = " '".implode("','", $versionIds)."' ";
}
$query .= "\n UNION ALL ";
$query .= 'SELECT questionid, title, '.$GLOBALS['AKB_DB']->SubString('answer', 1, 201).' as answer , lastupdated, workflowstatus, \'articles\' as types, \'Question\' as plugin ,\'_\' as href ';
if (!empty($this->searchString) && $GLOBALS['databaseType'] == 'mysql') {
$query .= ", ".$GLOBALS['AKB_DB']->FullText($fullTextFields, $this->searchString, $booleanmode).' as score';
}
$query .= "\n FROM ".$GLOBALS['tablePrefix']."questions_history ";
$query .= "\n WHERE versionid IN ";
$query .= "\n ( ";
$query .= $versionIdsString;
$query .= ' ) ';
// End of Query for Question History Searching
}
// run this query only if the attachment searching is enabled
if (isset($GLOBALS['enableAttachmentSearch']) && $GLOBALS['enableAttachmentSearch'] == true && !empty($this->searchString)) {
$workflowQry = '';
if (validUrl()) {
$workflowQry = " AND ( q.workflowstatus = '".WF_STATUS_APPROVED."' OR q.workflowstatus = '".WF_STATUS_AUTO_APPROVED."' )";
}
/** Query for Attachment Searching **/
$attachmentFullTextFields = array ('content', 'filename');
$count_query .= "\n UNION ALL ";
$count_query .= 'SELECT COUNT(DISTINCT p.pageindexid, ';
if (!empty($this->searchString) && $GLOBALS['databaseType'] == 'mysql') {
$count_query .= $GLOBALS['AKB_DB']->FullText($attachmentFullTextFields, $this->searchString, $booleanmode).') as num';
} else {
$count_query = substr($count_query,0,-2).") as num ";
}
$count_query .= "\n FROM ".$GLOBALS['tablePrefix'].'categoryassociations a, '.$GLOBALS['tablePrefix'].'categories c,';
$count_query .= '('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid)';
$count_query .= ', '.$GLOBALS['tablePrefix'].'pageindex p ';
$count_query .= "\n WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
AND q.questionid = p.questionid
".$workflowQry."
AND q.visible = 1
AND
(
((q.startdate < '".$GLOBALS['currDatetime']."') AND ((q.enableexpiry = 1) AND q.expirydate > '".$GLOBALS['currDatetime']."'))
OR
((q.startdate < '".$GLOBALS['currDatetime']."') AND (q.enableexpiry = 0))
)
AND (qa.groupid is null ".AKB_AUTH::deriveGroupSQL(false).") ";
// Pre-get the page index id
$innerQuery = 'SELECT DISTINCT p.pageindexid';
$innerQuery .= "\n FROM ".$GLOBALS['tablePrefix']."categoryassociations a, ".$GLOBALS['tablePrefix']."categories c, ";
$innerQuery .= '('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid)';
$innerQuery .= ', '.$GLOBALS['tablePrefix'].'pageindex p';
$innerQuery .= ' WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
AND p.questionid = q.questionid
'.$workflowQry.'
AND q.visible = 1
AND
(
((q.startdate < \''.$GLOBALS['currDatetime']."') AND ((q.enableexpiry = 1) AND q.expirydate > '".$GLOBALS['currDatetime']."'))
OR
((q.startdate < '".$GLOBALS['currDatetime']."') AND (q.enableexpiry = 0))
)
AND (qa.groupid is null ".AKB_AUTH::deriveGroupSQL(false).") ";
//Inline help search option
if ((isInlineHelp()) && (isset($_GET['searchOverride']))) {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
} else {
if (!empty($this->searchCategories)) {
if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
} else {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
}
} else if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$innerQuery .= " AND c.categoryid IN ('".implode("', '", array_unique($allowed_cats))."')";
$count_query .= " AND c.categoryid IN ('".implode("', '", array_unique($allowed_cats))."')";
}
}
if (!empty($this->searchString)) {
$innerQuery .= ' AND ('.$GLOBALS['AKB_DB']->FullText($attachmentFullTextFields, $this->searchString, $booleanmode) . ' ) ';
}
if (!empty($this->searchString)) {
$count_query .= ' AND ('.$GLOBALS['AKB_DB']->FullText($attachmentFullTextFields, $this->searchString, $booleanmode) . ' ) ';
}
$pageIndexIds = array();
$innerResults = $GLOBALS['AKB_DB']->Query($innerQuery);
while ($innerRow = $GLOBALS['AKB_DB']->Fetch($innerResults)) {
$pageIndexIds[] = $innerRow['pageindexid'];
}
$pageIndexIdsString = "''";
if (sizeof($pageIndexIds)) {
$pageIndexIdsString = " '".implode("','", $pageIndexIds)."' ";
}
$query .= "\n UNION ALL ";
$query .= 'SELECT questionid, filename as title, '.$GLOBALS['AKB_DB']->SubString('content', 1, 201).' as answer, lastupdated, \''.WF_STATUS_AUTO_APPROVED.'\' as workflowstatus, \'attachments\' as types, plugin, fullurl as href ';
if (!empty($this->searchString) && $GLOBALS['databaseType'] == 'mysql') {
$query .= ", ".$GLOBALS['AKB_DB']->FullText($attachmentFullTextFields, $this->searchString, $booleanmode).' as score';
}
$query .= "\n FROM ".$GLOBALS['tablePrefix']."pageindex ";
$query .= "\n WHERE pageindexid IN ";
$query .= "\n ( ";
$query .= $pageIndexIdsString;
$query .= " ) ";
// End of Query for Attachment Searching
}
$query .= "\n ORDER BY $orderBy DESC ";
$num_results = 0;
$numResult = $GLOBALS['AKB_DB']->Query($count_query);
while ($numRow = $GLOBALS['AKB_DB']->Fetch($numResult)) {
$num_results += $numRow['num'];
}
// If there are no results there isn't any point doing the big query
if ($num_results == 0) {
return false;
}
$query .= $GLOBALS['AKB_DB']->AddLimit($this->start, $this->maxResults);
$result = $GLOBALS['AKB_DB']->Query($query);
if ($GLOBALS['AKB_DB']->CountResult($query) > 0) {
// Load the results into the search results variable
while ($row = $GLOBALS['AKB_DB']->Fetch($result)) {
$this->searchResults[] = $row;
}
for ($i = count($this->searchResults); $i < $num_results; $i++) {
$this->searchResults[$i] = null;
}
} else {
return false;
}
}
// }}}
// {{{ questKeywordSearch()
/**
* questKeywordSearch
* Perform a keyword search
*
* @return bool False if no matches are found
*/
function questKeywordSearch()
{
$orderBy = ' lastupdated ';
$this->searchString = trim($this->searchString);
// We can't search without something to search for
if (empty($this->searchString)) {
return;
}
$allowed_cats = array();
if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$allowed_cats = $GLOBALS['AKB_CLASS_HELPER']->GetPermittedCats();
}
$like_query = $this->buildLikeQuery();
$attachment_like_query = $this->buildAttachmentLikeQuery();
//Is this inline help searching?
if ((isInlineHelp()) && (isset($_GET['searchOverride']))) {
$_carray = $GLOBALS['AKB_CLASS_HELPER']->tree->GetBranchFrom((int)$_GET['searchOverride'],false);
$_carray[] = (int)$_GET['searchOverride'];
}
$workflowQry = '';
if (validUrl()) {
$workflowQry = ' AND ( q.workflowstatus = \''.WF_STATUS_APPROVED.'\' OR q.workflowstatus = \''.WF_STATUS_AUTO_APPROVED.'\' ) ';
}
$count_query = 'SELECT count(distinct q.questionid) as num
FROM '.$GLOBALS['tablePrefix'].'categoryassociations a, '.$GLOBALS['tablePrefix'].'categories c,
('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid)
WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
'.$workflowQry.'
AND visible = 1
AND
(
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND ((q.enableexpiry = 1) AND q.expirydate > \''.$GLOBALS['currDatetime'].'\'))
OR
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND (q.enableexpiry = 0))
)
AND ('.$like_query.')
AND (qa.groupid is null '.AKB_AUTH::deriveGroupSQL().')
'.$this->customFieldsSQLHelper($this->doCustomFields,$this->customFieldData);
$query = 'SELECT questionid, title, '.$GLOBALS['AKB_DB']->SubString('answer', 1, 201).' as answer, lastupdated, workflowstatus, \'articles\' as types, \'Question\' as plugin ,\'_\' as href ';
$query .= 'FROM '.$GLOBALS['tablePrefix'].'questions q
WHERE q.questionid IN
(
SELECT q.questionid
FROM '.$GLOBALS['tablePrefix'].'categoryassociations a, '.$GLOBALS['tablePrefix'].'categories c,
('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid)
WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
'.$workflowQry.'
AND visible = 1
AND
(
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND ((q.enableexpiry = 1) AND q.expirydate > \''.$GLOBALS['currDatetime'].'\'))
OR
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND (q.enableexpiry = 0))
)
AND ('.$like_query.')
AND (qa.groupid is null '.AKB_AUTH::deriveGroupSQL().')
'.$this->customFieldsSQLHelper($this->doCustomFields,$this->customFieldData);
//Inline help search option
if ((isInlineHelp()) && (isset($_GET['searchOverride']))) {
$query .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
} else {
if (!empty($this->searchCategories)) {
if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$query .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
} else {
$query .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
}
} else if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$query .= "AND a.categoryid IN (".implode(',', array_unique($allowed_cats)).")";
$count_query .= "AND a.categoryid IN (".implode(',', array_unique($allowed_cats)).")";
}
}
if (!empty($this->constraintFunctions)) {
foreach ($this->constraintFunctions as $f) {
$query .= $this->$f();
$count_query .= $this->$f();
}
}
$query .= ')';
// *******
// Seaching in QuestionsHistory
// *******
if (validUrl()) {
$count_query .= "\n UNION ALL ";
$count_query .= 'SELECT COUNT(DISTINCT qh.questionid) as num ';
$count_query .= 'FROM '.$GLOBALS['tablePrefix'].'catassoc_history a, '.$GLOBALS['tablePrefix'].'categories c, '.$GLOBALS['tablePrefix'].'categoryassociations ca,
('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid)
, '.$GLOBALS['tablePrefix'].'questions_history qh
WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
AND ca.categoryid = a.categoryid
AND ca.questionid = a.questionid
AND q.questionid = qh.questionid
AND q.visible = 1
AND ( q.workflowstatus = \''.WF_STATUS_REPENDING.'\' '.getShowDisapprovedArticleString('OR', 'q.').')
AND ( qh.workflowstatus = \''.WF_STATUS_APPROVED.'\' OR qh.workflowstatus = \''.WF_STATUS_AUTO_APPROVED.'\' )
AND
(
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND ((q.enableexpiry = 1) AND q.expirydate > \''.$GLOBALS['currDatetime'].'\'))
OR
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND (q.enableexpiry = 0))
)
AND ('.str_replace(' q.', ' qh.', $like_query).')
AND (qa.groupid is null '.AKB_AUTH::deriveGroupSQL().')
'.$this->customFieldsSQLHelper($this->doCustomFields,$this->customFieldData);
$innerQuery = '
SELECT MAX(qh.versionid) as versionid
FROM '.$GLOBALS['tablePrefix'].'catassoc_history a, '.$GLOBALS['tablePrefix'].'categories c, '.$GLOBALS['tablePrefix'].'categoryassociations ca,
('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid) , '.$GLOBALS['tablePrefix'].'questions_history qh
WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
AND ca.categoryid = a.categoryid
AND ca.questionid = a.questionid
AND q.questionid = qh.questionid
AND ( q.workflowstatus = \''.WF_STATUS_REPENDING.'\' '.getShowDisapprovedArticleString('OR', 'q.').')
AND (qh.workflowstatus = \''.WF_STATUS_APPROVED.'\' OR qh.workflowstatus = \''.WF_STATUS_AUTO_APPROVED.'\' )
AND q.visible = 1
AND
(
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND ((q.enableexpiry = 1) AND q.expirydate > \''.$GLOBALS['currDatetime'].'\'))
OR
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND (q.enableexpiry = 0))
)
AND ('.str_replace(' q.', ' qh.', $like_query).')
AND (qa.groupid is null '.AKB_AUTH::deriveGroupSQL().')
';
//Inline help search option
if ((isInlineHelp()) && (isset($_GET['searchOverride']))) {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
} else {
if (!empty($this->searchCategories)) {
if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
} else {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
}
} else if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$innerQuery .= "AND a.categoryid IN (".implode(',', array_unique($allowed_cats)).")";
$count_query .= "AND a.categoryid IN (".implode(',', array_unique($allowed_cats)).")";
}
}
$innerQuery .= ' GROUP BY qh.questionid ';
$versionIds = array();
$innerResults = $GLOBALS['AKB_DB']->Query($innerQuery);
while ($innerRow = $GLOBALS['AKB_DB']->Fetch($innerResults)) {
$versionIds[] = $innerRow['versionid'];
}
$versionIdsString = "''";
if (sizeof($versionIds)) {
$versionIdsString = " '".implode("','", $versionIds)."' ";
}
$query .= "\n UNION ALL ";
$query .= 'SELECT questionid, title, '.$GLOBALS['AKB_DB']->SubString('answer', 1, 201).' as answer , lastupdated, workflowstatus, \'articles\' as types, \'Question\' as plugin ,\'_\' as href ';
$query .= "\n FROM ".$GLOBALS['tablePrefix']."questions_history ";
$query .= "\n WHERE versionid IN ( ";
$query .= $versionIdsString;
$query .= ')';
}
// run this query only if the attachment searching is enabled
if (isset($GLOBALS['enableAttachmentSearch']) && $GLOBALS['enableAttachmentSearch'] == true && !empty($this->searchString)) {
$count_query .= "\n UNION ALL ";
$count_query .= 'SELECT COUNT(DISTINCT p.pageindexid) as num ';
$count_query .= 'FROM '.$GLOBALS['tablePrefix'].'categoryassociations a, '.$GLOBALS['tablePrefix'].'categories c,
('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid)
, '.$GLOBALS['tablePrefix'].'pageindex p
WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
AND q.questionid = p.questionid
AND visible = 1
'.$workflowQry.'
AND
(
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND ((q.enableexpiry = 1) AND q.expirydate > \''.$GLOBALS['currDatetime'].'\'))
OR
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND (q.enableexpiry = 0))
)
AND ('.$attachment_like_query.')
AND (qa.groupid is null '.AKB_AUTH::deriveGroupSQL().')
';
$innerQuery = '
SELECT DISTINCT p.pageindexid
FROM '.$GLOBALS['tablePrefix'].'categoryassociations a, '.$GLOBALS['tablePrefix'].'categories c,
('.$GLOBALS['tablePrefix'].'question_auth qa RIGHT JOIN '.$GLOBALS['tablePrefix'].'questions q ON qa.questionid = q.questionid) , '.$GLOBALS['tablePrefix'].'pageindex p
WHERE q.questionid = a.questionid
AND c.categoryid = a.categoryid
AND q.questionid = p.questionid
'.$workflowQry.'
AND visible = 1
AND
(
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND ((q.enableexpiry = 1) AND q.expirydate > \''.$GLOBALS['currDatetime'].'\'))
OR
((q.startdate < \''.$GLOBALS['currDatetime'].'\') AND (q.enableexpiry = 0))
)
AND ('.$attachment_like_query.')
AND (qa.groupid is null '.AKB_AUTH::deriveGroupSQL().')
';
//Inline help search option
if ((isInlineHelp()) && (isset($_GET['searchOverride']))) {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", $_carray)."') \n";
} else {
if (!empty($this->searchCategories)) {
if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique(array_intersect($allowed_cats, $this->searchCategories)))."') \n";
} else {
$innerQuery .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
$count_query .= " AND a.categoryid IN ('".implode("', '", array_unique($this->searchCategories))."') \n";
}
} else if (isset($GLOBALS['categoryRestrictOption']) && $GLOBALS['categoryRestrictOption'] == "1") {
$innerQuery .= "AND a.categoryid IN (".implode(',', array_unique($allowed_cats)).")";
$count_query .= "AND a.categoryid IN (".implode(',', array_unique($allowed_cats)).")";
}
}
$pageIndexIds = array();
$innerResults = $GLOBALS['AKB_DB']->Query($innerQuery);
while ($innerRow = $GLOBALS['AKB_DB']->Fetch($innerResults)) {
$pageIndexIds[] = $innerRow['pageindexid'];
}
$pageIndexIdsString = "''";
if (sizeof($pageIndexIds)) {
$pageIndexIdsString = " '".implode("','", $pageIndexIds)."' ";
}
$query .= "\n UNION ALL ";
$query .= 'SELECT questionid, filename as title, '.$GLOBALS['AKB_DB']->SubString('content', 1, 201).' as answer, lastupdated, \''.WF_STATUS_AUTO_APPROVED.'\' as workflowstatus, \'attachments\' as types, plugin, fullurl as href';
$query .= "\n FROM ".$GLOBALS['tablePrefix']."pageindex ";
$query .= "\n WHERE pageindexid IN ( ";
$query .= $pageIndexIdsString;
$query .= ')';
}
$query .= "\n ORDER BY $orderBy DESC ";
// Clean up our query before we use it
// sprintf doesnt like newlines and tabs in our sql
$numResult = $GLOBALS['AKB_DB']->Query($count_query);
$num_results = 0;
$numRow = array();
while ($numRow = $GLOBALS['AKB_DB']->Fetch($numResult)) {
$num_results += $numRow['num'];
}
// If there are no results there isn't any point doing the big query
if ($num_results == 0) {
return false;
}
// Limit our query
$query .= $GLOBALS['AKB_DB']->AddLimit($this->start, $this->maxResults);
$result = $GLOBALS['AKB_DB']->Query($query);
if ($GLOBALS['AKB_DB']->CountResult($query) > 0) {
// Load the results into the search results variable
while ($row = $GLOBALS['AKB_DB']->Fetch($result)) {
$this->searchResults[] = $row;
}
for ($i = count($this->searchResults); $i < $num_results; $i++) {
$this->searchResults[$i] = null;
}
} else {
return false;
}
}
// }}}
// {{{ questSearch()
/**
* questSearch
* Perform a search
*
* @param boolean $fallback Do we fall back to the other type of
* searching if the first type provides no results ?
*
* @return void
*/
function questSearch($fallback=false)
{
//Check if this is a valid article ID. If so redirect directly.
if ((int)$this->searchString > 0) {
$qapi = new API_QUESTION();
if ($qapi->load((int)$this->searchString)) {
header("location: ".GetUrl("question",(int)$this->searchString));
die();
}
}
$this->stripIgnoreWords();
switch ($this->searchType) {
case 1:
$this->questFullTextSearch();
if ($fallback && empty($this->searchResults)) {
$this->questKeywordSearch();
}
break;
case 2:
$this->questKeywordSearch();
if ($fallback && empty($this->searchResults)) {
$this->questFullTextSearch();
}
break;
case 3:
$this->questFullTextSearch(true);
if ($fallback && empty($this->searchResults)) {
$this->questKeywordSearch();
}
break;
default:
$this->questFullTextSearch();
if ($fallback && empty($this->searchResults)) {
$this->questKeywordSearch();
}
}
}
/**
* stripIgnoreWords
* Remove the ignore words from the search string
*
* @return bool true
*/
function stripIgnoreWords()
{
if (is_array($this->ignoreWords)) {
foreach ($this->ignoreWords as $eachIgnoreWord) {
$this->searchString = preg_replace('/\b'.trim($eachIgnoreWord) . '\b/i', '', $this->searchString);
}
} else {
$this->searchString = preg_replace('/\b'.trim($this->ignoreWords).'\b/i', '', $this->searchString);
}
return true;
}
// }}}
function buildLikeQuery()
{
$tmpWords = trim(str_replace('"', '', $this->searchString));
$arrWords = preg_split('%\s+%', $tmpWords);
$output = array();
foreach ($arrWords as $word) {
$word = trim($word);
if (empty($word)) {
continue;
}
$template = " q.title LIKE '%".$GLOBALS['AKB_DB']->Quote($word)."%'
OR q.answer LIKE '%".$GLOBALS['AKB_DB']->Quote($word)."%'
OR q.metakeywords LIKE '%".$GLOBALS['AKB_DB']->Quote($word)."%' ";
$output[] = $template;
}
return implode(' OR ', $output);
}
function buildAttachmentLikeQuery()
{
$tmpWords = trim(str_replace('"', '', $this->searchString));
$arrWords = preg_split('%\s+%', $tmpWords);
$output = array();
foreach ($arrWords as $word) {
$word = trim($word);
if (empty($word)) {
continue;
}
$template = " content LIKE '%".$GLOBALS['AKB_DB']->Quote($word)."%'
OR filename LIKE '%".$GLOBALS['AKB_DB']->Quote($word)."%'";
$output[] = $template;
}
return implode(' OR ', $output);
}
/**
* customFieldsSQLHelper
*
* Called from SQL with a condition and a type in order to get back either a blank string or a specific part of
* SQL for called custom fields via the search.
*
* Data array includes customfieldid as key and then an array of data beneath it. This is because one question could
* potentially have multiple custom fields attached.
*
* @return string
*/
function customFieldsSQLHelper($condition,$cfd_array) {
if ($condition) {
$unionStr = " UNION ALL ";
$buildString = "";
if ((is_array($cfd_array)) && (count($cfd_array) > 0)) {
$buildString = "
SELECT questionid FROM
(
";
$count = 0;
foreach ($cfd_array as $customfieldid=>$data_array) {
if (is_array($data_array)) {
foreach ($data_array as $data_string) {
if ($data_string != "") {
$buildString .= "SELECT questionid FROM ".$GLOBALS['tablePrefix']."questions_customfields qcf WHERE qcf.data = '".$GLOBALS['AKB_DB']->Quote($data_string)."' AND qcf.customfieldid = '".(int)$customfieldid."'".$unionStr;
$count++;
}
}
} else if ((gettype($data_array) == "string") && ($data_array != "")) {
if ($buildString != "") {
$buildString .= "SELECT DISTINCT questionid FROM ".$GLOBALS['tablePrefix']."questions_customfields qcf WHERE qcf.data = '".$GLOBALS['AKB_DB']->Quote($data_array)."' AND qcf.customfieldid = '".(int)$customfieldid."'".$unionStr;
$count++;
}
}
}
if (strlen($buildString) > 0) {
$buildString = "AND q.questionid IN (".substr($buildString,0,-strlen($unionStr))." ) x )";
}
}
return $buildString;
}
}
}
?>
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
