WordPress MU

I copied the code from /wp-admin/index.php to get the comment count from one blog and tried making it add up the comments for all the blogs across my wpmu install, but its not working:

<?php
require ('wp-config.php');

$res = mysql_query("SELECT blog_id from wp_blogs");
while ($line = mysql_fetch_array($res)) {
$id = $line['blog_id'];
$comments = (int) $wpdb->get_var("SELECT COUNT(*) FROM wp_${id}_comments WHERE comment_approved = '1'");
}

echo $id;
?>

What am I doing wrong? If I replace wp_${id}_comments with wp_1_comments it will give me the correct value. I just dont know how to make it all add up :D

im using this: (Credits to Stian, for helping sorting it out)

<?php
$query = "SHOW TABLES LIKE 'wp_%_comments'";
$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_NUM)) {
$tables2[] = $row[0];
}

$query3 = "SELECT SUM(antal) FROM ((SELECT COUNT(*) AS antal FROM ";
$query3 .= implode(" WHERE comment_approved = '1' ) UNION ( SELECT COUNT(*) AS antal FROM ", $tables2);
$query3 .= ")) AS temp";

$results2 = mysql_query($query3);
$comment_count = mysql_result($results2, 0);

echo $comment_count; ?>

Enjoy, naturaly it can be applied to posts aswell if u edit it.

Best regards - Fredrik Näs

Nice, seems like its working. The number seems ok...

I'll test some more and report back.

Np m8 good luck!

Ps. it works fine for me, im using it live.

-Thanks
Fredrik Näs

Hmm, made it work with posts as well and all is fine. Antal is a swedish word right?

I wonder how much of a strain would adding this to the front page mean. I have to decide now if I should make a separate page for stats or not :)

hehe yes sorry cleaned it from swedish but must have missed that one, "antal" means "amount" or "number of"

i have a few plugins over at bloggproffs.se/statistik, calling all sorts of stats over there, the one thing im still looking for is amount of posts in each blog. but im getting there ;)

print "<table>";
    #$blogs = get_blog_list();
    #print "<br>blogs: <br>";
    #print_r( $blogs );

    $most_active = get_most_active_blogs( 10, false );
    if( is_array( $most_active ) ) {
	print "<caption>Most Active Blogs</caption>";
	print "<tr><th scope='col'>ID</th><th scope='col'>Address</th><th scope='col'>Posts</th></tr>";
	while( list( $key, $details ) = each( $most_active ) ) {
	    $class = ('alternate' == $class) ? '' : 'alternate';
	    $url = "http://" . $details[ 'domain' ] . $details[ 'path' ];
	    print "<tr class='$class'><td>" . $details[ 'blog_id' ] . "</td><td><a href='$url'>$url</a></td><td>" . $details[ 'postcount' ] . "</td></tr>";
	}
    }
    print "</table>";

This'll get the top ten blogs by number of posts. First line you can change the list. Someone else here wrote it, but I couldn't find the original thread.