Fix comment_pingstate SQL error

[anoweco.git] / src / anoweco / Storage.php

diff --git a/src/anoweco/Storage.php b/src/anoweco/Storage.php
index f3b18ae3a1da275864dcd9cdf29f3d0f9b931230..b5f4165db6bb53369da6f00f8954d420785ccd9b 100644 (file)
--- a/src/anoweco/Storage.php
+++ b/src/anoweco/Storage.php
@@ -28,6 +28,7 @@ class Storage
             . ', comment_of_url = :ofUrl'
             . ', comment_type = :type'
             . ', comment_json = :json'
+            . ', comment_pingstate = "0"'
         );
 
         $ofUrl = '';
@@ -57,9 +58,11 @@ class Storage
     }
 
     /**
-     * @return null|object NULL if not found, comment object otherwise
+     * @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 getComment($id)
+    public function getJsonComment($id)
     {
         $stmt = $this->db->prepare(
             'SELECT * FROM comments WHERE comment_id = ?'
@@ -75,15 +78,13 @@ class Storage
         $json->Xrow = $row;
         //FIXME: load user
 
-        $stmt = $this->db->prepare(
-            'SELECT * FROM users WHERE user_id = ?'
-        );
+        $stmt = $this->db->prepare('SELECT * FROM users WHERE user_id = ?');
         $stmt->execute([$row->comment_user_id]);
         $rowUser = $stmt->fetchObject();
         if ($rowUser === false) {
             $rowUser = (object) array(
-                'user_id'   => 0,
-                'user_name' => 'Anonymous',
+                'user_id'       => 0,
+                'user_name'     => 'Anonymous',
                 'user_imageurl' => '',
             );
         }
@@ -92,6 +93,49 @@ class Storage
         return $json;
     }
 
+    /**
+     * @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
      */
@@ -139,5 +183,13 @@ class Storage
         );
         return $this->db->lastInsertId();
     }
+
+    public function setPostPingState($postId, $pingstate)
+    {
+        $stmt = $this->db->prepare(
+            'UPDATE comments SET comment_pingstate = ? WHERE comment_id = ?'
+        );
+        $stmt->execute(array($pingstate, $postId));
+    }
 }
 ?>