aboutsummaryrefslogtreecommitdiffhomepage
path: root/inc/poche/Database.class.php
diff options
context:
space:
mode:
Diffstat (limited to 'inc/poche/Database.class.php')
-rwxr-xr-x[-rw-r--r--]inc/poche/Database.class.php83
1 files changed, 57 insertions, 26 deletions
diff --git a/inc/poche/Database.class.php b/inc/poche/Database.class.php
index 3332b5a3..c998fe14 100644..100755
--- a/inc/poche/Database.class.php
+++ b/inc/poche/Database.class.php
@@ -10,8 +10,15 @@
10 10
11class Database { 11class Database {
12 var $handle; 12 var $handle;
13 13 private $order = array(
14 function __construct() 14 'ia' => 'ORDER BY entries.id',
15 'id' => 'ORDER BY entries.id DESC',
16 'ta' => 'ORDER BY lower(entries.title)',
17 'td' => 'ORDER BY lower(entries.title) DESC',
18 'default' => 'ORDER BY entries.id'
19 );
20
21 function __construct()
15 { 22 {
16 switch (STORAGE) { 23 switch (STORAGE) {
17 case 'sqlite': 24 case 'sqlite':
@@ -257,48 +264,62 @@ class Database {
257 $query = $this->executeQuery($sql, $params); 264 $query = $this->executeQuery($sql, $params);
258 } 265 }
259 266
260 public function getEntriesByView($view, $user_id, $limit = '') { 267 public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) {
261 switch ($_SESSION['sort']) 268 switch ($view) {
262 { 269 case 'archive':
263 case 'ia': 270 $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
264 $order = 'ORDER BY id'; 271 $params = array($user_id, 1);
265 break;
266 case 'id':
267 $order = 'ORDER BY id DESC';
268 break; 272 break;
269 case 'ta': 273 case 'fav' :
270 $order = 'ORDER BY lower(title)'; 274 $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? ";
275 $params = array($user_id, 1);
271 break; 276 break;
272 case 'td': 277 case 'tag' :
273 $order = 'ORDER BY lower(title) DESC'; 278 $sql = "SELECT entries.* FROM entries
279 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
280 WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
281 $params = array($user_id, $tag_id);
274 break; 282 break;
275 default: 283 default:
276 $order = 'ORDER BY id'; 284 $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? ";
285 $params = array($user_id, 0);
277 break; 286 break;
278 } 287 }
279 288
280 switch ($view) 289 $sql .= $this->getEntriesOrder().' ' . $limit;
281 { 290
291 $query = $this->executeQuery($sql, $params);
292 $entries = $query->fetchAll();
293
294 return $entries;
295 }
296
297 public function getEntriesByViewCount($view, $user_id, $tag_id = 0) {
298 switch ($view) {
282 case 'archive': 299 case 'archive':
283 $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; 300 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
284 $params = array($user_id, 1); 301 $params = array($user_id, 1);
285 break; 302 break;
286 case 'fav' : 303 case 'fav' :
287 $sql = "SELECT * FROM entries WHERE user_id=? AND is_fav=? " . $order; 304 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_fav=? ";
288 $params = array($user_id, 1); 305 $params = array($user_id, 1);
289 break; 306 break;
307 case 'tag' :
308 $sql = "SELECT count(*) FROM entries
309 LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
310 WHERE entries.user_id=? AND tags_entries.tag_id = ? ";
311 $params = array($user_id, $tag_id);
312 break;
290 default: 313 default:
291 $sql = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order; 314 $sql = "SELECT count(*) FROM entries WHERE user_id=? AND is_read=? ";
292 $params = array($user_id, 0); 315 $params = array($user_id, 0);
293 break; 316 break;
294 } 317 }
295 318
296 $sql .= ' ' . $limit;
297
298 $query = $this->executeQuery($sql, $params); 319 $query = $this->executeQuery($sql, $params);
299 $entries = $query->fetchAll(); 320 list($count) = $query->fetch();
300 321
301 return $entries; 322 return $count;
302 } 323 }
303 324
304 public function updateContent($id, $content, $user_id) { 325 public function updateContent($id, $content, $user_id) {
@@ -345,7 +366,7 @@ class Database {
345 } 366 }
346 367
347 public function retrieveAllTags($user_id) { 368 public function retrieveAllTags($user_id) {
348 $sql = "SELECT tags.* FROM tags 369 $sql = "SELECT DISTINCT tags.* FROM tags
349 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 370 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
350 LEFT JOIN entries ON tags_entries.entry_id=entries.id 371 LEFT JOIN entries ON tags_entries.entry_id=entries.id
351 WHERE entries.user_id=?"; 372 WHERE entries.user_id=?";
@@ -357,7 +378,7 @@ class Database {
357 378
358 public function retrieveTag($id, $user_id) { 379 public function retrieveTag($id, $user_id) {
359 $tag = NULL; 380 $tag = NULL;
360 $sql = "SELECT tags.* FROM tags 381 $sql = "SELECT DISTINCT tags.* FROM tags
361 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id 382 LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
362 LEFT JOIN entries ON tags_entries.entry_id=entries.id 383 LEFT JOIN entries ON tags_entries.entry_id=entries.id
363 WHERE tags.id=? AND entries.user_id=?"; 384 WHERE tags.id=? AND entries.user_id=?";
@@ -420,4 +441,14 @@ class Database {
420 $query = $this->executeQuery($sql_action, $params_action); 441 $query = $this->executeQuery($sql_action, $params_action);
421 return $query; 442 return $query;
422 } 443 }
444
445 private function getEntriesOrder() {
446 if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) {
447 return $this->order[$_SESSION['sort']];
448 }
449 else {
450 return $this->order['default'];
451 }
452 }
453
423} 454}