back to phpList.org

Export subscribers doesn't work


#1

The feature doesn’t work for all subscribers, as well for subscribers in particular list.

After clicking on “Export”, the page says “All done” and shows only one subscriber, looks like the list was cut off.


#2

phpList - latest version, updated Common plugin - the same story, just refuses to export subscribers from any list as well as all database,

Can anybody help?

Another issue how to select, say, all yahoo.com subscribers. I can select them directly in the database, but it leaves a lot of additional info saved in other tables.


#3

Hi, it sounds to me like you have got it set for

When they signed up with today’s date in the dropdown.

Try clicking Any date. Does that fix it?


#4

Thanks, Anna.

Just checked once more to be sure - still the same.

It says 'All done" and then what looks like the list that was cut off around the second line appears below

(As a new user I cannot upload an image)


#5

I threw this together so you can export from any of your lists.
NOTE: It does not delete the export file from the server when you’re done.

  1. Copy the code to a new file (something.php).
  2. Edit lines 4-8 using your data.
  3. Upload it to your server.
  4. Use your browser to visit the page and…
    ENJOY

WHAT A BUMMER… I Can’t seem to paste the code without it going straight to heck
The probable cause is that it’s PHP, JS, and HTML… Sorry.
Maybe somebody can help me with posting this?


#6

Ok, can you make some screenshots of what you see/do - and then if I can’t sort it we make a bug report :slight_smile:


#7

I’ve made a screenshot and tried to attach it, but as a new member I’m not allowed to do that.


#8

A few modifications done.
Same info as above
Edit Lines 5-9 using your data.
Save the file as export.php.
Upload it to your server.
Use a browser to visit the page.

<?php
//    EXPORTS CSV from any list

session_start();
$servername = "mydomain.com";    //ENTER YOUR DOMAIN NAME
$username = "mydbusername";        //ENTER YOUR DATABASE USERNAME
$password = "mydbpassword";        //ENTER YOUR DATABASE PASSWORD
$dbname = "mydbname";            //ENTER YOUR DATABASE NAME
$dbprefix = "phplist";            //REPLACE WITH YOUR DATABASE PREFIX IF DIFFERENT THAN DEFAULT
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (is_array($_SESSION['filesarray'])) {
    $listarray = $_SESSION['listarray'];
    $filesarray = $_SESSION['filesarray'];
} else {
    $listarray = array();
    $filesarray = array();
};

function getlists(){
    global $dbprefix, $conn, $filesarray, $listarray;
    if (!is_array($_SESSION['listarray'])) {
        if (!$conn) {
            die("Connection failed: " . mysqli_connect_error());
        } else {
            $sql = "SELECT id, name FROM ".$dbprefix."_list ORDER BY ".$dbprefix."_list.name ASC;";
            if ($result = mysqli_query($conn, $sql)) {
                while ($row = mysqli_fetch_assoc($result)) {
                    array_push($listarray, $row['id']);
                    echo $row['id']."<br />";
                    array_push($listarray, $row['name']);
                    $fname = (strtolower(preg_replace("#\s#", "_", $row['name'])).".csv");
                    array_push($filesarray, "$fname");
                };
            };
            mysqli_close($conn);
        };
    } else {
        $listarray = $_SESSION['listarray'];
        $filesarray = $_SESSION['filesarray'];
    };
    $lsize = (count($listarray)-1);
    for ($i = 0; $i <= $lsize; $i++) {
        echo "\t\t\t\t\t\t\t\t\t".'<option value = "'.$listarray[$i].'">';
        $i++;
        echo $listarray[$i].'</option>'."\n";
    };
    $_SESSION['listarray'] = $listarray;
    $_SESSION['filesarray'] = $filesarray;
};

function getfiles(){
    global $filesarray;
    foreach ($filesarray as $k => $v){
        if ((file_exists("$v")) && (preg_match("#\.csv$#", $v))){
            echo "\t\t\t\t\t\t\t\t".'<br /><a href="'.$v.'" download="'.$v.'">DOWNLOAD</a> '.$v."\n";
        };
    };
};

function delfile($expunge){
    if (file_exists("$expunge")) {unlink("$expunge");};
};

if ($_POST){
    $data = $_POST;
    $query = "SELECT name FROM ".$dbprefix."_list WHERE id = '".$data['xlist']."';";
    if ($result = mysqli_query($conn, $query)) {
        $listname = implode(mysqli_fetch_assoc($result));
    };
    $sql = "SELECT ".$dbprefix."_user_user.email";
    $heading = "Email Addy";
    if (array_key_exists('id', $data)){
        $sql .= ", ".$dbprefix."_list.id AS List_ID";
        $heading .= ",List ID";
    };
    if (array_key_exists('confirmed', $data)){
        $sql .= ", ".$dbprefix."_user_user.confirmed";
        $heading .= ",Confirmed";
    };
    if (array_key_exists('blacklisted', $data)){
        $sql .= ", ".$dbprefix."_user_user.blacklisted";
        $heading .= ",Blacklisted";
    };
    if (array_key_exists('entered', $data)){
        $sql .= ", ".$dbprefix."_user_user.entered";
        $heading .= ",Entered";
    };
    if (array_key_exists('modified', $data)){
        $sql .= ", ".$dbprefix."_user_user.modified";
        $heading .= ",Modified";
    };
    $sql .= " FROM ".$dbname.".".$dbprefix."_listuser";
    $sql .= " INNER JOIN ".$dbname.".".$dbprefix."_list ON (".$dbprefix."_listuser.listid = ".$dbprefix."_list.id)";
    $sql .= " INNER JOIN ".$dbname.".".$dbprefix."_user_user ON (".$dbprefix."_user_user.id = ".$dbprefix."_listuser.userid)";
    $sql .= " WHERE ".$dbprefix."_list.id = '".$data['xlist']."'";
    switch ($data['xconf']) {
        case "yes": $sql .= " AND ".$dbprefix."_user_user.confirmed ='1'"; break;
        case "no": $sql .= " AND ".$dbprefix."_user_user.confirmed ='0'"; break;
        default: $sql .= "";
    };
    $sql .= ";";
    $filename = (strtolower(preg_replace("#\s#", "_", $listname)).".csv");
    if (file_exists("$filename")) {unlink("$filename");};
    $xfile = fopen("$filename","a");
    fwrite($xfile, "$heading\n");
    $result = mysqli_query($conn, $sql);
    while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
        fputcsv($xfile, $row);
    };
mysqli_close($conn);
fclose($xfile);
};
?>
<html>
<head>
    <meta charset="utf-8">
    <meta name="keywords" content="">
    <title>PHPLIST EXPORT TOOL</title>
    <script language="javascript" type="text/javascript">
    <!--
        function CheckForm(form){
            var message = ""
            if (!form.xlist.value){ message += "- SELECT A List to Export;\n"; }
            if (message) {
                alert(message);
                return false;
            } else {
                return true;
            }
        }
    //-->
    </script>
</head>
<body>
<p style="text-align: center;">
    <table width="" cellspacing="0" cellpadding="10" border="1"><tbody>
        <th colspan = "2">EXPORT TOOL</th>
        <tr>
            <td><h3>CHOOSE<br />YOUR<br />EXPORT<br />CRITERIA</h3></td>
            <td>
                <form method="post" OnSubmit="return CheckForm(this)" action="export.php">
                    <table width="" cellspacing="0" cellpadding="10" border="1"><tbody>
                        <tr>
                            <td>Select the list you want to export:
                                <select name="xlist">
                                    <option value = "" SELECTED>* Select *</option><?php echo "\n"; getlists(); ?>
                                </select>
                            </td>
                        </tr>
                        <tr>
                            <td>Confirmed?<br /><input type="radio" value="yes" name="xconf"> Yes &nbsp;&nbsp;<input type="radio" value="no" name="xconf"> No &nbsp;&nbsp;<input type="radio" value="both" name="xconf" checked="checked"> Both</td>
                        </tr>
                        <tr>
                            <td>
                                Select Fields to export<br />
                                <input type="checkbox" value="id" name="id"> ID &nbsp;&nbsp;
                                <input type="checkbox" value="email" name="email" checked="checked"> Email &nbsp;&nbsp;
                                <input type="checkbox" value="confirmed" name="confirmed"> Confirmed<br />
                                <input type="checkbox" value="blacklisted" name="blacklisted"> Blacklisted &nbsp;&nbsp;
                                <input type="checkbox" value="entered" name="entered"> Entered &nbsp;&nbsp;
                                <input type="checkbox" value="modified" name="modified"> Modified
                            </td>
                        </tr>
                        <tr>
                            <td><input type="reset" value="Reset" /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<input type="submit" name="export" value="EXPORT"></td>
                        </tr>
                        <tr>
                            <td><strong>Exported Lists Available for Download:</strong><?php echo "\n"; getfiles(); ?>
                            </td>
                        </tr>
                    </tbody></table>
                </form>
            </td>
        </tr>
    </tbody></table>
</p>
</body>
</html>

#9

Thank you very much!

I will have time to implement it tonight and will lit you know


#10

Ok, try adding screenshots now


Exporting subscribers in one list
#11

Thanks a lot!

Script works like a dream!


#12

I’m glad you like it.
Just remember to remove it from your server.
You don’t want Google to index it or some jabroney to find it.


#13

Thank very much, you are the best


#14

Hi NYChris,
your solution works like a charme!
I just have one more question:
I’ve added other fields to export, like the region, nation, phone number and different others.
I’ve tried to implement the script adding the fields just copying the last line

if (array_key_exists(‘modified’, $data)){
$sql .= “, “.$dbprefix.”_user_user.modified”;
$heading .= “,Modified”;

I’ve modified the dbprefix with “_user attribute” thinking it was the way but I’m wrong 'cause the result is a blank page…
I’m a php dumb… could you please tell me how to correctly implement the script?
Thank you!


#15

Hi there!
Could you please help me with my little “problem”?
Thank you very much! :grinning:


#16

That’s not quite so simple.
The added fields (attributes) create additional tables.
You need phplist_user_user.id to get phplist_user_user_attribute.attributeid and phplist_user_user_attribute.val for each record that phplist_user_user.id = phplist_user_user_attribute.userid.
Then you need to use phplist_user_user_attribute.id to get phplist_user_attribute.name and phplist_user_attribute.tablename.
Then you use phplist_user_attribute.tablename and modify that result by prepending phplist_listattr_ to it.
(For Example usastates would become phplist_listattr_usastates)
Then you need to look in those tables for where the id = phplist_user_user_attribute.id from a few lookups ago.
Coding it would start something like this:
SELECT
phplist_user_user.email AS Email
, phplist_user_attribute.tablename
, phplist_listattr_usastates.name AS USA_State
FROM
$dbname.phplist_user_user_attribute
INNER JOIN $dbname.phplist_listattr_usastates
ON (phplist_user_user_attribute.value = phplist_listattr_usastates.id)
INNER JOIN $dbname.phplist_user_attribute
ON (phplist_user_user_attribute.attributeid = phplist_user_attribute.id)
INNER JOIN $dbname.phplist_user_user
ON (phplist_user_user.id = phplist_user_user_attribute.userid);


#17

Thanks a lot NYChris! I thought it was more simple… I will give it a try!