今天，市场部的同事反映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();
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()
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()) {
$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."' )";
}
$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()
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()
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();
}
}
}
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);
}
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;
}
}
}
?>
![数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)