[PHPLIST] export a list of bounced mails to a CSV file
[PHPLIST] export a list of bounced mails to a CSV file
10
Oct 11

phplist v2.10.10

among other systems we're using PHP List to process our customers' direct mailings. every now and then customers ask for a list of recipients that a certain EDM could not be delivered to.

phplist allows you to delete recipients with malformed email adresses or gives you a (very ugly) list of undelivered mails for your recipients.
this list is not of much use as it shows only the database ID of a certain user, but not his name or email address. in order to present our customers with a nice excel list we'd have to click on every bounced user-id and copy/paste their email address and names.

you could probably train a monkey to do this or you could ask your local database hacker to write a query like the following for this task:

select email, tattr.value
from
phplist_user_message_bounce as tbounce,
phplist_user_user as tuser,
phplist_user_user_attribute as tattr
where
tbounce.user = tuser.id and
tattr.attributeid = 1 and
tattr.userid = tuser.id and
tbounce.message = 175

in order to save the results to a csv file (which can easily be converted to a xls file) you'd add some more code to the query

select email, tattr.value
from
phplist_user_message_bounce as tbounce,
phplist_user_user as tuser,
phplist_user_user_attribute as tattr
where
tbounce.user = tuser.id and
tattr.attributeid = 1 and
tattr.userid = tuser.id and
tbounce.message = 175

into outfile '/tmp/your_lovely_list_of_bounces.csv'
-> fields terminated by '\t'
-> enclosed by '"'
-> lines terminated by '\n'

make sure you're selecting a directory with write access for the user your database is running on (eg. mysql:mysql). using /tmp/ is probably a good idea.

good night