Simple latest comment list
authorChristian Weiske <cweiske@cweiske.de>
Mon, 26 Feb 2018 21:39:09 +0000 (22:39 +0100)
committerChristian Weiske <cweiske@cweiske.de>
Mon, 26 Feb 2018 21:39:09 +0000 (22:39 +0100)
data/templates/latest.htm [new file with mode: 0644]
src/anoweco/Storage.php
www/css/latest.css [new file with mode: 0644]
www/latest.php [new file with mode: 0644]

diff --git a/data/templates/latest.htm b/data/templates/latest.htm
new file mode 100644 (file)
index 0000000..aa6e884
--- /dev/null
@@ -0,0 +1,30 @@
+<html xmlns="http://www.w3.org/1999/xhtml">
+ <head>
+  <meta charset="utf-8"/>
+  <title>Latest comments</title>
+  <link rel="stylesheet" type="text/css" href="css/latest.css"/>
+ </head>
+ <body class="h-entry">
+  <h1>Latest comments</h1>
+  <table>
+   <thead>
+    <tr>
+     <th>Date</th>
+     <th>Type</th>
+     <th>User</th>
+     <th>For domain</th>
+    </tr>
+    </thead>
+   <tbody>
+    {% for comment in comments %}
+    <tr>
+     <td><a href="{{comment.url}}">{{comment.comment_published}}</a></td>
+     <td>{{comment.comment_type}}</td>
+     <td>{{comment.user.user_name}}</td>
+     <td>{{comment.domain}}</td>
+    </tr>
+    {% endfor %}
+   </tbody>
+  </table>
+ </body>
+</html>
index 4508f10..6a7e204 100644 (file)
@@ -93,6 +93,49 @@ class Storage
     }
 
     /**
+     * @return null|object NULL if not found, JSON comment object otherwise
+     *                     - "Xrow" property contains the database row object
+     *                     - "user" property contains the user db row object
+     */
+    public function listLatest()
+    {
+        $stmt = $this->db->prepare(
+            'SELECT comment_id, comment_user_id, comment_published'
+            . ', comment_of_url, comment_type'
+            . ' FROM comments'
+            . ' ORDER BY comment_published DESC'
+            . ' LIMIT 20'
+        );
+        $stmt->execute();
+        $rows = $stmt->fetchAll(\PDO::FETCH_OBJ);
+        if (!count($rows)) {
+            return [];
+        }
+        $userIds = array_values(
+            array_unique(array_column($rows, 'comment_user_id'))
+        );
+
+
+        $placeholders = implode(',', array_fill(0, count($userIds), '?'));
+        $stmt = $this->db->prepare(
+            'SELECT * FROM users WHERE user_id IN (' . $placeholders . ')'
+        );
+        $stmt->execute($userIds);
+
+        $users = $stmt->fetchAll(\PDO::FETCH_OBJ);
+        $users = array_combine(
+            array_column($users, 'user_id'),
+            $users
+        );
+
+        foreach ($rows as $row) {
+            $row->user = $users[$row->comment_user_id];
+        }
+
+        return $rows;
+    }
+
+    /**
      * @return null|object NULL if not found, user database row otherwise
      */
     public function getUser($id)
diff --git a/www/css/latest.css b/www/css/latest.css
new file mode 100644 (file)
index 0000000..6c8c651
--- /dev/null
@@ -0,0 +1,8 @@
+table {
+    border: 1px solid grey;
+    border-collapse: collapse;
+}
+td, th {
+    border: 1px solid grey;
+    padding: 0.5ex 1ex;
+}
diff --git a/www/latest.php b/www/latest.php
new file mode 100644 (file)
index 0000000..12105d9
--- /dev/null
@@ -0,0 +1,17 @@
+<?php
+namespace anoweco;
+require 'www-header.php';
+
+$storage = new Storage();
+$comments = $storage->listLatest();
+
+foreach ($comments as $comment) {
+    $comment->url = Urls::comment($comment->comment_id);
+    $comment->domain = parse_url($comment->comment_of_url, PHP_URL_HOST);
+}
+
+$vars = [
+    'comments' => $comments,
+];
+render('latest', $vars);
+?>