public function cacheCategories($catID = 0) { // clear stats of affected categories $sql = "UPDATE `tbl_categories` SET `stats` = NULL"; if ($catID > 0) { $sql .= " WHERE `cid` = {$catID};"; } $this->exec($sql); $categories = new \DB\SQL\Mapper($this->db, $this->prefix . 'categories'); $sql = "SELECT C.cid, C.category, COUNT(DISTINCT S.sid) as counted, \r\n\t\t\t\t\tGROUP_CONCAT(DISTINCT C1.category SEPARATOR '||' ) as sub_categories, \r\n\t\t\t\t\tGROUP_CONCAT(DISTINCT C1.stats SEPARATOR '||' ) as sub_stats\r\n\t\t\tFROM `tbl_categories`C \r\n\t\t\t\tINNER JOIN (SELECT leveldown FROM `tbl_categories` WHERE `stats` = '' ORDER BY leveldown DESC LIMIT 0,1) c2 ON ( C.leveldown = c2.leveldown )\r\n\t\t\t\tLEFT JOIN `tbl_stories_categories`SC ON ( C.cid = SC.cid )\r\n\t\t\t\tLEFT JOIN `tbl_stories`S ON ( S.sid = SC.sid )\r\n\t\t\t\tLEFT JOIN `tbl_categories`C1 ON ( C.cid = C1.parent_cid )\r\n\t\t\tGROUP BY C.cid"; do { $items = $this->exec($sql); $change = FALSE; foreach ($items as $item) { if ($item['sub_categories'] == NULL) { $sub = NULL; } else { $sub_categories = explode("||", $item['sub_categories']); $sub_stats = explode("||", $item['sub_stats']); $sub_stats = array_map("json_decode", $sub_stats); foreach ($sub_categories as $key => $value) { $item['counted'] += $sub_stats[$key]->count; $sub[$value] = $sub_stats[$key]->count; } } $stats = json_encode(["count" => (int) $item['counted'], "cid" => $item['cid'], "sub" => $sub]); unset($sub); $categories->load(array('cid=?', $item['cid'])); $categories->stats = $stats; $categories->save(); $change = $change ?: $categories->changed(); } } while ($change != FALSE); }
public function saveShout($id, array $data) { $shout = new \DB\SQL\Mapper($this->db, $this->prefix . 'shoutbox'); $shout->load(array('id=?', $id)); $shout->copyfrom(["message" => $data['message']]); $i = $shout->changed("message"); $shout->save(); return $i; }