The idea of extracting your commentator’s e-mail addresses spawned recently. Now, I know what you’re thinking. No, I’m not going to spam you. This is a project that I want to undertake to show you that it’s possible and I in no way have anything to offer so don’t worry, you won’t be hearing from me any time soon.
Besides, bots and spammers have been around the block using software to harvest your e-mails since the inception of e-mail itself. Whether you like it or not, every time you comment on a blog, the e-mail you use is already inside the database of the site’s SQL. Thus, I suggest if you’re corresponding or blog hopping on a daily basis to use an e-mail that is solely for building blog relationships.
With WordPress gaining more popularity and becoming mainstream, I thought that there’s got to be a plugin which can do the same thing as well. However, it would go against terms to break the readers trust if any developer would release such a thing.
So basically, I haven’t a clue on how I can get all the e-mail addresses of the commentators on my blog. I’m not a programmer. I sway more on the technical side of hardware, swapping and cleaning out dust. So if you’re asking me to tinker with code. Forget about it!
With the help of magical Google, I found some resources to guide me through the process of getting my hands dirty through PHP though. It’s amazing what you can find when you put your mind to it.
Therefor, I’m going to take it one step further and attempt the process by going through the tutorials and write for you my walk-through for extracting e-mails from your comments. I advise you to go ahead with caution.
Here’s who we’re going to refer to for helping us out today:
- How to Extract Names and E-mail Addresses From WordPress
- How to Extract Names and E-mail Addresses From Your WP Database
- How to Extract Names and E-mail Addresses From WordPress Comments
- How to Extract Commentators E-mail Addresses with IP & Name From A Post
For those of you seeing where I’m going with this, the benefits are good for several reasons:
- When you need to update your readers on something you’ve changed (corrections)
- Thank them for visiting your blog (thanks Murray for pointing this out on Danke!)
- Expand on a conversation you haven’t gotten around to
While spamming is the last thing you want to do, I’m sure readers will know that whatever it is you are working on at the moment, if they’ve been following you, asking them to check it out will not hurt. However, you should announce it first on your blog. I know if I’ve commented on your blog before, I’ll be glad to drop by and take a look at what it is you’re working on.
Just ask me!
Some things you have to consider before starting this process:
- You might want to back up your database in case you press that wrong button
- refer to the WordPress site restoration post to see how to back up your database
- You’re going to access your web host’s cPanel and search in phpMyAdmin
Where to Start
Log into your web host cPanel controls. Under Databases, go into phpMyAdmin.
I had to then login into another panel by inserting my web host’s username and password.
I noticed that I had several databases and this is because I have several blogs hosted on the same web host. Just click through and find the database that is associated with the blog you want to extract comments out of.
Then look for the string wp_comments from the same section which is now expanded to show you more choices from the database you have chosen. You’ll notice that the number of comments and pings will be shown for you as well.
I wish I had installed No Self Pings earlier so that I won’t have to see so much of my own pings which as you can see showed up here. However, there’s no way around this as far as I know. On the other hand, if you know how to do this, let me know in the comments below.
Now, from the top of the screen, there’s one of two things you can do to sort your results so that you’re only seeing the comments you’ve approved and not the spam which was part of the list:
- Browse, Sort key by: comment_approved (Ascending)
- Search, go below to expand on Options, highlight the sections below, check DISTINCT and hit GO
Altogether I had about 900+ comments and that includes self-pings and trackbacks. So how do I now decrease the number that it’s more specific without duplicates? Choose the second option. When I did this, it decreased my results to about 400+ entries.
To go further, if you’re going to use an autoresponder to plug in these results, the duplicates can be disregarded from the Excel file you’ll export. Even though I sorted my comments to specifically eliminate any duplications, sometimes the IPs can come from other computers.
Exporting the Results
From the search results above, if you like what you see, just go ahead and click on Export from the top of the screen. Most likely you’ll want to edit your results further, so there’s a whole slew of choices to choose from. Find the one that best suits you. For myself, I’m going to export the search into a Microsoft Excel 2000 file.
The list will then populate and be ready for you to save to your hard drive.
You can now delete any entries you don’t want. However, as I mentioned before, if you’re going to insert this list into your autoresponder, it should eliminate any pings, trackbacks and duplicates.
If you’ve followed option 2 from the steps above, you’ll see the entries on your Excel file for:
- commentator’s name
- commentator’s e-mail address
- commentator’s IP
If you’ve followed option 1, you’re going to see every string – name, e-mail, URL, IP, etc.
For Individual Posts
Let’s say you want to extract the e-mail addresses from a specific post. What you’ll need is an extra few steps. However, it’s nothing out of the ordinary from the above.
From option 2, you’re going to insert a number associated with the post of your choice. Now, this can be a bit tricky if you have permalinks because you can’t see the ID of the post looking at your blog from the outside. You have to log into your WordPress Dashboard, go to Posts and put the mouse over the post you want to pull the e-mails out of.
My favorite way of finding the post number is through hoovering your mouse over the number of comments you have on the post. This will typically show you a 4 digit number that you will reference to.
For example, you should see something like this:
The last 4 digit is the number you’ll use to insert into your database to search.
OK, going back to phpMyAdmin, option 2, below in the Search, Options, where it says
Or Add search conditions (body of the "where" clause):
Input in comment_post_id = xxxx where the x’s are the 4 digit of the post you’ve just found. In my case, it’s 6339 as seen below.
You should now see the new search populated for you. Now, just Export and proceed to saving the search to your preferred file and destination. That’s it!
The result is nothing but perfect. I’ve noticed that there are still pings and trackbacks on the exported list. Furthermore, you need to go over the file yourself to clean up all the unnecessary duplicates. So even though it’s possible, it’s far from perfect.
My list started with 900+ possible choices. It then was cleanse down to 400+. I think if I go over it to clean it a few more times, the average would be 150+. That sounds realistic but it’ll take some time.
What are your thoughts on extracting your commentator’s emails?
IMAGE: World and Email Symbol by renjith krishnan