Like the blog? Get the book »

Get Comment Info from the WordPress Database

Get Comment Info from the WordPress Database

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 is retrieving the comment information from the database for the 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:

Screenshot of WordPress database 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.

Let’s walk through the specific steps to see how it all works..

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.

Want more SQL recipes like this one? Check out Wizard’s SQL Recipes for WordPress — includes an entire chapter on optimizing the WP database!

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. 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

    Thanks for the useful and handy tip.

Comments are closed for this post. Contact us with any critical information.
© 2009–2024 Digging Into WordPress Powered by WordPress Monzilla Media shapeSpace