How to track the number of comments in your blogs

Yes, it’s a sequel to How to track the number of your blog posts in graphical form.

I had been using that method to track the number of my blog posts, in this blog and others, for months. But, believe it or not, I only wondered yesterday if it was possible to track comments as well. Talk about being attentive.

The answer was yes, and it was quite trivial: just do a search-and-replace for “posts” to “comments” in all script contents and file names. But… whoa, 4700 comments? That’s almost 5 times as many as the posts I have, and I surely don’t have that many comments.

The explanation is that comments flagged as spam were included. So I had to specify that I only wanted approved comments.

Since we all love to see things for ourselves, here’s my own example.

And now for the scripts themselves:

1- file /etc/mrtg-blogcomments.cfg

WorkDir: /var/www/htdocs/blogcomments Options[blogcomments]: nopercent,noi,growright,nobanner,nolegend,noinfo,gauge,integer Refresh: 6000 Target[blogcomments]: `/root/bin/blogcomments.php` MaxBytes[blogcomments]: 5000 Title[blogcomments]: Blog Comments PageTop[blogcomments]: <h1>Blog Comments</h1> XSize[blogcomments]: 500 YSize[blogcomments]: 250 XScale[blogcomments]: 1.4 YScale[blogcomments]: 1.4 YLegend[blogcomments]: comments ShortLegend[blogcomments]: comments Legend1[blogcomments]: Blog Comments Legend3[blogcomments]: Max Comments LegendI[blogcomments]: Blog Comments: LegendO[blogcomments]: Blog Comments:

2- file /root/bin/blogcomments.php

#!/usr/local/bin/php <?php $comments[1] = rtrim (`mysql -s -u blog1user -pblog1pw blog1 -e “select count(*) from wp_comments where comment_approved=’1′;” `); $comments[2] = rtrim (`mysql -s -u blog2user -pblog2pw blog2 -e “select count(*) from wp_comments where comment_approved=’1′;” `); $comments[3] = rtrim (`mysql -s -u blog3user -pblog3pw blog3 -e “select count(*) from wp_comments where comment_approved=’1′;” `); # … $total = array_sum ($comments); print “$total\n”; print “$total\n”; print `uptime`; print `uname -n`; ?>

NOTE 1: the script is in /root/bin , because it contains the databases’ passwords.
NOTE 2: obviously, you should change blog#user, blog#pw and blog#.
NOTE 3: if there are more blogs, just add the queries at “# …”, in the script

3- in root’s crontab:

*/5 * * * * /usr/local/bin/mrtg /etc/mrtg-blogcomments.cfg

Enjoy.

Add to: