Hello there.
As i used Wmu for my blogengine i made a script which i think can be useful for others. This script is making a table of N blogs with their stats, like last post, last comment, its names, post & comments count and more.
screenshot
Script is connecting to DB takes blog names and users (1 query).
Then for each blog it gets last comment & last post data (2 queries).
Then post & comment total counts (2 queries).
Then builds an array.
I wrote a small example using script of using the array, which builds a table with data, using css.
Here is the code of first part:
<?
//WMU STATS by Inviz
//Contacts:
// Mail: inviz@personart.ru
// MSN: invizko@hotmail.com
// ICQ: 2323825
//last uptdated October, 23 2005
//some kinda OPTIONS
// Uncomment for last active n blogs
//$limit_results=1;
mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);
$query='SELECT id,domain,path,last_updated,display_name
FROM wp_users
LEFT JOIN wp_blogs ON blog_id = id
WHERE is_public="yes" ORDER BY last_updated DESC';
$limit_results ? $query.=' LIMIT '.$limit_results : '';
$result=mysql_query($query);
if (mysql_num_rows($result)>0) {
$i=0;
while ($row=mysql_fetch_array($result)) {
$table[$i]['path']=$row['path'];
$table[$i]['domain']=$row['domain'];
$table[$i]['name']=$row['display_name'];
$table[$i]['id']=$row['id'];
$subquery='SELECT comment_id,comment_date,comment_author,post_date,post_title,post_name
FROM wp_'.$row['id'].'_comments
LEFT JOIN wp_'.$row['id'].'_posts ON id=comment_post_ID
ORDER BY comment_date DESC
LIMIT 1';
$subresult=mysql_query($subquery);
if (mysql_num_rows($subresult)>0) {
$temp=mysql_fetch_row($subresult);
$table[$i]['comment']=true;
$table[$i]['comment_date']=$temp['1'];
$table[$i]['comment_author']=$temp['2'];
$table[$i]['comment_post_path']=explode(' ',$temp['3']);
$table[$i]['comment_post_path']=str_replace('-','/',$table[$i]['comment_post_path'][0]);
$table[$i]['comment_post_title']=$temp['4'];
$table[$i]['comment_post_name']=$temp['5'];
$table[$i]['comment_post_date']=$temp['3'];
$table[$i]['comment_post_url']='http://'.$table[$i]['domain'].$table[$i]['path'].$table[$i]['comment_post_path'].'/'.$table[$i]['comment_post_name'].'#comments';
unset($temp);
}
$subquery='SELECT id,post_date_gmt,post_date,post_title,post_name, post_content
FROM wp_'.$row['id'].'_posts
WHERE post_status=\'publish\'
ORDER BY post_date_gmt DESC
LIMIT 1';
$subresult=mysql_query($subquery);
if (mysql_num_rows($subresult)>0) {
$temp=mysql_fetch_row($subresult);
$table[$i]['post']=true;
$table[$i]['post_except']=substr($temp[5],0,26).'...';
$table[$i]['post_path']=explode(' ',$temp['1']);
$table[$i]['post_path']=str_replace('-','/',$table[$i]['post_path'][0]);
$table[$i]['post_title']=$temp['3'];
$table[$i]['post_name']=$temp['4'];
$table[$i]['post_date']=$temp['2'];
$table[$i]['post_url']='http://'.$table[$i]['domain'].$table[$i]['path'].$table[$i]['post_path'].'/'.$table[$i]['post_name'];
unset($temp);
}
$subquery='SELECT count(comment_id) FROM wp_'.$row['id'].'_comments';
$subresult=mysql_query($subquery);
if (mysql_num_rows($subresult)>0) {
$temp=mysql_fetch_row($subresult);
$table[$i]['comments_count']=$temp[0];
} else {
$table[$i]['comments_count']=0;
}
$subquery='SELECT count(id) FROM wp_'.$row['id'].'_posts WHERE post_status="publish"';
$subresult=mysql_query($subquery);
if (mysql_num_rows($subresult)>0) {
$temp=mysql_fetch_row($subresult);
$table[$i]['posts_count']=$temp[0];
} else {
$table[$i]['posts_count']=0;
}
$i++;
}
}
?>
Wooah. It can be included in the top of home.php file.
Then the home.php example:
<?php get_header(); ?>
<style>
#wmu_stats th {
color: #fff;
background:#4876A3;
text-align:left;
padding:2px;
}
#wmu_stats td {
padding:2px;
}
.wmu_stats_row_0 td {
background: #eaeaea;
}
</style>
<div id="content" class="widecolumn">
<h2><?php echo $current_site->site_name ?></h2>
This is a WordPress Mu powered site.
You can:
- Login
- Create a new blog
- Edit thie file at
wp-content/themes/home/home.phpwith your favourite text editor and customize this screen.
<?
echo "<table width=\"100%\" id=\"wmu_stats\">
";
echo "<tr><th>Blogauthor</th><th>Last Comment</th><th>Last Post</th><th>Psts</th><th>Cmmnts</th></tr>";
$i=0;
foreach ($table as $row) {
$i++;
echo "<tr class=\"wmu_stats_row_". $i%2 ."\">";
echo "<td><b>".$row['name']."</b></td>";
echo "<td align=\"center\">";
echo $row['comment']==true ? "".$row['comment_author']."
<small>".$row['comment_date']."</small>" : '<b>—</b>';
echo "</td>";
echo "<td align=\"center\">";
if ($row['post']==true) {
echo "";
if ($row['post_title'])
echo $row['post_title'];
else
echo $row['post_except'];
echo "
<small>".$row['post_date']."</small>";
} else {
echo "<b>—</b>";
}
echo "</td>";
echo "<td><b>".$row['posts_count']."</b></td>";
echo "<td><b>".$row['comments_count']."</b></td>";
echo "</tr>";
}
echo "</table>";
?>
</div>
<?php get_footer(); ?>
inertia
