From db45924cad61568b93a8a0a3b1ce13d7507d8e5d Mon Sep 17 00:00:00 2001 From: Robert Sesek Date: Fri, 18 Feb 2005 03:34:21 +0000 Subject: [PATCH] r57: Simplified query build. Searching now works. Added highlighting keywords. NB: we don't do wild card hilights (ie: test* will highlight test, testing, tested, etc.). --- search.php | 106 ++++++++++++++++++++----- showreport.php | 43 +++++++++- templates/default/search.tpl | 8 +- templates/default/search_results.tpl | 13 +++ templates/default/trackerhome_bits.tpl | 2 +- 5 files changed, 148 insertions(+), 24 deletions(-) create mode 100644 templates/default/search_results.tpl diff --git a/search.php b/search.php index f50ef75..5437d2e 100644 --- a/search.php +++ b/search.php @@ -51,7 +51,9 @@ if ($_REQUEST['do'] == 'results') 'status' => INT, // done 'resolution' => INT, // done 'assignedto' => INT, // done - 'date' => INT) // done + 'date' => INT, // done + 'sortby' => STR, + 'orderby' => STR) ); // ------------------------------------------------------------------- @@ -64,6 +66,8 @@ if ($_REQUEST['do'] == 'results') { $keywords = preg_split('#\s+#', $vars['summary']); + // #*# need to have some str to bool conversions + foreach ($keywords AS $word) { if (strlen($word) < SEARCH_WORD_MIN) @@ -73,25 +77,31 @@ if ($_REQUEST['do'] == 'results') if (MODE_ALL) { - $querybuild['where']['text'] .= " +$word"; + $querybuild['text'] .= " +$word"; } else { - $querybuild['where']['text'] .= " $word"; + $querybuild['text'] .= " $word"; + } + + if (!preg_match('#-(.+?)#', trim($word))) + { + $hilight .= " $word"; } } - $temp = trim($querybuild['where']['text']); + $hilight = preg_replace('#[^0-9a-zA-Z_ ]#', '', $hilight); + $hilight = trim($hilight); + $hilight = preg_replace('#\s#', '+', $hilight); + + $temp = trim($querybuild['text']); if (MODE_ALL OR MODE_RAW) { - // do some operations for those who use str boolean operators - $querybuild['where']['text'] = "AND MATCH (bug.summary, comment.comment) AGAINST ('$temp' IN BOOLEAN MODE)"; - } - else - { - $querybuild['where']['text'] = "AND MATCH (bug.summary, comment.comment) AGAINST ('$temp')"; + $bool_flag = ' IN BOOLEAN MODE'; } + + $querybuild['text'] = "AND\n\t\t\t(\n\t\t\t\tMATCH (bug.summary) AGAINST ('$temp'$bool_flag)\n\t\t\t\tOR MATCH (comment.comment) AGAINST ('$temp'$bool_flag)\n\t\t\t)"; } // ------------------------------------------------------------------- @@ -103,7 +113,7 @@ if ($_REQUEST['do'] == 'results') $user = $DB_sql->query_first("SELECT * FROM user WHERE email = '$vars[reporter]' OR MATCH (displayname) AGAINST ('$vars[reporter]')"); if ($user['userid']) { - $querybuild['where']['reporter'] = "AND bug.userid = $user[userid] OR comment.userid = $user[userid]"; + $querybuild['reporter'] = "AND bug.userid = $user[userid] OR comment.userid = $user[userid]"; } } @@ -111,7 +121,7 @@ if ($_REQUEST['do'] == 'results') // product/component/version stuff if (is_array($vars['pcv'])) { - $querybuild['where']['pcv'] = "AND bug.productid = {$vars['pcv']['product']} AND bug.componentid = {$vars['pcv']['component']} AND bug.versionid = {$vars['pcv']['version']}"; + $querybuild['pcv'] = "AND bug.productid = {$vars['pcv']['product']} AND bug.componentid = {$vars['pcv']['component']} AND bug.versionid = {$vars['pcv']['version']}"; } // ------------------------------------------------------------------- @@ -120,31 +130,31 @@ if ($_REQUEST['do'] == 'results') // severity if ($vars['severity']) { - $querybuild['where']['severity'] = "AND bug.severity = $vars[severity]"; + $querybuild['severity'] = "AND bug.severity = $vars[severity]"; } // priority if ($vars['priority']) { - $querybuild['where']['priority'] = "AND bug.priority = $vars[priority]"; + $querybuild['priority'] = "AND bug.priority = $vars[priority]"; } // status if ($vars['status']) { - $querybuild['where']['status'] = "AND bug.status = $vars[status]"; + $querybuild['status'] = "AND bug.status = $vars[status]"; } // resolution if ($vars['resolution']) { - $querybuild['where']['resolution'] = "AND bug.resolution = $vars[resolution]"; + $querybuild['resolution'] = "AND bug.resolution = $vars[resolution]"; } // assignment if ($vars['assignedto']) { - $querybuild['where']['assignedto'] = "AND bug.assignedto = $vars[assignedto]"; + $querybuild['assignedto'] = "AND bug.assignedto = $vars[assignedto]"; } // ------------------------------------------------------------------- @@ -153,10 +163,68 @@ if ($_REQUEST['do'] == 'results') { // now - (seconds/day * number of days) $dateline = time() - ($vars['date'] * 3600); - $querybuild['where']['date'] = "AND bug.dateline >= $dateline"; + $querybuild['date'] = "AND bug.dateline >= $dateline"; + } + + // ------------------------------------------------------------------- + // sort by + $sortby = array('bugid', 'severity', 'priority', 'status', 'resolution', 'dateline'); + $orderby = array('ASC', 'DESC'); + $vars['orderby'] = strtoupper($vars['orderby']); + if (in_array($vars['sortby'], $sortby) AND in_array($vars['orderby'], $orderby)) + { + $sortclause = "ORDER BY $vars[sortby] $vars[orderby]"; + } + else if ($vars['sortby'] == 'relevance') + { + $sortclause = ''; } + else + { + echo 'bad sort'; + exit; + } + + // ------------------------------------------------------------------- + // do the search + $search = $DB_sql->query(" + SELECT bug.*, comment.commentid, + user1.displayname AS firstreport, + user2.displayname AS lastpost + FROM " . TABLE_PREFIX . "bug AS bug + LEFT JOIN " . TABLE_PREFIX . "comment AS comment + ON (bug.bugid = comment.bugid) + LEFT JOIN user AS user1 + ON (bug.userid = user1.userid) + LEFT JOIN user AS user2 + ON (bug.lastpostby = user2.userid) + WHERE bug.bugid <> 0 + " . implode("\n\t\t\t", $querybuild) . " + $sortclause" + ); + + $numrows = $DB_sql->num_rows($search); + + if ($numrows < 1) + { + echo 'no results found'; + exit; + } + + while ($bug = $DB_sql->fetch_array($search)) + { + $bug['product'] = $bugsys->datastore['product']["$bug[productid]"]['title']; + $bug['version'] = $bugsys->datastore['version']["$bug[versionid]"]['version']; + $bug['status'] = $bugsys->datastore['status']["$bug[status]"]['status']; + $bug['resolution'] = $bugsys->datastore['resolution']["$bug[resolution]"]['resolution']; + $bug['lastpostinfo'] = datelike('standard', $bug['lastposttime']) . ' by ' . $bug['lastpost']; + $bug['urladd'] = "&hilight=$hilight"; + eval('$bugs .= "' . $tpl->fetch('trackerhome_bits') . '";'); + } + + eval('$tpl->flush("' . $tpl->fetch('search_results') . '");'); - print_r($querybuild); +// print_r($querybuild); } // ################################################################### diff --git a/showreport.php b/showreport.php index 4d6c680..3ddfdca 100644 --- a/showreport.php +++ b/showreport.php @@ -25,7 +25,9 @@ if (!can_perform('canviewbugs')) // ################################################################### -sanitize(array('bugid' => INT)); +// ------------------------------------------------------------------- +// get the report +sanitize(array('bugid' => INT, 'hilight' => STR)); $bug = $DB_sql->query_first(" SELECT bug.*, user.displayname, user.email, user.showemail @@ -41,6 +43,8 @@ if (!is_array($bug)) exit; } +// ------------------------------------------------------------------- +// prep display $bug['userinfo'] = construct_user_display($bug); $bug['product'] = $bugsys->datastore['product']["$bug[productid]"]['title']; $bug['component'] = iff($bug['componentid'], $bugsys->datastore['product']["$bug[componentid]"]['title']); @@ -62,6 +66,22 @@ else $show['editreport'] = false; } +// ------------------------------------------------------------------- +// hilight +$words = explode(' ', $vars['hilight']); +foreach ($words AS $word) +{ + if (trim($word)) + { + $word = preg_quote($word); + $hilight[] = $temp = trim(preg_replace('#[^0-9a-zA-Z_ ]#', '', $word)); + } +} +print_r($words); +print_r($hilight); + +// ------------------------------------------------------------------- +// get comments $comments_fetch = $DB_sql->query(" SELECT comment.*, user.email, user.showemail, user.displayname FROM " . TABLE_PREFIX . "comment AS comment @@ -82,6 +102,17 @@ while ($comment = $DB_sql->fetch_array($comments_fetch)) { $show['editcomment'] = false; } + + if (is_array($hilight)) + { + foreach ($hilight AS $id => $find) + { + $find = "#($find)#"; + $replace = "\\1"; + $comment['comment_parsed'] = preg_replace($find, $replace, $comment['comment_parsed']); + } + } + eval('$comments .= "' . $tpl->fetch('showreport_comment') . '";'); } @@ -94,6 +125,16 @@ else $show['newreply'] = false; } +if (is_array($hilight)) +{ + foreach ($hilight AS $id => $find) + { + $find = "#($find)#"; + $replace = "\\1"; + $bug['summary'] = preg_replace($find, $replace, $bug['summary']); + } +} + eval('$tpl->flush("' . $tpl->fetch('SHOWREPORT') . '");'); /*=====================================================================*\ diff --git a/templates/default/search.tpl b/templates/default/search.tpl index da59f59..0ca12d9 100644 --- a/templates/default/search.tpl +++ b/templates/default/search.tpl @@ -21,7 +21,7 @@
Date (submitted since): - + - + + Ascending + Descending
diff --git a/templates/default/search_results.tpl b/templates/default/search_results.tpl new file mode 100644 index 0000000..2c63918 --- /dev/null +++ b/templates/default/search_results.tpl @@ -0,0 +1,13 @@ +Here are the search results: + + + + + + + + + + +$bugs +
Bug IDTitleReporterProduct/VersionStatus/ResolutionLast Post
\ No newline at end of file diff --git a/templates/default/trackerhome_bits.tpl b/templates/default/trackerhome_bits.tpl index 7e121cc..844cfd6 100644 --- a/templates/default/trackerhome_bits.tpl +++ b/templates/default/trackerhome_bits.tpl @@ -1,6 +1,6 @@ $bug[bugid] - $bug[summary] + $bug[summary] $bug[firstreport] $bug[product] / $bug[version] $bug[status]/$bug[resolution] -- 2.22.5