PDA

View Full Version : Update Post Counts Script


Raptor
05 Dec 2002, 16:57
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

$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();
?>

Floris
05 Dec 2002, 20:16
Please release hacks in an attachment in some .zip or .txt or whatever file.

CeleronXL
05 Dec 2002, 20:58
Eh?

Xenon
05 Dec 2002, 21:00
nice idea, but it works wrong.

it's that fast, because you don't use a join query to threads, which is needed, if you have forums which don't count to postcounter...

nevertheless the if- idea is good, should be included, why run a query which would change nothing, just time ;)

Logician
06 Dec 2002, 19:19
Agreed with Xenon..

Besides please notice that it ignores moderated posts and posts that are marked as "invisible" by Mods. So when you administer these posts, postcounts will get incorrect..

-Sidekick-
09 Dec 2002, 03:30
lol, looks like another 14 hour job ahead of ya...