diff options
| author | Meik Sievertsen <acydburn@phpbb.com> | 2008-09-24 13:57:13 +0000 |
|---|---|---|
| committer | Meik Sievertsen <acydburn@phpbb.com> | 2008-09-24 13:57:13 +0000 |
| commit | 936f7d43f0635b59b4eb7fcdff4d04b37ea03c21 (patch) | |
| tree | 88d73f9b55f94a2b9e05755ce7c5f22cd8d7186c | |
| parent | 1e7a75db8f286730250baeefedd640dbfa5f3427 (diff) | |
| download | forums-936f7d43f0635b59b4eb7fcdff4d04b37ea03c21.tar forums-936f7d43f0635b59b4eb7fcdff4d04b37ea03c21.tar.gz forums-936f7d43f0635b59b4eb7fcdff4d04b37ea03c21.tar.bz2 forums-936f7d43f0635b59b4eb7fcdff4d04b37ea03c21.tar.xz forums-936f7d43f0635b59b4eb7fcdff4d04b37ea03c21.zip | |
better query for syncing post counts. Thanks to BartVB for this. ;)
git-svn-id: file:///svn/phpbb/branches/phpBB-3_0_0@8932 89ea8834-ac86-4346-8a33-228a782c2dd0
| -rw-r--r-- | phpBB/includes/acp/acp_main.php | 23 | ||||
| -rw-r--r-- | phpBB/install/database_update.php | 26 |
2 files changed, 23 insertions, 26 deletions
diff --git a/phpBB/includes/acp/acp_main.php b/phpBB/includes/acp/acp_main.php index eacc79272b..2b932ebeeb 100644 --- a/phpBB/includes/acp/acp_main.php +++ b/phpBB/includes/acp/acp_main.php @@ -186,30 +186,29 @@ class acp_main // Resync post counts $start = 0; + $step = ($config['num_posts']) ? (max((int) ($config['num_posts'] / 5), 20000)) : 20000; + + $db->sql_query('UPDATE ' . USERS_TABLE . ' SET user_posts = 0'); do { - $sql = 'SELECT COUNT(p.post_id) AS num_posts, u.user_id - FROM ' . USERS_TABLE . ' u - LEFT JOIN ' . POSTS_TABLE . ' p ON (u.user_id = p.poster_id AND p.post_postcount = 1 AND p.post_approved = 1) - GROUP BY u.user_id - ORDER BY u.user_id ASC'; - $result = $db->sql_query_limit($sql, 200, $start); + $sql = 'SELECT COUNT(post_id) AS num_posts, poster_id + FROM ' . POSTS_TABLE . ' + WHERE post_id BETWEEN ' . ($start + 1) . ' AND ' . ($start + $step) . ' + AND post_postcount = 1 AND post_approved = 1 + GROUP BY poster_id'; + $result = $db->sql_query($sql); if ($row = $db->sql_fetchrow($result)) { - $i = 0; - do { - $sql = 'UPDATE ' . USERS_TABLE . " SET user_posts = {$row['num_posts']} WHERE user_id = {$row['user_id']}"; + $sql = 'UPDATE ' . USERS_TABLE . " SET user_posts = user_posts + {$row['num_posts']} WHERE user_id = {$row['poster_id']}"; $db->sql_query($sql); - - $i++; } while ($row = $db->sql_fetchrow($result)); - $start = ($i < 200) ? 0 : $start + 200; + $start += $step; } else { diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 81db58cf21..3e733e6b22 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -1893,32 +1893,30 @@ function change_database_data(&$no_updates, $version) /** * Do not resync post counts here. An admin may later do this from the ACP $start = 0; + $step = ($config['num_posts']) ? (max((int) ($config['num_posts'] / 5), 20000)) : 20000; + + $sql = 'UPDATE ' . USERS_TABLE . ' SET user_posts = 0'; + _sql($sql, $errored, $error_ary); do { - @flush(); - - $sql = 'SELECT COUNT(p.post_id) AS num_posts, u.user_id - FROM ' . USERS_TABLE . ' u - LEFT JOIN ' . POSTS_TABLE . ' p ON (u.user_id = p.poster_id AND p.post_postcount = 1 AND p.post_approved = 1) - GROUP BY u.user_id - ORDER BY u.user_id ASC'; - $result = $db->sql_query_limit($sql, 200, $start); + $sql = 'SELECT COUNT(post_id) AS num_posts, poster_id + FROM ' . POSTS_TABLE . ' + WHERE post_id BETWEEN ' . ($start + 1) . ' AND ' . ($start + $step) . ' + AND post_postcount = 1 AND post_approved = 1 + GROUP BY poster_id'; + $result = _sql($sql, $errored, $error_ary); if ($row = $db->sql_fetchrow($result)) { - $i = 0; - do { - $sql = 'UPDATE ' . USERS_TABLE . " SET user_posts = {$row['num_posts']} WHERE user_id = {$row['user_id']}"; + $sql = 'UPDATE ' . USERS_TABLE . " SET user_posts = user_posts + {$row['num_posts']} WHERE user_id = {$row['poster_id']}"; _sql($sql, $errored, $error_ary); - - $i++; } while ($row = $db->sql_fetchrow($result)); - $start = ($i < 200) ? 0 : $start + 200; + $start += $step; } else { |