export phpList subscribers via sql in mysql database

Question

For some reason, I am unable to export a table of subscribers from my phpList (ver. 3.0.6) admin pages. I've searched on the web, and several others have had this problem but no workarounds have been posted. As a workaround, I would like to query the mySQL database directly to retrieve a similar table of subscribers. But I need help with the SQL command. Note that I don't want to export or backup the mySQL database, I want to query it in the same way that the "export subscribers" button is supposed to do in the phpList admin pages.

In brief, I have two tables to query. The first table, user contains an ID and email for every subscriber. For example:

id | email
1 | [email protected]
2 | [email protected]

The second table, user_attribute contains a userid, attributeid, and value. Note in the example below that userid 1 has values for all three possible attributes, while userid's 2 and 3 are either missing one or more of the three attributeid's, or have blank values for some.

userid | attributeid | value
1 | 1 | 1
1 | 2 | 4
1 | 3 | 6
2 | 1 | 3
2 | 3 | 
3 | 1 | 4

I would like to execute a SQL statement that would produce a row of output for each id/email that would look like this (using id 3 as an example):

id | email | attribute1 | attribute2 | attribute3
3 | [email protected] |  4 | "" | "" |               

Can someone suggest SQL query language that could accomplish this task?

A related query I would like to run is to find all id/email that do not have a value for attribute3. In the example above, this would be id's 2 and 3. Note that id 3 does not even have a blank value for attributeid3, it is simply missing.

Any help would be appreciated. John

Answer 1

I know this is a very old post, but I just had to do the same thing. Here's the query I used. Note that you'll need to modify the query based on the custom attributes you have setup. You can see I had name, city and state as shown in the AS clauses below. You'll need to map those to the attribute id. Also, the state has a table of state names that I linked to. I excluded blacklisted (unsubscribed), more than 2 bounces and unconfirmed users.

SELECT 
    users.email, 
    (SELECT value 
       FROM `phplist_user_user_attribute` attrs 
       WHERE
       attrs.userid = users.id and
       attributeid=1
    ) AS name,
    (SELECT value 
       FROM `phplist_user_user_attribute` attrs 
       WHERE
       attrs.userid = users.id and
       attributeid=3
    ) AS city,
    (SELECT st.name 
       FROM `phplist_user_user_attribute` attrs 
       LEFT JOIN `phplist_listattr_state` st
         ON attrs.value = st.id
       WHERE
       attrs.userid = users.id and
       attributeid=4
    ) AS state

FROM 
    `phplist_user_user` users
WHERE 
    users.blacklisted=0 and 
    users.bouncecount<3 and 
    users.confirmed=1
;

I hope someone finds this helpful.