DiggingIntoWordPress

by Chris Coyier & Jeff Starr

Get Comment Info from the WordPress Database

Posted by on

An easy way for visitors to enter their emails is by commenting on a post. We did this recently for people to sign up for a notification email. Instead of using a plugin or custom function for a one-time email list, we just went with WordPress core functionality and used post comments for people to sign up. Then the trick then is retrieving the comment information from the database for the specific sign-up post.

We did this recently to collect commentators’ email addresses, but could have easily extracted other comment info as well — comment author, comment date, comment url, and basically anything in the wp_comments table, shown here:

getting-comment-info You can easily display and collect any of this information for any specific page or post on your site. All you need is a non-public page (or other theme location) to output the results (“non-public” especially if you’re displaying any email data). In our case we just created a new private page and selected our custom page template. Load the page and viola! — instant list of all comment author emails for our sign-up post.

Getting comment info from the database

So you’ve got your sign-up post with some comments, and now want to collect the information and send some emails or whatever. To get the information, we need to query the WP database, select our columns from the wp_comments table, and then display the results on our custom page.

For the SQL query, getting data from the comment table is straightforward, but doing so for a specific post requires a dash of voodoo found in an update on this post. To make a long story short, you have to use nested queries with an arbitrary “AS WHATEVER” added at the end, as such:

SELECT DISTINCT comment_author, comment_author_email, comment_author_IP 
FROM ( 
SELECT DISTINCT comment_author, comment_author_email, comment_author_IP 
FROM wp_comments WHERE comment_post_ID = 1
) AS WHATEVER

The “WHATEVER” is essentially meaningless, so use any name you want. Why? Apparently the “AS” clause is required for the nested (or whatever) queries to work their magic. As you can see, this enables us to grab any column from the wp_comments table. In the example query, we’re selecting the comment_author, comment_author_email, and comment_author_IP columns.

If you have access to the database, you can use a program such as phpMyAdmin to execute the above query directly. Otherwise, we’ll go with the WordPress custom-private-page route. Open your page template and add the following code beneath the_content() template tag:

<?php //grab the data
$comment_info = $wpdb->get_results("SELECT DISTINCT comment_author, comment_author_email, comment_author_IP 
	FROM (SELECT DISTINCT comment_author, comment_author_email, comment_author_IP 
	FROM wp_comments 
	WHERE comment_post_ID = 1
	) AS WHATEVER"); 
// display the results
echo '<ul>';
foreach($comment_info as $info) { 
	echo '<li><strong>'. $info->comment_author .'</strong> - '. $info->comment_author_email .' - <small>'. $info->comment_author_IP .'</small></li>'; 
}
echo '</ul>';
?>

Just pick your post ID and done. When you visit the custom page in a browser, you should see the results of your query displayed as a list, similar to this:

  • Juan Grisjuan@hotmail.com123.456.789
  • Max Ernstmax@gmail.com987.654.321
  • Salvador Dalidali@email.com456.789.123

But no need to keep it list format, with a little tweaking, we can output any data using whatever markup works best. For example, to just grab the emails from a nice <pre> list, change the foreach loop to this:

echo '<pre>';
foreach($comment_info as $info) { 
	echo $info->comment_author_email . "\n";
}
echo '</pre>';

..and that should give you just the data, with no interfering markup:

juan@hotmail.com
max@gmail.com
dali@email.com

Customizing

There are two ways to customize this technique. In the query itself, you can specify which columns you want to display. And then you can also customize the markup, to format the data to suit your specific needs. Sort of a multipurpose method for grabbing post-specific info from the database.

5 Responses

  1. After a few months with my Head First SQL book, I love working with databases. Thanks for the tip on the nested queries. Very cool.

    By the way, I love violas as much as the next music-lover, but I don’t think that’s what you meant ;)

  2. You always manage to post a coding trick that makes me think about WP in a new way. Sometimes we forget the wealth of data inside the WP db that we can use if we just extract it. You probably just sucked a couple hours out of my morning exploring this – thx for the ideas!

  3. Why do you think you need use a subquery in the from clause? Does $wpdb do something different than normal SQL?

    Also, why not use the get_comment function?

    I’m just starting to dig into the functions and trying figure out where and where not to use them.

    Thanks for the info?

  4. Jeff Starr

    I think get_comments might work, but not get_comment, just based on the stated objectives in the article. Good luck with your quest?

  5. Bharat Chowdary April 28, 2012

    Thanks for the useful and handy tip.

Comments are closed. Contact us with any critical information. Thank you!

Code is poetry