6 public function __construct()
8 require __DIR__ . '/../../data/config.php';
9 $this->db = new \PDO($dbdsn, $dbuser, $dbpass);
10 $this->db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
14 * Store a new comment into the database
16 * @param object $json Micropub create JSON
17 * @param integer $userId ID of the user whom this comment belongs
19 * @return integer Comment ID
22 public function addComment($json, $userId)
24 $stmt = $this->db->prepare(
25 'INSERT INTO comments SET'
26 . ' comment_user_id = :userId'
27 . ', comment_published = NOW()'
28 . ', comment_of_url = :ofUrl'
29 . ', comment_type = :type'
30 . ', comment_json = :json'
35 if (isset($json->properties->{'in-reply-to'})) {
36 $ofUrl = reset($json->properties->{'in-reply-to'});
38 } else if (isset($json->properties->{'like-of'})) {
39 $ofUrl = reset($json->properties->{'like-of'});
43 'Invalid post type, only reply and like allowed',
53 ':json' => json_encode($json),
56 return $this->db->lastInsertId();
60 * @return null|object NULL if not found, JSON comment object otherwise
61 * - "Xrow" property contains the database row object
62 * - "user" property contains the user db row object
64 public function getJsonComment($id)
66 $stmt = $this->db->prepare(
67 'SELECT * FROM comments WHERE comment_id = ?'
69 $stmt->execute([$id]);
70 $row = $stmt->fetchObject();
76 $json = json_decode($row->comment_json);
80 $stmt = $this->db->prepare('SELECT * FROM users WHERE user_id = ?');
81 $stmt->execute([$row->comment_user_id]);
82 $rowUser = $stmt->fetchObject();
83 if ($rowUser === false) {
84 $rowUser = (object) array(
86 'user_name' => 'Anonymous',
87 'user_imageurl' => '',
91 $json->user = $rowUser;
96 * @return null|object NULL if not found, JSON comment object otherwise
97 * - "Xrow" property contains the database row object
98 * - "user" property contains the user db row object
100 public function listLatest()
102 $stmt = $this->db->prepare(
103 'SELECT comment_id, comment_user_id, comment_published'
104 . ', comment_of_url, comment_type'
106 . ' ORDER BY comment_published DESC'
110 $rows = $stmt->fetchAll(\PDO::FETCH_OBJ);
114 $userIds = array_values(
115 array_unique(array_column($rows, 'comment_user_id'))
119 $placeholders = implode(',', array_fill(0, count($userIds), '?'));
120 $stmt = $this->db->prepare(
121 'SELECT * FROM users WHERE user_id IN (' . $placeholders . ')'
123 $stmt->execute($userIds);
125 $users = $stmt->fetchAll(\PDO::FETCH_OBJ);
126 $users = array_combine(
127 array_column($users, 'user_id'),
131 foreach ($rows as $row) {
132 $row->user = $users[$row->comment_user_id];
139 * @return null|object NULL if not found, user database row otherwise
141 public function getUser($id)
143 $stmt = $this->db->prepare(
144 'SELECT * FROM users WHERE user_id = ?'
146 $stmt->execute([$id]);
147 $row = $stmt->fetchObject();
149 if ($row === false) {
155 public function findUser($name, $imageurl)
157 $stmt = $this->db->prepare(
158 'SELECT user_id FROM users'
159 . ' WHERE user_name = ? AND user_imageurl = ?'
161 $stmt->execute([$name, $imageurl]);
162 $row = $stmt->fetchObject();
164 if ($row === false) {
167 return $row->user_id;
170 public function createUser($name, $imageurl)
172 $stmt = $this->db->prepare(
173 'INSERT INTO users SET'
174 . ' user_name = :name'
175 . ', user_imageurl = :imageurl'
180 ':imageurl' => $imageurl,
183 return $this->db->lastInsertId();
186 public function setPostPingState($postId, $pingstate)
188 $stmt = $this->db->prepare(
189 'UPDATE comments SET comment_pingstate = ? WHERE comment_id = ?'
191 $stmt->execute(array($pingstate, $postId));