From a86b03a117bfae8dbfb6c2562b29272814c4a50b Mon Sep 17 00:00:00 2001 From: Banana Date: Thu, 3 Oct 2019 22:28:13 +0200 Subject: [PATCH] pagination --- webroot/config.default.php | 4 +- webroot/lib/category.class.php | 54 ++++++++++++++----- webroot/lib/management.class.php | 91 +++++++++++++++++++++----------- webroot/lib/tag.class.php | 58 ++++++++++++++++---- webroot/view/home.php | 2 +- webroot/view/overview.inc.php | 31 +++++++---- webroot/view/overview.php | 8 +-- 7 files changed, 173 insertions(+), 75 deletions(-) diff --git a/webroot/config.default.php b/webroot/config.default.php index e645573..9b015f7 100644 --- a/webroot/config.default.php +++ b/webroot/config.default.php @@ -3,7 +3,7 @@ * Insipid * Personal web-bookmark-system * - * Copyright 2016-2018 Johannes Keßler + * Copyright 2016-2019 Johannes Keßler * * Development starting from 2011: Johannes Keßler * https://www.bananas-playground.net/projekt/insipid/ @@ -47,7 +47,7 @@ define('LOCAL_STORAGE', 'localdata'); define("USE_PAGE_AUTH",false); # results per page -define("RESULTS_PER_PAGE",10); +define("RESULTS_PER_PAGE",12); # settings for importing from e-mail # SSL/TLS only diff --git a/webroot/lib/category.class.php b/webroot/lib/category.class.php index b901f40..de47cd0 100644 --- a/webroot/lib/category.class.php +++ b/webroot/lib/category.class.php @@ -37,7 +37,13 @@ class Category { * the current loaded category by DB id * @var int */ - private $id; + private $_id; + + /** + * current loaded tag data + * @var array + */ + private $_data; public function __construct($databaseConnectionObject) { $this->DB = $databaseConnectionObject; @@ -46,27 +52,31 @@ class Category { /** * by given string load the info from the DB and even create if not existing * @param string $string + * @return bool|int */ public function initbystring($string) { - $this->id = false; + $this->_id = false; if(!empty($string)) { - $queryStr = "SELECT id FROM `".DB_PREFIX."_category` + $queryStr = "SELECT `id`,`name` FROM `".DB_PREFIX."_category` WHERE `name` = '".$this->DB->real_escape_string($string)."'"; $query = $this->DB->query($queryStr); if(!empty($query) && $query->num_rows > 0) { $result = $query->fetch_assoc(); - $this->id = $result['id']; + $this->_id = $result['id']; + $this->_data = $result; } else { $queryStr = "INSERT INTO `".DB_PREFIX."_category` SET `name` = '".$this->DB->real_escape_string($string)."'"; $this->DB->query($queryStr); if(!empty($this->DB->insert_id)) { - $this->id = $this->DB->insert_id; + $this->_id = $this->DB->insert_id; + $this->_data['id'] = $this->_id; + $this->_data['name'] = $string; } } } - return $this->id; + return $this->_id; } /** @@ -75,7 +85,7 @@ class Category { * @return mixed */ public function initbyid($id) { - $this->id = false; + $this->_id = false; if(!empty($id)) { $queryStr = "SELECT id,name @@ -84,11 +94,27 @@ class Category { $query = $this->DB->query($queryStr); if(!empty($query) && $query->num_rows > 0) { $result = $query->fetch_assoc(); - $this->id = $id; + $this->_id = $id; + $this->_data = $result; } } - return $this->id; + return $this->_id; + } + + /** + * return all or data fpr given key on the current loaded tag + * @param bool $key + * @return array|mixed + */ + public function getData($key=false) { + $ret = $this->_data; + + if(!empty($key) && isset($this->_data[$key])) { + $ret = $this->_data[$key]; + } + + return $ret; } /** @@ -97,10 +123,10 @@ class Category { * @return void */ public function setRelation($linkid) { - if(!empty($linkid) && !empty($this->id)) { + if(!empty($linkid) && !empty($this->_id)) { $queryStr = "INSERT IGNORE INTO `".DB_PREFIX."_categoryrelation` SET `linkid` = '".$this->DB->real_escape_string($linkid)."', - `categoryid` = '".$this->DB->real_escape_string($this->id)."'"; + `categoryid` = '".$this->DB->real_escape_string($this->_id)."'"; $this->DB->query($queryStr); } } @@ -112,18 +138,18 @@ class Category { public function delete() { $ret = false; - if(!empty($this->id)) { + if(!empty($this->_id)) { $this->DB->begin_transaction(MYSQLI_TRANS_START_READ_WRITE); try { $queryStr = "DELETE FROM `".DB_PREFIX."_categoryrelation` - WHERE `categoryid` = '".$this->DB->real_escape_string($this->id)."'"; + WHERE `categoryid` = '".$this->DB->real_escape_string($this->_id)."'"; $this->DB->query($queryStr); $queryStr = "DELETE FROM `".DB_PREFIX."_category` - WHERE `id` = '".$this->DB->real_escape_string($this->id)."'"; + WHERE `id` = '".$this->DB->real_escape_string($this->_id)."'"; $this->DB->query($queryStr); $this->DB->commit(); diff --git a/webroot/lib/management.class.php b/webroot/lib/management.class.php index e709974..8180243 100644 --- a/webroot/lib/management.class.php +++ b/webroot/lib/management.class.php @@ -156,7 +156,7 @@ class Management { public function latestLinks($limit=5) { $ret = array(); - $queryStr = "SELECT * FROM `".DB_PREFIX."_link` WHERE `status` = 2 ORDER BY `created` DESC"; + $queryStr = "SELECT `title` FROM `".DB_PREFIX."_link` WHERE `status` = 2 ORDER BY `created` DESC"; if(!empty($limit)) { $queryStr .= " LIMIT $limit"; } @@ -194,32 +194,46 @@ class Management { * @param int $id * @param string $string * @param int $limit + * @param bool $offset * @return array */ - public function linksByCategory($id,$string,$limit=5) { + public function linksByCategory($id, $string, $limit=5, $offset=false) { $ret = array(); - $queryStr = "SELECT ".$this->COMBINED_SELECT_VALUES." - FROM `".DB_PREFIX."_combined` - WHERE `status` = 2"; + $querySelect = "SELECT ".$this->COMBINED_SELECT_VALUES; + $queryFrom = " FROM `".DB_PREFIX."_combined`"; + $queryWhere = " WHERE `status` = 2"; + if(!empty($id) && is_numeric($id)) { - $queryStr .= " AND `categoryId` = '" . $this->DB->real_escape_string($id) . "'"; + $queryWhere .= " AND `categoryId` = '" . $this->DB->real_escape_string($id) . "'"; } elseif(!empty($string) && is_string($string)) { - $queryStr .= " AND `category` = '" . $this->DB->real_escape_string($string) . "'"; + $queryWhere .= " AND `category` = '" . $this->DB->real_escape_string($string) . "'"; } else { return $ret; } - $queryStr .= "GROUP BY `hash` + $queryOrder = "GROUP BY `hash` ORDER BY `created` DESC"; + $queryLimit = ''; if(!empty($limit)) { - $queryStr .= " LIMIT $limit"; + $queryLimit .= " LIMIT $limit"; + if($offset !== false) { + $queryLimit .= " OFFSET $offset"; + } } - $query = $this->DB->query($queryStr); + $query = $this->DB->query($querySelect.$queryFrom.$queryWhere.$queryOrder.$queryLimit); if(!empty($query) && $query->num_rows > 0) { - $ret = $query->fetch_all(MYSQLI_ASSOC); + while($result = $query->fetch_assoc()) { + $linkObj = new Link($this->DB); + $ret['results'][] = $linkObj->loadShortInfo($result['hash']); + unset($linkObj); + } + + $query = $this->DB->query("SELECT COUNT(DISTINCT(hash)) AS amount ".$queryFrom.$queryWhere); + $result = $query->fetch_assoc(); + $ret['amount'] = $result['amount']; } return $ret; @@ -231,32 +245,46 @@ class Management { * @param int $id * @param string $string * @param int $limit + * @param bool $offset * @return array */ - public function linksByTag($id,$string,$limit=5) { + public function linksByTag($id, $string, $limit=5, $offset=false) { $ret = array(); - $queryStr = "SELECT ".$this->COMBINED_SELECT_VALUES." - FROM `".DB_PREFIX."_combined` - WHERE `status` = 2"; + $querySelect = "SELECT ".$this->COMBINED_SELECT_VALUES; + $queryFrom = " FROM `".DB_PREFIX."_combined`"; + $queryWhere = " WHERE `status` = 2"; + if(!empty($id) && is_numeric($id)) { - $queryStr .= " AND `tagId` = '" . $this->DB->real_escape_string($id) . "'"; + $queryWhere .= " AND `tagId` = '" . $this->DB->real_escape_string($id) . "'"; } elseif(!empty($string) && is_string($string)) { - $queryStr .= " AND `tag` = '" . $this->DB->real_escape_string($string) . "'"; + $queryWhere .= " AND `tag` = '" . $this->DB->real_escape_string($string) . "'"; } else { return $ret; } - $queryStr .= "GROUP BY `hash` + $queryOrder = "GROUP BY `hash` ORDER BY `created` DESC"; + $queryLimit = ''; if(!empty($limit)) { - $queryStr .= " LIMIT $limit"; + $queryLimit .= " LIMIT $limit"; + if($offset !== false) { + $queryLimit .= " OFFSET $offset"; + } } - $query = $this->DB->query($queryStr); + $query = $this->DB->query($querySelect.$queryFrom.$queryWhere.$queryOrder.$queryLimit); if(!empty($query) && $query->num_rows > 0) { - $ret = $query->fetch_all(MYSQLI_ASSOC); + while($result = $query->fetch_assoc()) { + $linkObj = new Link($this->DB); + $ret['results'][] = $linkObj->loadShortInfo($result['hash']); + unset($linkObj); + } + + $query = $this->DB->query("SELECT COUNT(DISTINCT(hash)) AS amount ".$queryFrom.$queryWhere); + $result = $query->fetch_assoc(); + $ret['amount'] = $result['amount']; } return $ret; @@ -268,20 +296,21 @@ class Management { * @param bool $offset * @return array */ - public function links($limit=false,$offset=false) { + public function links($limit=10,$offset=false) { $ret = array(); - $queryStr = "SELECT `hash` - FROM `".DB_PREFIX."_link` - WHERE `status` = 2 - ORDER BY `created` DESC"; + $querySelect = "SELECT `hash`"; + $queryFrom = " FROM `".DB_PREFIX."_link`"; + $queryWhere = " WHERE `status` = 2"; + $queryOrder = " ORDER BY `created` DESC"; + $queryLimit = ""; if(!empty($limit)) { - $queryStr .= " LIMIT $limit"; + $queryLimit = " LIMIT $limit"; if($offset !== false) { - $queryStr .= " OFFSET $offset"; + $queryLimit .= " OFFSET $offset"; } } - $query = $this->DB->query($queryStr); + $query = $this->DB->query($querySelect.$queryFrom.$queryWhere.$queryOrder.$queryLimit); if(!empty($query) && $query->num_rows > 0) { while($result = $query->fetch_assoc()) { $linkObj = new Link($this->DB); @@ -289,9 +318,7 @@ class Management { unset($linkObj); } - $query = $this->DB->query("SELECT COUNT(DISTINCT(hash)) AS `amount` - FROM `".DB_PREFIX."_combined` - WHERE `status` = 2"); + $query = $this->DB->query("SELECT COUNT(hash) AS amount ".$queryFrom.$queryWhere); $result = $query->fetch_assoc(); $ret['amount'] = $result['amount']; } diff --git a/webroot/lib/tag.class.php b/webroot/lib/tag.class.php index 45caae7..d7f9052 100644 --- a/webroot/lib/tag.class.php +++ b/webroot/lib/tag.class.php @@ -37,7 +37,13 @@ class Tag { * the current loaded tag by DB id * @var int */ - private $id; + private $_id; + + /** + * current loaded tag data + * @var array + */ + private $_data; public function __construct($databaseConnectionObject) { $this->DB = $databaseConnectionObject; @@ -48,21 +54,24 @@ class Tag { * @param string $string */ public function initbystring($string) { - $this->id = false; + $this->_id = false; if(!empty($string)) { - $queryStr = "SELECT id FROM `".DB_PREFIX."_tag` + $queryStr = "SELECT `id`,`name` FROM `".DB_PREFIX."_tag` WHERE `name` = '".$this->DB->real_escape_string($string)."'"; $query = $this->DB->query($queryStr); if(!empty($query) && $query->num_rows > 0) { $result = $query->fetch_assoc(); - $this->id = $result['id']; + $this->_id = $result['id']; + $this->_data = $result; } else { $queryStr = "INSERT INTO `".DB_PREFIX."_tag` SET `name` = '".$this->DB->real_escape_string($string)."'"; $this->DB->query($queryStr); if(!empty($this->DB->insert_id)) { - $this->id = $this->DB->insert_id; + $this->_id = $this->DB->insert_id; + $this->_data['id'] = $this->_id; + $this->_data['name'] = $string; } } } @@ -71,11 +80,38 @@ class Tag { /** * by given DB table id load all the info we need * @param int $id + * @return bool|int */ public function initbyid($id) { + $this->_id = false; + if(!empty($id)) { - $this->id = $id; + $queryStr = "SELECT `id`,`name` FROM `".DB_PREFIX."_tag` + WHERE `id` = '".$this->DB->real_escape_string($id)."'"; + $query = $this->DB->query($queryStr); + if(!empty($query) && $query->num_rows > 0) { + $result = $query->fetch_assoc(); + $this->_id = $result['id']; + $this->_data = $result; + } } + + return $this->_id; + } + + /** + * return all or data fpr given key on the current loaded tag + * @param bool $key + * @return array|mixed + */ + public function getData($key=false) { + $ret = $this->_data; + + if(!empty($key) && isset($this->_data[$key])) { + $ret = $this->_data[$key]; + } + + return $ret; } /** @@ -84,10 +120,10 @@ class Tag { * @return boolean */ public function setRelation($linkid) { - if(!empty($linkid) && !empty($this->id)) { + if(!empty($linkid) && !empty($this->_id)) { $queryStr = "INSERT IGNORE INTO `".DB_PREFIX."_tagrelation` SET `linkid` = '".$this->DB->real_escape_string($linkid)."', - `tagid` = '".$this->DB->real_escape_string($this->id)."'"; + `tagid` = '".$this->DB->real_escape_string($this->_id)."'"; $this->DB->query($queryStr); } } @@ -99,18 +135,18 @@ class Tag { public function delete() { $ret = false; - if(!empty($this->id)) { + if(!empty($this->_id)) { $this->DB->begin_transaction(MYSQLI_TRANS_START_READ_WRITE); try { $queryStr = "DELETE FROM `".DB_PREFIX."_tagrelation` - WHERE `tagid` = '".$this->DB->real_escape_string($this->id)."'"; + WHERE `tagid` = '".$this->DB->real_escape_string($this->_id)."'"; $this->DB->query($queryStr); $queryStr = "DELETE FROM `".DB_PREFIX."_tag` - WHERE `id` = '".$this->DB->real_escape_string($this->id)."'"; + WHERE `id` = '".$this->DB->real_escape_string($this->_id)."'"; $this->DB->query($queryStr); $this->DB->commit(); diff --git a/webroot/view/home.php b/webroot/view/home.php index 0e8a556..58128e9 100644 --- a/webroot/view/home.php +++ b/webroot/view/home.php @@ -229,7 +229,7 @@

diff --git a/webroot/view/overview.inc.php b/webroot/view/overview.inc.php index 09eb95a..2aa80c6 100644 --- a/webroot/view/overview.inc.php +++ b/webroot/view/overview.inc.php @@ -51,9 +51,12 @@ $pagination = array('pages' => 0); switch($_requestMode) { case 'tag': if(!empty($_id)) { - $linkCollection = $Management->linksByTag($_id,false,false); - if(!empty($linkCollection)) { - $subHeadline = $linkCollection[0]['tag'].' '; + $linkCollection = $Management->linksByTag($_id,false,RESULTS_PER_PAGE, (RESULTS_PER_PAGE * ($_curPage-1))); + if(!empty($linkCollection['results'])) { + $tagObj = new Tag($DB); + $tagObj->initbyid($_id); + $tagname = $tagObj->getData('name'); + $subHeadline = $tagname.' '; } } else { @@ -64,9 +67,12 @@ switch($_requestMode) { break; case 'category': if(!empty($_id)) { - $linkCollection = $Management->linksByCategory($_id,false,false); - if(!empty($linkCollection)) { - $subHeadline = $linkCollection[0]['category'].' '; + $linkCollection = $Management->linksByCategory($_id,false,RESULTS_PER_PAGE, (RESULTS_PER_PAGE * ($_curPage-1))); + if(!empty($linkCollection['results'])) { + $catObj = new Category($DB); + $catObj->initbyid($_id); + $catname = $catObj->getData('name'); + $subHeadline = $catname.' '; } } else { @@ -79,11 +85,14 @@ switch($_requestMode) { default: # show all $linkCollection = $Management->links(RESULTS_PER_PAGE, (RESULTS_PER_PAGE * ($_curPage-1))); - if(!empty($linkCollection['amount'])) { - $pagination['pages'] = ceil($linkCollection['amount'] / RESULTS_PER_PAGE); - $pagination['curPage'] = $_curPage; - $pagination['m'] = $_requestMode; - } +} +if(!empty($linkCollection['amount'])) { + $pagination['pages'] = ceil($linkCollection['amount'] / RESULTS_PER_PAGE); + $pagination['curPage'] = $_curPage; + $pagination['linkadd'] = '&m='.$_requestMode; + if(!empty($_id)) { + $pagination['linkadd'] .= '&id='.$_id; + } } if($pagination['pages'] > 11) { diff --git a/webroot/view/overview.php b/webroot/view/overview.php index c9dbc38..405cb24 100644 --- a/webroot/view/overview.php +++ b/webroot/view/overview.php @@ -51,14 +51,14 @@
- 0) { ?> + 1) { ?>