title: Count up all the blog posts on a WP network link: https://onemoretech.wordpress.com/2014/03/13/count-up-all-the-blog-posts-on-a-wp-network/ author: phil2nc description: post_id: 7123 created: 2014/03/13 12:40:45 created_gmt: 2014/03/13 16:40:45 comment_status: closed post_name: count-up-all-the-blog-posts-on-a-wp-network status: publish post_type: post

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: [code language="perl" gutter="false"] #!/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; [/code]

Copyright 2004-2019 Phil Lembo