TimArcher.com A blog about technology...and more.

Today I had a very simple need for my Drupal based blog. I needed to get the number of blog entries by user and publication status.

To accomplish this, I put together the query below which will dump out this information showing the username, publication status (0=unpublished, 1=published), and the count of blog posts for that username and publication status combination.

The query is:

SELECT users.name, node.status, count(*) 
FROM node, users 
WHERE type = 'blog' 
AND   node.uid = users.uid 
GROUP BY users.name, node.status
ORDER BY users.name, node.status;

Simply run that query through your MySQL command line prompt, or through a GUI utility such as phpMyAdmin. The output will look something like:

About two weeks ago I had setup Drupal 4.7.4, MySQL 4.1.12, and Apache 2.0.52 on my RedHat Linux AS 4 server to serve my site, timarcher.com.

Now one of my friends would like to setup a site for himself. We wanted to leverage everything that I had already configured for timarcher.com and not have to setup another Drupal code base. The basic requirement was to use the same Drupal installation/code base, but allow my friend to have his own configuration, database, users, etc and allow his site to display entirely different content than my existing site.

The remainder of this post will detail the steps I took to get this to work, and assumes you already have a working Drupal installation.