Forum Maintenance > Recalculate Post Counts

[pixelchutes]

We recently migrated a home grown forum solution into SMF 1.1.3

All went very successful, as SMF is such a great piece of software!

I especially enjoyed the

Forum Maintenance > Recount all forum totals and statistics feature, however I noticed that this did not seem to account for "Post Counts" (e.g. smf_members.posts) Is it possible this already exists but I have overlooked it?

This wasn't too big of a deal, nothing a little custom SQL couldn't handle. However, I think it would be great if there was a individual feature for "Recalculating Post Counts", or if it was natively included in the "Recount all forum totals and statistics" process.

Regardless, great work! Excited to see what's in store for v2.0!

[karlbenson]

I think its because admins/mods can adjust a persons post count.

And recounting from scratch would reset it back to the exact amount.

[pixelchutes]

I think its because admins/mods can adjust a persons post count.

And recounting from scratch would reset it back to the exact amount.

You know, that makes perfect sense. I didn't even think of that. Well, no worries

[karlbenson]

I suppose in some instances it might be useful to be able to undo any overzealous post count count altering that has been done.

[rsw686]

Heres a thread with a script to recount the posts

http://www.simplemachines.org/community/index.php?topic=162344.0

A mod to add the recount post count feature to Recount all forum totals and statistics

http://custom.simplemachines.org/mods/index.php?mod=561

And a script I put together to just print out the members who's count differs from the actual count. This script makes no modifications so you can decide whether to fix the post count or not.

Code Select Expand


<?php
// Load SSI.php
require_once('SSI.php');

// Turn this on.
error_reporting(E_ALL);

// Are we allowed in here?
isAllowedTo('admin_forum');

// Lookup the total number of members
$request = db_query("
SELECT COUNT(DISTINCT m.ID_MEMBER)
FROM ({$db_prefix}messages AS m, {$db_prefix}boards AS b)
WHERE m.ID_MEMBER != 0
AND b.countPosts = 0
AND m.ID_BOARD = b.ID_BOARD", __FILE__, __LINE__);
list($totalMembers) = mysql_fetch_row($request);
mysql_free_result($request);
echo 'Checking post count for '.$totalMembers.' members.<br/>';

// Lets get the members and their post counts
$request = db_query("
SELECT m.ID_MEMBER, COUNT(m.ID_MEMBER) AS posts
FROM ({$db_prefix}messages AS m, {$db_prefix}boards AS b)
WHERE m.ID_MEMBER != 0
AND b.countPosts = 0
AND m.ID_BOARD = b.ID_BOARD
GROUP BY m.ID_MEMBER", __FILE__, __LINE__);

// Compare to the stored post count
while ($row = mysql_fetch_assoc($request))
{
$request2 = db_query("
SELECT m.posts
FROM {$db_prefix}members AS m
WHERE ID_MEMBER = $row[ID_MEMBER]", __FILE__, __LINE__);
list($storedCount) = mysql_fetch_row($request2);
if ($row['posts'] != $storedCount)
echo 'Mismatch Count! Member: '.$row['ID_MEMBER'].', Stored Count: '.$storedCount.', Actual Count: '.$row['posts'].'<br/>';
}
mysql_free_result($request);

echo 'Complete!<br/>';

?>


[Oldiesmann]

You don't need a table alias if you're only pulling data from one table...

[rsw686]

Yeah I had thrown that together quickly. I did a rewrite and incorporated a preview into the recount script at http://www.simplemachines.org/community/index.php?topic=162344.0

Code Select Expand


<?php
// Load SSI.php
require_once('SSI.php');

// Turn this on.
error_reporting(E_ALL);

// Are we allowed in here?
isAllowedTo('admin_forum');

$_REQUEST['start'] = !isset($_REQUEST['start']) ? '0' : (int) $_REQUEST['start'];

// Give a link to start
echo '
<h2><a href="', $_SERVER['PHP_SELF'], '?recount;start=0">Start Post Recount (Preview)</a></h2>
<h2><a href="', $_SERVER['PHP_SELF'], '?recount;update;start=0">Start Post Recount (Update)</a></h2>';

// Are we set to start?
if (isset($_REQUEST['recount']))
{
// Only run this query if we don't have the total.
if (!isset($_SESSION['totalMembers']))
{
$request = db_query("
SELECT COUNT(DISTINCT m.ID_MEMBER)
FROM ({$db_prefix}messages AS m, {$db_prefix}boards AS b)
WHERE m.ID_MEMBER != 0
AND b.countPosts = 0
AND m.ID_BOARD = b.ID_BOARD", __FILE__, __LINE__);
list ($_SESSION['totalMembers']) = mysql_fetch_row($request);
mysql_free_result($request);
}

// Initialize the recounted members total
if(isset($_REQUEST['start']) && $_REQUEST['start'] == 0)
$_SESSION['recountedMembers'] = 0;

// Lets get the members and their post counts.
// Make sure that we only get boards that have posts count enabled.
// !!! On a big board 200 might be a bit to high to count.
$request = db_query("
SELECT m.ID_MEMBER, COUNT(m.ID_MEMBER) AS posts
FROM ({$db_prefix}messages AS m, {$db_prefix}boards AS b)
WHERE m.ID_MEMBER != 0
AND b.countPosts = 0
AND m.ID_BOARD = b.ID_BOARD
GROUP BY m.ID_MEMBER
LIMIT $_REQUEST[start], 200", __FILE__, __LINE__);
$_SESSION['recountedMembers'] += mysql_num_rows($request);

// Get all the results and assign the correct value.
while ($row = mysql_fetch_assoc($request))
{
$requestStored = db_query("
SELECT ID_MEMBER, memberName, posts
FROM {$db_prefix}members
WHERE ID_MEMBER = $row[ID_MEMBER]", __FILE__, __LINE__);
$countStored = mysql_fetch_assoc($requestStored);
if ($row['posts'] != $countStored['posts'])
{
echo '
<h3>Mismatch! Member: [', $countStored['ID_MEMBER'], '] ', $countStored['memberName'], ', Stored Count: ', $countStored['posts'], ', Actual Count: '.$row['posts'];
if(isset($_REQUEST['update']))
{
db_query("
UPDATE {$db_prefix}members
SET posts = $row[posts]
WHERE ID_MEMBER = $row[ID_MEMBER]", __FILE__, __LINE__);
echo ' ... Corrected!';
}
echo '</h3>';
}
}

// How many members have their post count been fixed?
echo '
<h3>', $_SESSION['recountedMembers'] >= $_SESSION['totalMembers'] ? $_SESSION['totalMembers'] : $_SESSION['recountedMembers'], '/', $_SESSION['totalMembers'], ' had their post count verified<h3>';

if ($_SESSION['recountedMembers'] >= $_SESSION['totalMembers'])
{
unset($_SESSION['recountedMembers'],$_SESSION['totalMembers']);
echo '
<h3>Done</h3>';
}
elseif ($_SESSION['recountedMembers'] < $_SESSION['totalMembers'])
echo '
<h3><a href="', $_SERVER['PHP_SELF'], '?recount;', isset($_REQUEST['update']) ? 'update;' : '', 'start=', $_SESSION['recountedMembers'], '">Next set of members</a></h3>';

}
?>


[spiros]

Nice mod, however it counts also member posts that are in the Recycle bin.

[Arantor]

You need to alter your recycle bin board's configuration to have that not count posts.

[spiros]

Thanks!