Living in a world of GUIs (Graphical User Interface) many people are no longer aware about what SQL can do. Recently a friend asked me about, is it possible to extract the email adresses of my registered user out of my Joomla? Sure it is!
It is not even difficult and the idea behind was to load them into some email list to send out season greeting to all subscribers. To keep it simple here I will explain it by using a more common wordpress example, but in principle it can be done for any data the same way.
Most email clients are able to import a CSV (Comma separated List of Values) File by default, as also Excel or Access will ‘eat’ them without problems.
Now we have to invoke the MySQL Command Line Client or any other tool which enables us to execute a SQL Statement hopefully we will ending up with such a prompt:
mysql>
There we first tell MySQL to work with the specific database in question.
mysql> use DATABASE (e.q use wordpress)
With the command desc we can get a field description of our table.
mysql> desc TABLENAME (e.q. desc wp_users)
Knowing about the table structure now, with select we can extract the data wished:
mysql> select user_nicename as Name,user_email as Email from wp_users;
+——-+———————+
| Name | Email |
+——-+———————+
| admin | admin@localhost.com |
+——-+———————+
1 row in set (0.00 sec)
Seeing the output and being sure about the result will match our needs we are now finally going to spool the query result into a file:
mysql> select user_nicename,user_email into outfile ‘c:/temp/u_emails.csv’ fields terminated by ‘;’ from wp_users;
Query OK, 1 row affected (0.01 sec)
Content of “c:/temp/u_email.csv”:
admin;admin@localhost.com
or as variation:
mysql> select user_nicename,user_email into outfile ‘c:/temp/u_emails.txt’ fields enclosed by ‘”‘ terminated by ‘;’ from wp_users;
Query OK, 1 row affected (0.01 sec)
Content of “c:/temp/u_email.csv”:
“admin”;”admin@localhost.com”
And now it should be easy for you to import all emails addresses within your email client’s address book.