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'
31 . ', comment_pingstate = "0"'
36 if (isset($json->properties->{'in-reply-to'})) {
37 $ofUrl = reset($json->properties->{'in-reply-to'});
39 } else if (isset($json->properties->{'like-of'})) {
40 $ofUrl = reset($json->properties->{'like-of'});
44 'Invalid post type, only reply and like allowed',
54 ':json' => json_encode($json),
57 return $this->db->lastInsertId();
61 * @return null|object NULL if not found, JSON comment object otherwise
62 * - "Xrow" property contains the database row object
63 * - "user" property contains the user db row object
65 public function getJsonComment($id)
67 $stmt = $this->db->prepare(
68 'SELECT * FROM comments WHERE comment_id = ?'
70 $stmt->execute([$id]);
71 $row = $stmt->fetchObject();
77 $json = json_decode($row->comment_json);
81 $stmt = $this->db->prepare('SELECT * FROM users WHERE user_id = ?');
82 $stmt->execute([$row->comment_user_id]);
83 $rowUser = $stmt->fetchObject();
84 if ($rowUser === false) {
85 $rowUser = (object) array(
87 'user_name' => 'Anonymous',
88 'user_imageurl' => '',
92 $json->user = $rowUser;
97 * @return null|object NULL if not found, JSON comment object otherwise
98 * - "Xrow" property contains the database row object
99 * - "user" property contains the user db row object
101 public function listLatest()
103 $stmt = $this->db->prepare(
104 'SELECT comment_id, comment_user_id, comment_published'
105 . ', comment_of_url, comment_type'
107 . ' ORDER BY comment_published DESC'
111 $rows = $stmt->fetchAll(\PDO::FETCH_OBJ);
115 $userIds = array_values(
116 array_unique(array_column($rows, 'comment_user_id'))
120 $placeholders = implode(',', array_fill(0, count($userIds), '?'));
121 $stmt = $this->db->prepare(
122 'SELECT * FROM users WHERE user_id IN (' . $placeholders . ')'
124 $stmt->execute($userIds);
126 $users = $stmt->fetchAll(\PDO::FETCH_OBJ);
127 $users = array_combine(
128 array_column($users, 'user_id'),
132 foreach ($rows as $row) {
133 $row->user = $users[$row->comment_user_id];
140 * @return null|object NULL if not found, user database row otherwise
142 public function getUser($id)
144 $stmt = $this->db->prepare(
145 'SELECT * FROM users WHERE user_id = ?'
147 $stmt->execute([$id]);
148 $row = $stmt->fetchObject();
150 if ($row === false) {
156 public function findUser($name, $imageurl)
158 $stmt = $this->db->prepare(
159 'SELECT user_id FROM users'
160 . ' WHERE user_name = ? AND user_imageurl = ?'
162 $stmt->execute([$name, $imageurl]);
163 $row = $stmt->fetchObject();
165 if ($row === false) {
168 return $row->user_id;
171 public function createUser($name, $imageurl)
173 $stmt = $this->db->prepare(
174 'INSERT INTO users SET'
175 . ' user_name = :name'
176 . ', user_imageurl = :imageurl'
181 ':imageurl' => $imageurl,
184 return $this->db->lastInsertId();
187 public function setPostPingState($postId, $pingstate)
189 $stmt = $this->db->prepare(
190 'UPDATE comments SET comment_pingstate = ? WHERE comment_id = ?'
192 $stmt->execute(array($pingstate, $postId));