Changeset 33
- Timestamp:
- 2007-11-25 22:35:32 (4 years ago)
- Files:
-
- trunk/admin/classes/pages/Deletecategory.php (modified) (3 diffs)
- trunk/admin/classes/pages/Deleteforum.php (modified) (2 diffs)
- trunk/classes/misc/Member.php (modified) (2 diffs)
- trunk/classes/misc/Post.php (modified) (1 diff)
- trunk/classes/pages/Delete.php (modified) (1 diff)
- trunk/classes/pages/Members.php (modified) (2 diffs)
- trunk/classes/pages/Moderate.php (modified) (2 diffs)
- trunk/db/mysql.sql (modified) (1 diff)
- trunk/db/spamboard.sql (modified) (1 diff)
- trunk/db/sqlite.sql (modified) (1 diff)
Legend:
- Unmodified
- Added
- Removed
- Modified
- Copied
- Moved
trunk/admin/classes/pages/Deletecategory.php
r20 r33 48 48 $rows = $q->fetchAll(); 49 49 $q = NULL; 50 $posters = Array(); 50 51 foreach ($rows as $row) { 51 52 // iterate over forums in this category … … 56 57 $q2 = NULL; 57 58 foreach ($rows2 as $row2) { 59 // get all posters of this topic 60 $q = $C->prepare('SELECT poster, postedbymember FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE topic = :id'); 61 $q->bindParam(':id', $row2['topicid'], PDO::PARAM_INT, 12); 62 $q->execute(); 63 $rows = $q->fetchAll(); 64 $q = NULL; 65 foreach ($rows as $row) { 66 /* if this post had been made by a member, 67 * remember the membername for later recounting of posts */ 68 if ($row['postedbymember'] == 1) { 69 $posters[] = $row['poster']; 70 } 71 } 58 72 // delete posts in this forum 59 73 $q3 = $C->prepare('DELETE FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE topic = :id'); … … 79 93 $q3->execute(); 80 94 $q3 = NULL; 95 } 96 // recount posts of the affected members 97 $posters = array_unique($posters); 98 foreach ($posters as $poster) { 99 $_member = new Member($poster, TRUE); 100 $_member->recountPosts(); 101 $_member->setPosts(); 81 102 } 82 103 } else { trunk/admin/classes/pages/Deleteforum.php
r20 r33 49 49 $rows = $q->fetchAll(); 50 50 $q = NULL; 51 $posters = Array(); 51 52 foreach ($rows as $row) { 53 // get all posters of this topic 54 $q = $C->prepare('SELECT poster, postedbymember FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE topic = :id'); 55 $q->bindParam(':id', $row['topicid'], PDO::PARAM_INT, 12); 56 $q->execute(); 57 $rows = $q->fetchAll(); 58 $q = NULL; 59 foreach ($rows as $row) { 60 /* if this post had been made by a member, 61 * remember the membername for later recounting of posts */ 62 if ($row['postedbymember'] == 1) { 63 $posters[] = $row['poster']; 64 } 65 } 52 66 // delete posts 53 67 $q2 = $C->prepare('DELETE FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE topic = :id'); … … 65 79 $q2->execute(); 66 80 $q2 = NULL; 81 } 82 // recount posts of the affected members 83 $posters = array_unique($posters); 84 foreach ($posters as $poster) { 85 $_member = new Member($poster, TRUE); 86 $_member->recountPosts(); 87 $_member->setPosts(); 67 88 } 68 89 } else { trunk/classes/misc/Member.php
r27 r33 123 123 } 124 124 // get number of posts 125 $q2 = $C->prepare('SELECT COALESCE(COUNT(postid), 0) AS posts FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE postedbymember=1 AND poster=:name'); 126 $q2->bindParam(':name', $this->name, PDO::PARAM_STR); 127 $s = $q2->execute(); 128 if ($s) { 129 $row2 = $q2->fetchObject(); 130 } else { 131 $row2->posts = 0; 132 } 133 $q2 = NULL; 134 $this->posts = $row2->posts; 125 $this->posts = $row->memberposts; 135 126 // determine stage by number of posts 136 127 $_i = 0; … … 283 274 return $this->posts; 284 275 } 276 277 /** 278 * Function: setPosts 279 * Description: write number of posts given by input or stored in member 280 * variable back to database 281 * Input: $posts - number of posts 282 * Returns: - 283 **/ 284 public function setPosts($posts = NULL) { 285 if ($posts === NULL) { 286 // use number of posts member variable 287 $n = $this->posts; 288 } else { 289 $n = $posts; 290 } 291 // write to database 292 $q = $C->prepare('UPDATE ' . $SETTINGS['dbtableprefix'] . 'members SET memberposts = :posts WHERE memberid = :id'); 293 $q->bindParam(':posts', $n, PDO::PARAM_INT); 294 $q->bindParam(':id', $this->id, PDO::PARAM_INT, 12); 295 $q->execute(); 296 $q = NULL; 297 } 298 299 /** 300 * Function: recountPosts 301 * Description: get the exact number of posts for this member by counting 302 * in the posts table 303 * Input: - 304 * Returns: - 305 **/ 306 public function recountPosts() { 307 $q = $C->prepare('SELECT COALESCE(COUNT(*), 0) FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE poster = :membername AND postedbymember = :one'); 308 $q->bindParam(':membername', $this->name, PDO::PARAM_STR); 309 $q->bindValue(':one', 1, PDO::PARAM_INT); 310 $q->execute(); 311 $this->posts = $q->fetchColumn(); 312 $q = NULL; 313 } /* function recountPosts */ 285 314 286 315 /** trunk/classes/misc/Post.php
r20 r33 388 388 $q->execute(); 389 389 $q = NULL; 390 // recount member's posts if necessary 391 if ($postedbymember == 1) { 392 $_member = new Member($_SESSION['memberid']); 393 $_member->recountPosts(); 394 $_member->setPosts(); 395 } 390 396 // topic subscription 391 397 if (isset($_SESSION['memberid']) && $_SESSION['memberid'] > 0) { trunk/classes/pages/Delete.php
r19 r33 68 68 $q = NULL; 69 69 if ($_posts >= 2) { 70 // get poster's member id (if any) 71 $q = $C->prepare('SELECT poster, postedbymember FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE postid = :id'); 72 $q->bindParam(':id', $id, PDO::PARAM_INT, 12); 73 $q->execute(); 74 $row = $q->fetchObject(); 75 $q = NULL; 76 if ($row->postedbymember == 1) { 77 // recount posts of this member 78 $_member = new Member($row->poster, TRUE); 79 $_member->recountPosts(); 80 $_member->setPosts(); 81 } 70 82 // purge post from database 71 83 $q = $C->prepare('DELETE FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE postid=:id'); trunk/classes/pages/Members.php
r24 r33 50 50 switch ($order) { 51 51 case 'name': 52 $orderdb = 'membername asc';52 $orderdb = 'membername ASC'; 53 53 break; 54 54 case 'status': 55 $orderdb = 'memberstatus asc';55 $orderdb = 'memberstatus ASC'; 56 56 break; 57 57 case 'posts': 58 /* we need a slightly more complex and significantly slower query here; 59 * in fact some work on this would still be required to make it really good, 60 * however, the obvious (LEFT OUTER JOIN) takes even several times the time this method used at the moment takes */ 61 // first, we ONLY take the member who have actually posted and order them 62 $q = $C->prepare('SELECT m.memberid AS memberid, COUNT(p.postid) AS memberposts FROM ' . $SETTINGS['dbtableprefix'] . 'members AS m, ' . $SETTINGS['dbtableprefix'] . 'posts AS p WHERE p.poster = m.membername AND p.postedbymember = :one GROUP BY m.memberid ORDER BY memberposts DESC, membername ASC LIMIT :start, :perpage'); 63 $q->bindValue(':one', 1, PDO::PARAM_INT); 64 $q->bindParam(':start', $start, PDO::PARAM_INT); 65 $q->bindParam(':perpage', $SETTINGS['topicsperpage'], PDO::PARAM_INT); 58 /* the total posts of a member are listed in the members table; 59 * this is bad SQL style, but it's needed for performance reasons */ 60 $orderdb = 'memberposts DESC'; 66 61 break; 67 62 case 'registered': 68 $orderdb = 'registered asc';63 $orderdb = 'registered ASC'; 69 64 break; 70 65 } 71 if ($order != 'posts') { 72 // simple (fast) query) 73 $q = $C->prepare('SELECT memberid FROM ' . $SETTINGS['dbtableprefix'] . 'members ORDER BY ' . $orderdb . ', membername ASC LIMIT :start, :perpage'); 74 $q->bindParam(':start', $start, PDO::PARAM_INT); 75 $q->bindParam(':perpage', $SETTINGS['topicsperpage'], PDO::PARAM_INT); 76 } 66 $q = $C->prepare('SELECT memberid FROM ' . $SETTINGS['dbtableprefix'] . 'members ORDER BY ' . $orderdb . ', membername ASC LIMIT :start, :perpage'); 67 $q->bindParam(':start', $start, PDO::PARAM_INT); 68 $q->bindParam(':perpage', $SETTINGS['topicsperpage'], PDO::PARAM_INT); 77 69 $s = $q->execute(); 78 70 // put all the results into an array … … 101 93 // display members 102 94 $i = $start + 1; 103 // add the zero posters when ordering by posts (if needed, because it's a page which contains some)104 if ($order == 'posts' && count($rows_ordered) < $SETTINGS['topicsperpage']) {105 // get number of members who have / have not posted106 $q3 = $C->prepare('SELECT COUNT(DISTINCT(poster)) FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE postedbymember=:one');107 $q3->bindValue(':one', 1, PDO::PARAM_INT);108 $q3->execute();109 $_postercount = $q3->fetchColumn();110 $q3 = NULL;111 $_nonpostercount = $membercount - $_postercount;112 // define start and length of database query113 $_start = max($start - $_postercount, 0);114 $_num = $SETTINGS['topicsperpage'] - count($rows_ordered);115 // get the non-poster ids116 $q3 = $C->prepare('SELECT memberid FROM ' . $SETTINGS['dbtableprefix'] . 'members WHERE membername NOT IN (SELECT DISTINCT(poster) FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE postedbymember=:one) ORDER BY membername ASC LIMIT ' . $_start . ', ' . $_num);117 $q3->bindValue(':one', 1, PDO::PARAM_INT);118 $q3->execute();119 $_rows_temp = $q3->fetchAll();120 $q3 = NULL;121 // append new results to array122 $_j = count($rows_ordered);123 foreach ($_rows_temp as $_row) {124 $rows_ordered[$_j] = $_row;125 $_j++;126 }127 }128 95 foreach ($rows_ordered as $row) { 129 96 // create member object trunk/classes/pages/Moderate.php
r20 r33 175 175 $this->html->body->addChild('h2', $LANG['Delete_Topic']); 176 176 if ($commit == 'y') { 177 // get all posters of this topic 178 $q = $C->prepare('SELECT poster, postedbymember FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE topic = :id'); 179 $q->bindParam(':id', $id, PDO::PARAM_INT, 12); 180 $q->execute(); 181 $rows = $q->fetchAll(); 182 $q = NULL; 183 $_posters = Array(); 184 foreach ($rows as $row) { 185 // if this post had been made by a member, remember the membername for later recounting of posts 186 if ($row['postedbymember'] == 1) { 187 $_posters[] = $row['poster']; 188 } 189 } 177 190 // delete all posts belonging to the topic 178 191 $q = $C->prepare('DELETE FROM ' . $SETTINGS['dbtableprefix'] . 'posts WHERE topic=:id'); … … 185 198 $q->execute(); 186 199 $q = NULL; 200 // recount posts of the affected members 201 $_posters = array_unique($_posters); 202 foreach ($_posters as $poster) { 203 $_member = new Member($poster, TRUE); 204 $_member->recountPosts(); 205 $_member->setPosts(); 206 } 187 207 // success message 188 208 $div = $this->html->body->addChild('div', $LANG['topic_deleted']); trunk/db/mysql.sql
r32 r33 12 12 CREATE INDEX idx_forums ON %prefix%forums (forum_category ASC); 13 13 CREATE INDEX idx_topics ON %prefix%topics (forum ASC); 14 CREATE INDEX idx_posts ON %prefix%posts (topic DESC, posttime ASC); 14 CREATE INDEX idx_posts_topic ON %prefix%posts (topic DESC, posttime ASC); 15 CREATE INDEX idx_posts_poster ON %prefix%posts (poster ASC, posttime DESC); 16 CREATE INDEX idx_members_membername ON %prefix%members (membername ASC); 17 CREATE INDEX idx_members_memberstatus ON %prefix%members (memberstatus ASC); 18 CREATE INDEX idx_members_memberposts ON %prefix%members (memberposts DESC); 19 CREATE INDEX idx_members_registered ON %prefix%members (registered ASC); trunk/db/spamboard.sql
r32 r33 90 90 memberpassword TEXT NOT NULL, 91 91 memberstatus VARCHAR(255) NOT NULL, 92 memberposts INTEGER NOT NULL DEFAULT 0, 92 93 location VARCHAR(255) NULL, 93 94 icq VARCHAR(255) NULL, trunk/db/sqlite.sql
r32 r33 1 1 CREATE INDEX idx_forums ON %prefix%forums (forum_category ASC); 2 2 CREATE INDEX idx_topics ON %prefix%topics (forum ASC); 3 CREATE INDEX idx_posts ON %prefix%posts (topic DESC, posttime ASC); 3 CREATE INDEX idx_posts_topic ON %prefix%posts (topic DESC, posttime ASC); 4 CREATE INDEX idx_posts_poster ON %prefix%posts (poster ASC, posttime DESC); 5 CREATE INDEX idx_members_membername ON %prefix%members (membername ASC); 6 CREATE INDEX idx_members_memberstatus ON %prefix%members (memberstatus ASC); 7 CREATE INDEX idx_members_memberposts ON %prefix%members (memberposts DESC); 8 CREATE INDEX idx_members_registered ON %prefix%members (registered ASC);
