Tweeked / Faster Update Post Count Script = Display feature

[Raptor]

ok heres the reason for this - i have over 35,000 members and 500,000 posts on our forum. We recently did a purge of old crap and needed to run the update post counts script in admin cp.

Well to say it was slow was an understatment. It took a total of 14 hours to finish.

So a coder freind of mine wote some php which did the same job in only 2 hours (from a backup of our db) - it also displayed what action it took on each row (if post count in DB equalled the post count in the USER table it simply skipped - if it found a different number it displayed that value next to the user id.

Now you can use this code and do whatever you like with it. I thought it might be nice to implement into the admin cp. Not really a hack but more of a tweaked version perhaps. Enjoy anyway.....

PHP Code:

<?php

$link = mysql_connect("localhost", "username", "password");
mysql_select_db("database_name");

set_time_limit(300000000);

if ($startfrom)
    $foundresult = mysql_query("select * from user where userid >= $startfrom order by userid asc");
else
    $foundresult = mysql_query("select * from user order by userid asc");
$foundnum = mysql_num_rows($foundresult);

for ($i=0;$i<$foundnum;$i++)
{
    $row = mysql_fetch_array($foundresult);
    $huntId = $row["userid"];
    
    // now count posts
    $huntresult = mysql_query("select count(*) from post where userid='$huntId'");
    $huntnum = mysql_result($huntresult, 0);
    
    // posts identical to user entry?
    if ( ($huntnum <= 0) || ($huntnum == $row["posts"]) )
    {
        echo("Skipping userid: $huntId<br>\r\n");
    }
    else
    {
        echo("Updating userid: $huntId (posts: $huntnum)<br>\r\n");
        mysql_query("update user set posts = '$huntnum' where userid = '$huntId'");
    }
    
    flush();
}

mysql_close();
?>

output looks like.....

Updating userid: 63475 (posts: 49)
Updating userid: 63477 (posts: 6)
Skipping userid: 63478
Updating userid: 63480 (posts: 5)
Skipping userid: 63482
Skipping userid: 63484
Skipping userid: 63486
Skipping userid: 63488
Updating userid: 63490 (posts: 70)

I'm sure someone could take this further, at least I hope so anyway