How to extract names and email addresses from your WP database

March
28

I had to do some playing around with my wordpress database when I wanted to move my install from a shared hosting to a dedicated server (which turned out to be quite easy!) and I went in a bit further to phpmyadmin to see what other things I could do.

With the current release of my new contest game, Aqua Craft, I wanted to let everyone that has visited here know about it and rather than wait for every single commenter to come back and see the post, I thought it might be easier to extract the email addresses of all commenters from the database and send them all a message. (luckily, only one person replied saying it was spam!).

I checked my wp_comments table and it had a few thousand entries, yey! I thought. I’ll send them all a message! I extracted the table into a csv file and was disappointed to see literally hundreds and hundreds of spammer email addresses in there…

wp_comments table with spam addresses

Not being a mysql database geek, I did some searching and found a way to extract all the addresses of the commenters that had been approved… here’s how…

1.) Log in to phpmyadmin
2.) select the wp_comments table on the left and click the “browse” button on comment_author_email

phpmyadmin wp_comments table

3.) Click “edit” on the mysql statment at the top of the page and change it to the following

edit mysql statement

SELECT * FROM `wp_comments` WHERE `comment_approved` = ’1′

That will show you a list of all the comments made that have been approved.

4.) Click on the “export” tab and select these options..

export approved comments

This will give you a large .csv file ready to be opened by Excel

5.) Open the file in excel which will give you this..

raw csv file

This is a lot more than we need, so delete the columns for comment id, comment content etc so you’re left with comment_author, comment_author_email and comment_author_url

6.) Your excel file should look like this…

Fixed csv file

Now there will be a lot of duplicate email addresses from multiple commenters but if you have a decent newsletter or mass email program, you should be able to import this file and let it filter out all the dupes and discard the trackbacks and pings.

Easy peasy! I ended up with a few hundred email addresses who I could tell about my latest contest.. awesome…

Code, PHP, Wordpress


14 Comments zu “How to extract names and email addresses from your WP database”

  1. Rhys
    28.03.08 1:42 am

    So that’s how you did it, you cheeky sod :P

    Am going to reply to you, when I have enough time to forumlate a proper reply to you :)

    Rhys’s last blog post..Music Sounds Better With You – Three Most Embarrassing Songs on my Playlist

    #1

  2. Andy Bailey
    28.03.08 10:29 am

    Rhys: ok, I hope “enough time to formulate a proper reply” is not a euphemism for “I’m preparing the legal documents to sue your arse” :-/

    #2

  3. Victoria's Secret Models
    29.03.08 5:19 pm

    Excellent tutorial! Bookmarked ;)

    #3

  4. Bobby Revell
    30.03.08 10:47 am

    Very cool idea! I’ve messed around in phpmyadmin for a while now but am afraid to screw things up. I’ve thought about trying to get a email list before – this is a great way. Thanks Andy:)

    Bobby Revell’s last blog post..Advertising On Your Body and Soul

    #4

  5. Andy Bailey
    30.03.08 10:53 am

    bobby: no problem mate, glad I could help!

    #5

  6. Bobby’s Batch #12 - My Date With a Deceptive Lying Witch
    07.04.08 4:04 am

    [...] How to extract names and email addresses from your WP database: Andy Bailey’s very useful article about how to derive names and email addresses from your WordPress database. It’s complete in detail and easy to understand. [...]

    #6

  7. how do i extract email addresses
    17.05.08 8:15 am

    [...] playing around with my wordpress database when I wanted to move my install from a shared hosting …http://www.fiddyp.co.uk/how-to-extract-names-and-email-addresses-from-your-wp-database/trackback/Order Page for Extract Email Addresses From Multiple Web Sites …Extract email addresses From [...]

    #7

  8. paul rice
    26.05.08 8:09 am

    well written message,well done

    #8

  9. phpmyadmin list email addresses
    21.06.08 3:19 pm

    [...] now but am afraid to screw things up. I??ve thought about trying to get a email list before …http://www.fiddyp.co.uk/how-to-extract-names-and-email-addresses-from-your-wp-database/what is the best database software for you? please indicate why and …its free and with phpmyadmin [...]

    #9

  10. Gert Hough
    25.06.08 11:59 pm

    You’ve got balls Andy!

    I would not even think of doing that. Just the thought of a spam complaint will send me running. I have come about a related blog post about different plugins that are available that can handle one’s list building from your blog. And I must say that I have been impressed. There are such a lot that I just there and then thought – may be later – and closed the window.

    I use aweber and I must say that is over there – at the top. Since I currently have bandwidth problems to sort out I sort of hang around until I can figure out my problems…

    In the mean time – how dedicated did you go and why?

    Gert Houghs last blog post..Business email list for sale – 5000 email addresses cheap cheap

    #10

  11. Leo
    18.10.08 7:49 pm

    Phpmyadmin Version ?

    #11

  12. Tellman's Free Listbuilding Software
    31.01.09 1:42 pm

    [...] How to extract names and email addresses from your WP database – I had to do some playing around with my wordpress database when I wanted to move my install from a shared hosting to a dedicated server (which turned out to be quite easy!) and I went in a bit further to phpmyadmin to see what other … [...]

    #12

  13. cet
    09.05.09 9:46 pm

    Perfect Sites.

    #13

  14. eyenetws
    28.05.09 9:46 pm

    very short & sweet ..I tried it and finally i made it very simple way

    thanks for the awesome tutorial.

    #14