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:

mysql> SELECT users.name, node.status, count(*) 
    -> FROM node, users 
    -> WHERE type = 'blog' 
    -> AND   node.uid = users.uid 
    -> GROUP BY users.name, node.status;
+------+--------+----------+
| name | status | count(*) |
+------+--------+----------+
| tima |      0 |        7 |
| tima |      1 |       21 |
+------+--------+----------+
2 rows in set (0.00 sec)

The results show that my username, tima, has 7 unpublished blog posts and 21 published blog posts.

Another useful query, and the next requirement I see coming, is to summarize all the Drupal content posted by user and publication status. We can easily get to that by modifying the query above to exclude the restriction on type and instead include it in our grouping.

The query will then look like this:

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

And our output will look like:

mysql> SELECT users.name, node.type, node.status, count(*) 
    -> FROM node, users 
    -> WHERE node.uid = users.uid 
    -> GROUP BY users.name, node.type, node.status
    -> ORDER BY users.name, node.type, node.status;
+------+-------+--------+----------+
| name | type  | status | count(*) |
+------+-------+--------+----------+
| tima | blog  |      0 |        7 |
| tima | blog  |      1 |       21 |
| tima | image |      1 |        5 |
| tima | page  |      1 |        1 |
| tima | poll  |      1 |        1 |
+------+-------+--------+----------+

Remember that in the status field, a 0 means that the content is unpublished, and a 1 means that the content is published.