public function actionDefault($param) { // Yes, I hate this code, too. // Yes, it was the fastest way to build a prototype. // Yes, it would just kill the database server later on. // No, do not use anywhere. // Yes, you have just been warned. $this->tags = array_filter(preg_split('/\\s+/', $param)); if (empty($this->tags)) { $query = 'SELECT * FROM urls'; } else { $query = 'SELECT * FROM urls WHERE id IN ( SELECT s1.url_id FROM (SELECT ut.url_id FROM url_tags ut JOIN tags t ON ut.tag_id = t.id WHERE t.tag IN (?)) s1' . "\n"; for ($i = 2; $i <= count($this->tags); $i++) { $query .= "JOIN (SELECT ut.url_id FROM url_tags ut JOIN tags t ON ut.tag_id = t.id WHERE t.tag IN (?)) s{$i} ON s1.url_id = s{$i}.url_id\n"; } $query .= ')'; } $result = call_user_func_array([$this->database, 'fetchAll'], array_merge([$query], $this->tags)); $urls = array(); foreach ($result as $url) { $urls[$url->id] = $url; $urls[$url->id]->tags = array(); } if (!empty($urls)) { $urlTags = $this->database->fetchAll(' SELECT ut.url_id AS urlId, t.tag, tc.category AS category FROM tags t JOIN url_tags ut ON t.id = ut.tag_id LEFT JOIN tag_categories tc ON t.category_id = tc.id WHERE ut.url_id IN (?) ORDER BY t.category_id, t.tag ', array_keys($urls)); foreach ($urlTags as $tag) { $urls[$tag->urlId]->tags[] = $tag; } } $allTags = array_values($this->database->fetchPairs('SELECT id, tag FROM tags ORDER BY tag')); $this->template->tags = $this->tags; $this->template->urls = $urls; $this->template->allTags = $allTags; }
public function getClanky($limit = 10, $offset = 0, $url = null, $stitek = null, $aktuality = false, $search = null, $skupina = null) { $query1 = "SELECT c.*,cr.perex,cr.text,cr.datum as aktualizovano, k.nazev as kategorie,cr.id as crid,\n ko.komentaru, u.alt, u.title\n FROM clanky c\n JOIN (select * from clanky_revize where id in (SELECT max(id) from clanky_revize group by clanek_id )) cr ON (c.id=cr.clanek_id)\n JOIN kategorie k ON (c.kategorie_id = k.id)\n LEFT JOIN upload u ON (c.obrazek_id = u.id)\n LEFT JOIN (select clanek_id,count(*) as komentaru from komentare group by clanek_id) ko ON (ko.clanek_id=c.id)\n WHERE datum_vydani IS NOT NULL AND datum_vydani<=STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s') AND c.smazano=0\n "; $query2 = " GROUP BY c.id\n ORDER BY datum_vydani DESC\n LIMIT ? OFFSET ?;"; if ($aktuality) { return $this->database->fetchAll($query1 . "AND k.url='aktuality' AND k.url=?" . $query2, new \DateTime(), $url, $limit, $offset); } elseif (!empty($search)) { return $this->database->fetchAll($query1 . "AND MATCH(c.titulek,cr.perex,cr.text) AGAINST (? IN BOOLEAN MODE)" . $query2, new \DateTime(), $search, $limit, $offset); } elseif (!empty($url)) { return $this->database->fetchAll($query1 . "AND k.url!='aktuality' AND k.url=?" . $query2, new \DateTime(), $url, $limit, $offset); } elseif (!empty($stitek)) { return $this->database->fetchAll($query1 . "AND c.id IN (SELECT clanek_id FROM stitky WHERE stitek=?)" . $query2, new \DateTime(), $stitek, $limit, $offset); } elseif (!empty($skupina)) { return $this->database->fetchAll($query1 . "AND c.skupina=?" . $query2, new \DateTime(), $skupina, $limit, $offset); } else { return $this->database->fetchAll($query1 . "AND k.url!='aktuality'" . $query2, new \DateTime(), $limit, $offset); } }
public function getItemsByKey($key, $limit = 10) { $channel = $this->getChannelByKey($key); $items = $this->database->fetchAll("select *" . " from rss_items" . " where rss_channel_id=?" . " ORDER BY id desc" . " LIMIT ?", $channel['id'], $limit); return $items; }
public function getAll() { return $this->database->fetchAll("SELECT * FROM uzivatele;"); }
public function getForClanek($id) { return $this->database->fetchAll("SELECT * FROM komentare WHERE clanek_id=? ORDER BY id desc ;", $id); }
public function getImages() { return $this->database->fetchAll("SELECT * FROM upload\n WHERE extension='jpg' OR extension='jpeg' OR extension='png'\n order by id desc;"); }
public function getMenu() { return $this->database->fetchAll("SELECT * FROM kategorie WHERE menu=1;"); }