Rebuilding Forum Stats

[Ethereal]

Has anyone yet made a script to rebuild the forum stats page:

(this one) http://www.simplemachines.org/community/index.php?action=stats

So that when a person converts to SMF from another piece of software, the registrations per day, posts per day, etc. will all be current as to the posts currently in the database?

[Jerry]

I was just thinking if that was possible also, I converted from phpbb and am missing many months of stats. Although I forgot to post the idea.

[Ethereal]

I'm sure someone can make this use the PHPBB db interface, or the SMF db interface. It worked for me, however, it looks weird seeing a max value of 0 for the "Most Online" part. Maybe someone can come up with something creative to figure this out.

Code Select Expand

<?php

print '
--
-- Stats Updater
-- For PHPBB/mySQL forums to SMF/mySQL Forums
-- Expect possible bugs.
--
-- Fixes / Comments to [email protected]
--
-- This php file will output SQL statements to console, so, you probably want to
-- php update.php > somefile.txt, then
--
-- mysql smf_database -u smf_user -p < somefile.txt
--
';

$link = mysql_connect("localhost", "user", "password");
mysql_select_db("phpbb_database");

echo '
-- Parsing Users for Registrations
';

$q1 = "SELECT user_regdate FROM phpbb_users";
$r1 = mysql_query($q1);

echo "-- Rows: ".mysql_num_rows($r1)."\n";

while ($row = mysql_fetch_row($r1)) {
$ts = date("Ymd", $row[0]);
$year = substr($ts, 0, 4);
$month = substr($ts, 4, 2);
$day = substr($ts, 6, 2);
//print $row[0] . " converts to: " . date("Ymd", $row[0])." $year-$month-$day\n";

$stats[$year][$month][$day]["reg"]++;
}

@mysql_free_result($r1);

echo '
-- Parsing topics...
';

$q2 = "SELECT topic_time, topic_poster FROM phpbb_topics WHERE 1";
$r2 = mysql_query($q2);

echo "-- Rows: ".mysql_num_rows($r2)."\n";

while ($row = mysql_fetch_row($r2)) {
$ts = date("Ymd", $row[0]);
$year = substr($ts, 0, 4);
$month = substr($ts, 4, 2);
$day = substr($ts, 6, 2);
$stats[$year][$month][$day]["topics"]++;
if (!$usertmp[$year][$month][$day][$row[1]]) {
$stats[$year][$month][$day]["users"]++;
$usertmp[$year][$month][$day][$row[1]] = 1;
}
}

@mysql_free_result($r2);

echo '
-- Parsing Posts...
';

$q3 = "SELECT post_time, poster_id FROM phpbb_posts WHERE 1";
$r3 = mysql_query($q3);

echo "-- Rows: ".mysql_num_rows($r3)."\n";

while ($row = mysql_fetch_row($r3)) {
$ts = date("Ymd", $row[0]);
$year = substr($ts, 0, 4);
$month = substr($ts, 4, 2);
$day = substr($ts, 6, 2);
$stats[$year][$month][$day]["posts"]++;
if (!$usertmp[$year][$month][$day][$row[1]]) {
$stats[$year][$month][$day]["users"]++;
$usertmp[$year][$month][$day][$row[1]] = 1;
}
}

@mysql_free_result($r3);

$sql = array();

foreach ($stats as $year => $rem1) {
foreach ($rem1 as $month => $rem2) {
foreach ($rem2 as $day => $rem3) {
$topics = $rem3["topics"];
$posts = $rem3["posts"];
$reg = $rem3["reg"];
$allon = $rem3["users"];
if ($year != 1969) {
$sql[] = "INSERT INTO smf_log_activity VALUES('$year-$month-$day','','$topics','$posts','$reg','','$allon')";
}
}
}
}

echo '-- Sorting'."\n";

sort($sql);

foreach ($sql as $k => $v) {
echo "$v;\n";
}

?>

It threw 1500 users, 8k topics, and 100k posts back to me in less than 30 seconds.

[Jerry]

Seemed to work,  thanks for this script!