One of the popular traditions around the blogosphere is for bloggers to share a little link love with their fellow bloggers by linking to sites and/or specific posts that were particularly interesting.
On many sites, including this one, there is a special edition of link love dedicated to the readers that provided the most comments in a given month. The first Monday of each month I reward my top commentators with a little link love in my Speedlinking series.
But what do you do when the new month begins and the comment count in your sidebar widget has been reset before you recorded the top commentators?
This is a question that Ms. Danielle recently asked as she didn’t want to sit at the computer just before midnight to grab a screen shot listing her top commentators.
I’ll share the answer with you in just a moment but will mention that this method requires you to execute a query in your database and does require some degree of technical skill. If any of this information scares you or does not make sense, please feel free to ask for assistance or more clarification.
Here are the steps to retrieve the count of top commentators for a given month:
- Log into the control panel of your hosting account.
- Locate and click the link to phpMyAdmin – may require additional security credentials depending on your setup.
- Open a SQL Query Window by clicking the SQL icon on the left or navigating to the “wp_comments” table and selecting the SQL tab.
- Enter the following SQL statement, substituting the appropriate year and month as necessary (this query would provide the comment count from September 2007):
SELECT comment_author, COUNT( comment_id ) AS comment_count FROM wp_comments WHERE comment_date LIKE ‘2007-09%’ GROUP BY comment_author ORDER BY comment_count DESC
- Execute the SQL statement by clicking the Go button.
- Print screen or snag the list of top commentators to be used in your link love post.
- Close and/or logout of phpMyAdmin and your control panel.
This is an easy way to grab a current count of how many comments each reader has left in a given time period. Give this a try the next time you forget to grab a screen shot before your Top Commentator widget resets.
{ 11 comments… read them below or add one }
I love phpmyadmin! I setup a mock copy of my site in a private sudirectory so I can practice w/o worrying about screwing anything up. There’s a lot of very cool things you can do with it. Good post! We cannot fear the database:)
I’ve got a copy of mySQL running locally that I use with XAMPP for my local development environment. Periodically I take a current backup of my live site and refresh my local copy.
This is a great way to test things out and become more familiar with the database. Thanks for the feedback Bobby!
I love XAMPP too. Apache friends are some sharp folks!
You totally lost me at logging in to the control panel.
Very nice tip. I’ll remember this one. Thanks buddy!
Hey, thanks for this. One suggestion I have for people who’s sites don’t get large volumes of comments (like mine doesn’t) is to include an additional argument after the date and before the GROUP BY that is:
AND comment_type NOT LIKE ‘pingback’
this will eliminate trackbacks and such from showing up in the list. I like the power of phpmyadmin, I just need to learn to use it a bit better.
Informative, indeed!
@Leo :: This stuff is easy to learn – stop being a pretty-boy actor and cut the ties with Stephen doing all of the technical work! 😉
@Brown Baron :: No problem, hope it can come in handy for you some time if you ever need it.
@Nick :: That is a great point about excluding the pingbacks/trackbacks from the comment count. You can also exclude yourself from the query but I figured it was easy enough to simply not capture that in the screen shot.
@STR :: Glad you liked it!
Great tip! I’ll have to try that for this month. I’ve been a bad shout out’er 😛
Thanks for the tip. I’m glad to see more helpful posts on your blog.
@Stephen :: Yeah, stop being such a shout out slacker! 😉
@Rose :: You’re welcome.
As a reminder to everyone, please do not hesitate to drop me an email or comment if you want to see more of a given topic or type of post.
{ 3 trackbacks }