type in your query to search makeyougohmm
Things that ... make you go hmmtechnology music video art news reviews and muse on the web

November 14, 2008

MySQL queries to sort by most comments by reader in WordPress blog

developers, blogs and podcasting, How To — by TDavid @ 10:10 am PST

There are Wordpress plugins out there that will display who has left the most comments at your WordPress powered blog, but what if you want to query this directly from the MySQL database? Here are the queries required to do that.

Show top 10 commenters at your blog with URL and email address

select count(comment_ID) as a, comment_author, comment_author_url, comment_author_email from wpmughd_comments group by comment_author order by a DESC limit 10;

hmm-topcommenters-112008

Note that Lestat and ^Lestat are the same person. This query comes in handy to see users that have changed their names to leave different comments. Sterling has done this too, adding "chip" between his name in 16 comments, giving him a grand total of 619 comments.

As of this morning’s query this blog has 13,162 approved comments. You can find this number inside the WordPress admin dashboard area, but if you want to query it, use the following:

select count(comment_ID) from wpmughd_comments where comment_approved=‘1′;

For reference:

1 = comment approved
0 = comment pending
spam = comment marked as spam

A past Hmm post shows the query necessary to delete spam comments from a WordPress database.

How many comments have I made as of this writing? 1,976 (15%). I’d be curious from a percentage basis seeing how that compares to other bloggers. Feel free to provide your own percentage stats in the comments or trackback.

I’m going to make an extra effort to get these top commenters on future Hmmcast Live shows. Since they aren’t textually shy, maybe the same will apply to audio versions.

Show top 10 commenters for year at your blog

What if you wanted to find out who the top commenters were by year? Here’s the query to do that:

select count(comment_ID) as a, comment_author, comment_author_url, comment_author_email from wpmughd_comments where comment_date like ‘<strong>2008</strong>%’ group by comment_author order by a DESC limit 10;

Just replace 2008 with the year you want to check.  I ran this for years 2003-2007 as well as 2008 to date. If you’d like to see how many comments were made by year, use the following query:

select count(comment_ID) from wpmughd_comments where comment_approved=‘1′ and comment_date like ‘<strong>2008</strong>%’;

It’s interesting to note the numbers of comments received by year as this blog has grown.

2003 - total comments made: 93

hmm-comments-2003

2004 - total comments made: 561 (+503%)

hmm-comments-2004

2005 - total comments made: 2405 (+329%)

hmm-comments-2005

2006 - total comments made: 4,940 (+105%)

hmm-comments-2006

2007 - total comments made: 3,642 (-26%)

hmm-comments-2007 

2008 - total comments made through November 14, 2008: 1,517

hmm-comments-2008

Notes:

  • the peak for most comments received in the year to this blog was in 2006 and has fallen off noticeably since. I’m sure there are many bloggers who would love to have over 100 comments left a month so believe me I’m not complaining. But for the curious, why has this happened? Are blogs dying as some are suggesting? Or is commenting on blogs themselves dying? I don’t think so. Sure, traffic is down a bit here for the year, but not to the degree of the number of comments dropping off.
  • There are tons of places these days to comment externally like Twitter, FriendFeed. I predict this trend of leaving comments elsewhere will level off somewhat and we’ll see return to commenting at blogs directly. Perhaps this won’t rebound in 2009, but I don’t see things deteriorating much further.
  • There remain good reasons to leave comments at blogs, particularly for bloggers wanting other bloggers to follow blog comments to signature URLs. I’m sure there are a good number of readers who don’t use these microblogging tools, nor care to.
  • In the post dated July 23, 2007 I noted that commenting activity had "increased dramatically" over the last year — statistically, at least from a calendar year basis this appears inaccurate. I’m not sure from reading that post what queries I was using to come to that conclusion, but the numbers above don’t lie.

How is commenting activity going at your blog?

Questions for bloggers: have you seen a similar drop-off? What kinds of things are you doing to invite more comments left on your blog?

Questions for non-bloggers: why aren’t you leaving as many comments on blogs as in year’s past? Or are you leaving more? More directly, what would make you more likely to leave more comments on this blog?

Did this post make you go hmm?

F = please no more posts like thisD = not among your best stuffC = average postB = good post, I liked itA = great post, please create more like this (Hmm, no ratings yet)

Loading ... Loading ...

Maybe Related Posts (plugin generated)

RSS Feed comments for this post 17 Comments »

  1. I blame my less commenting on more blogging and actual work. haha.

    Comment by darkmoon — November 14, 2008 @ 10:26 am PST

  2. excuses, excuses! lol

    Comment by TDavid — November 14, 2008 @ 10:45 am PST

  3. Heeeyy…. my second comment got taken out? Lame. Just cuz I had a keyword in it?

    Comment by darkmoon — November 14, 2008 @ 11:15 am PST

  4. What was your second comment? And more importantly what was the keyword? lol

    Comment by TDavid — November 14, 2008 @ 11:34 am PST

  5. V.I.A.G.R.AAAAAAA…..

    Comment by darkmoon — November 14, 2008 @ 11:44 am PST

  6. hehe, yeah, I bet that one is being filtered out.

    Comment by TDavid — November 14, 2008 @ 12:06 pm PST

  7. That’s lame. For being in the top 2 in past years, I should be able to get white listed. Sterling probably cheated in 2006 btw……. I bet he gamed the system, and started to spam your system with one word comments when you weren’t looking. Ehh Sterling? Eh????? ;)

    Comment by darkmoon — November 14, 2008 @ 12:28 pm PST

  8. Seriously, you make a good point, I agree that there should be no keyword filtering for established commenters. Since WP has no support for that type of filtering, I would need to whip up a plugin to do that or see if somebody else has created one already. If anybody reading knows of just such animal, let me know.

    Comment by TDavid — November 14, 2008 @ 6:25 pm PST

  9. It doesn’t look like your code plugin is working correctly.

    Comment by Andrew Ferguson — November 14, 2008 @ 8:54 pm PST

  10. …now it is…hmmm.

    Comment by Andrew Ferguson — November 14, 2008 @ 8:56 pm PST

  11. […] over at Make You Go Hmm has been playing around with mySQL to get stats on his users comments. I also enjoy looking at pretty numbers (always being careful to remember they mean absolutely […]

    Pingback by Andrew Ferguson dot NET » Comment Stats — November 15, 2008 @ 1:13 pm PST

  12. Hey I was number 1 for 2005! All others were number 2 or less.

    Comment by FranciscoIV — November 17, 2008 @ 5:39 am PST

  13. I’m not even going to be close to my usual spot. It’s all Sterling’s fault. I’m still bearing a grudge against his 2006 reign. :p

    Comment by darkmoon — November 17, 2008 @ 7:27 am PST

  14. Oh my. I think that if I’m in your top ten, it’s official: I spend too much time on the internet!

    At least I’m spending it in a good place on your website!

    Cheers!

    M

    Comment by Matt Wardlaw — November 18, 2008 @ 10:33 am PST

  15. Long live us rock and roll bloggers, Matt! Thank you, darkmoon, Andrew, FranciscoIV and others for spending a little of your time here :)

    Comment by TDavid — November 18, 2008 @ 4:15 pm PST

  16. I don’t spend time here. It’s all in the spacial time dimensional warp thingie.

    Comment by darkmoon — November 18, 2008 @ 5:10 pm PST

  17. I showed Top Commentators widget because I’m not good in programming. Only if I knew it earlier. But, I’m happy using the top commentators plugin as it earns me more comments and rss subscribers.

    Comment by Atniz — January 7, 2009 @ 10:58 pm PST


TrackBack URI: http://www.makeyougohmm.com/20081114/5662/trackback/

Leave a comment


By leaving a comment you consent to the Official Hmm Comment Policy

Return Home

Copyright 2003-2008 KMR Enterprises All Rights Reserved. Privacy Policy