From d63ede652cde27bc2ee9c8dcb2259c783c80f75a Mon Sep 17 00:00:00 2001 From: Banana Date: Wed, 4 Jan 2017 15:25:58 +0100 Subject: [PATCH] search. --- webroot/lib/management.class.php | 48 ++++++++++++++++++++++++++++++-- webroot/lib/summoner.class.php | 2 +- webroot/view/home.inc.php | 36 ++++++++++++++++++++---- webroot/view/overview.inc.php | 2 +- 4 files changed, 78 insertions(+), 10 deletions(-) diff --git a/webroot/lib/management.class.php b/webroot/lib/management.class.php index d1a45e3..c7c148e 100644 --- a/webroot/lib/management.class.php +++ b/webroot/lib/management.class.php @@ -81,7 +81,7 @@ class Management { * return the latest addded links * @param number $limit */ - public function latest($limit=5) { + public function latestLinks($limit=5) { $ret = array(); $queryStr = "SELECT * FROM `".DB_PREFIX."_link` WHERE `status` = 2 ORDER BY `created` DESC"; @@ -173,7 +173,11 @@ class Management { return $ret; } - public function all($limit=false) { + /** + * return all links and Info we have from the combined view + * @param int $limit + */ + public function links($limit=false) { $ret = array(); $queryStr = "SELECT * FROM `".DB_PREFIX."_combined` @@ -187,6 +191,46 @@ class Management { return $ret; } + + /** + * for simpler management we have the search data in a seperate column + * it is not fancy or even technical nice but it damn works + */ + private function _updateSearchIndex() { + $allLinks = array(); + $queryStr = "SELECT hash FROM `".DB_PREFIX."_link`"; + $query = $this->DB->query($queryStr); + if(!empty($query) && $query->num_rows > 0) { + $allLinks = $query->fetch_all(MYSQLI_ASSOC); + } + + if(!empty($allLinks)) { + foreach($allLinks as $link) { + $LinkObj = new Link($this->DB); + $l = $LinkObj->load($link['hash']); + + $searchStr = $l['title']; + $searchStr .= ' '.$l['description']; + foreach($l['tags'] as $t) { + $searchStr .= ' '.$t['tag']; + } + foreach($l['categories'] as $c) { + $searchStr .= ' '.$c['category']; + } + + # now update the search string + $queryStr = "UPDATE `".DB_PREFIX."_link` + SET `search` = '".$this->DB->real_escape_string($searchStr)."' + WHERE `hash` = '".$this->DB->real_escape_string($link['hash'])."'"; + + $this->DB->query($queryStr); + + unset($LinkObj,$l,$searchStr,$t,$c,$queryStr); + } + } + + + } } ?> \ No newline at end of file diff --git a/webroot/lib/summoner.class.php b/webroot/lib/summoner.class.php index 010d92f..de03bd6 100644 --- a/webroot/lib/summoner.class.php +++ b/webroot/lib/summoner.class.php @@ -339,7 +339,7 @@ class Summoner { * * @param string $string */ - static function prepareTagOrCategorieStr($string) { + static function prepareTagOrCategoryStr($string) { $ret = array(); $string = trim($string, ", "); diff --git a/webroot/view/home.inc.php b/webroot/view/home.inc.php index 5823dad..b5f1456 100644 --- a/webroot/view/home.inc.php +++ b/webroot/view/home.inc.php @@ -51,7 +51,22 @@ if(isset($_POST['data']) && !empty($_POST['data']) && isset($_POST['submitsearch } elseif(Summoner::validate($searchValue,'text')) { # search for this in more then one field - + # remove mysql funktion stuff + $searchValue = str_replace("*", "", $searchValue); + $searchValue = str_replace("+", "", $searchValue); + $searchValue = str_replace("-", "", $searchValue); + $searchValue = str_replace("<", "", $searchValue); + $searchValue = str_replace(">", "", $searchValue); + $searchValue = str_replace("~", "", $searchValue); + $searchValue = str_replace("'", "", $searchValue); + $searchValue = str_replace('"', "", $searchValue); + + $queryStr = "SELECT *, MATCH (search) + AGAINST ('*".$DB->real_escape_string($searchValue)."*' IN BOOLEAN MODE) AS score + FROM `".DB_PREFIX."_link` + WHERE MATCH (search) + AGAINST ('*".$DB->real_escape_string($searchValue)."*' IN BOOLEAN MODE) + ORDER BY score DESC"; } else { $submitFeedback['message'] = 'Invalid input'; @@ -116,6 +131,16 @@ if(isset($_POST['data']) && !empty($_POST['data']) && isset($_POST['addnewone']) if($isUrl === true && !empty($formData['title']) && $username === FRONTEND_USERNAME && $password === FRONTEND_PASSWORD) { $hash = md5($formData['url']); + + # categories and tag stuff + $catArr = Summoner::prepareTagOrCategoryStr($formData['category']); + $tagArr = Summoner::prepareTagOrCategoryStr($formData['tag']); + + $search = $formData['title']; + $search .= ' '.$formData['description']; + $search .= ' '.implode(" ",$tagArr); + $search .= ' '.implode(" ",$catArr); + $queryStr = "INSERT IGNORE INTO `".DB_PREFIX."_link` SET `link` = '".$DB->real_escape_string($formData['url'])."', `created` = NOW(), @@ -123,15 +148,14 @@ if(isset($_POST['data']) && !empty($_POST['data']) && isset($_POST['addnewone']) `description` = '".$DB->real_escape_string($formData['description'])."', `title` = '".$DB->real_escape_string($formData['title'])."', `image` = '".$DB->real_escape_string($formData['image'])."', - `hash` = '".$DB->real_escape_string($hash)."'"; + `hash` = '".$DB->real_escape_string($hash)."', + `search` = '".$DB->real_escape_string($search)."'"; $DB->query($queryStr); $linkID = $DB->insert_id; if(!empty($linkID)) { - # categories and tag stuff - $catArr = Summoner::prepareTagOrCategorieStr($formData['category']); - $tagArr = Summoner::prepareTagOrCategorieStr($formData['tag']); + if(!empty($catArr)) { foreach($catArr as $c) { @@ -171,5 +195,5 @@ if(isset($_POST['data']) && !empty($_POST['data']) && isset($_POST['addnewone']) $existingCategories = $Management->categories(); $existingTags = $Management->tags(); -$latestLinks = $Management->latest(); +$latestLinks = $Management->latestLinks(); $orderedCategories = $Management->categoriesByDateAdded(); \ No newline at end of file diff --git a/webroot/view/overview.inc.php b/webroot/view/overview.inc.php index f4c0fe5..0ba9a0a 100644 --- a/webroot/view/overview.inc.php +++ b/webroot/view/overview.inc.php @@ -72,5 +72,5 @@ switch($_requestMode) { case 'all': default: # show all - $linkCollection = $Management->all(); + $linkCollection = $Management->links(); } -- 2.39.5