WordPress is very popular open-source software for blogging. The database of this CMS contains in particular the comments left by Internet users when they comment on articles. It can be very interesting to retrieve the email list of people who left a message. This can be useful for subscribing to a newsletter (with their consent) or for sending any email, for example to notify if the blog changes address.
This article offers several SQL queries to export the email addresses left when an Internet user writes a comment. You should adapt the queries if you have used a specific prefix for your tables. You can make these requests in the interface of PhpMyAdmin of your host.
Export email addresses
The easiest solution to export the list of email addresses is to perform the following SQL query.
SELECT DISTINCT comment_author_email FROM wp_comments WHERE comment_author_email <> '';
Once the request has been launched in PhpMyAdmin, you can export the results using the “export” link which is located below the results.
To know : be careful, some people use fictitious or disposable email addresses to avoid receiving spam or unsolicited marketing offers. If you don’t have a lot of results you can browse the list to recognize emails that you think are made up.
Export emails with name and number of comments
The previous request made it possible to retrieve exclusively email addresses, but for sending a newsletter it may be wise to export the name or nickname associated with the email address. So the email can be personalized.
It can also be interesting to know if a user has left a lot of posts on your blog. This can be handy for sending a message to the most loyal readers.
The query below lists the email addresses with the associated name and the number of comments left on the blog with that email address.
SELECT MIN(comment_author), comment_author_email, COUNT(*) AS nbr_commentaires FROM wp_comments WHERE comment_author_email <> '' GROUP BY comment_author_email ORDER BY nbr_commentaires DESC
Warning : if the user has used the email address several times but has indicated several nicknames, this request will display only one name among those he was able to use.
Export emails and exclude certain email addresses
Until now, SQL queries allowed you to list all emails, but sometimes you have to filter to avoid having your own email address or those of the blog’s editors.
The query below presents 2 ways to filter certain results. It’s up to you to adapt to meet your needs.
SELECT DISTINCT comment_author_email FROM wp_comments WHERE comment_author_email <> '' AND comment_author_email <> 'email@example.com' -- filtrer l'email indiqué AND comment_author_email NOT LIKE '%@example.com' -- filtrer les emails se terminant par '@example.com'
Make good use of these requests and especially try to respect the law and your readers when you have collected the email addresses.
To note : some nicknames can be in the format “Name @ Keyword”To match the format of the KeywordLuv plugin. If you want to keep just the name and remove the keywords, you will need to remove the keywords from the KeywordLuv comments first.