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:

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.

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 Gris – juan@hotmail.com – 123.456.789
• Max Ernst – max@gmail.com – 987.654.321
• Salvador Dali – dali@email.com – 456.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.