Count up all the blog posts on a WP network

WordPress in MultiSite mode implements the concept of a “network” of “sites”. No one seems to have thought about how to get a total count of all the blog posts on a network.

Although their use of the terminology is not always consistent, WordPress in MultiSite mode (as distinct form WordPress MultiUser, or WordPress MU) uses the metaphor of a “network of sites” to organize things. A network contains sites, and each site then can contain many blogs. Although they all share the same database, each blog has its own copy of some key tables, like “posts” and “options”, which are named with the blog’s ID number (for example, “wp_3_options” or “wp_37_posts”).

Currently there’s nothing in the Network Admin dashboard that reports statistics like the number of posts or pages published across the network. To get that information you have to log separately into the dashboard for each blog, not the numbers you’re interested in, and then use a calculator to tally them up.

There are various posts in the WordPres fora and in places like Stackoverflow that provide some php code or pseudocode for implementing widgits or plugins in WordPres to provide this capability. There were even a few plugins that purported to do it. I did try the one free plugin on the latest release of WordPress without success, but writing widgets is something I’m just not ready for yet.

So instead I decided to try and find an SQL query or set of queries that might get me where I’m looking to go. Although I saw some hints on Stackoverflow, in the end I had to dive in, take some time to study the schema, and then hack together the following script.

In its present form the script only counts blog posts, not pages, although it can be made to do it by simply changing the “post_type” from “post” to “page”. It also doesn’t provide the blog name, although the path could be substituted for blog_id in its output.

So here it is, without further comment, straight from my hg repo to your desk:

#!/usr/bin/perl
# wp_count_network_blogs.pl
# Created 3/13/14 by P Lembo

use strict;
use Text::ParseWords;
use DBI();

my $HOME = $ENV{'HOME'};
our($dbUsr, $dbPass, $blogsDB, $blogsHost);
require "$HOME/etc/admin.conf";
my $dbHost = $blogsHost;
my $dbName = $blogsDB;

get_data();

sub get_data {
   my $dbh = DBI->connect(
       "DBI:mysql:database=$dbName;host=$dbHost", "$dbUsr",
      "$dbPass", {'RaiseError' => 1});
  my $query = (
    "SELECT blog_id, path
     FROM wp_blogs;"
  );
  my $sth = $dbh->prepare($query);
  $sth->execute();

  my $total =0;
  while ( my( $blog_id, $path ) = $sth->fetchrow()) {	
	 my $count = get_postcount($blog_id);
	 print "Total posts on ", $path, " are ", $count, "\n";
	 $total = ($total + $count);
  }
  print "Total blogs on network: ", $total, "\n";
  $sth->finish();
  $dbh->disconnect();
}

sub get_postcount {
 my $blog_id = $_[0];
 my $dbh = DBI->connect(
    "DBI:mysql:database=$dbName;host=$dbHost",
    "$dbUsr", "$dbPass", {'RaiseError' => 1});
 my $table_name = "wp_" . $blog_id . "_posts";
 if($table_name =~ /wp_1_posts/) {
	$table_name = "wp_posts";
 }
 my $query = (
	"SELECT COUNT(*)
	 FROM $table_name
	 WHERE post_status = 'publish'
	 AND post_type = 'post';"
	 );
 my $sth = $dbh->prepare($query);
 $sth->execute();
 my $count = $sth->fetchrow();
 $sth->finish();
 $dbh->disconnect();

 return $count;
}
__END__;
This entry was posted in Database, Development, System Administration, Systems Analysis, Web on by .

About phil

My name is Phil Lembo. In my day job I’m an enterprise IT architect for a leading distribution and services company. The rest of my time I try to maintain a semi-normal family life in the suburbs of Raleigh, NC. E-mail me at philipATlembobrothersDOTcom. The opinions expressed here are entirely my own and not those of my employers, past, present or future (except where I quote others, who will need to accept responsibility for their own rants).