Drupal – Add all users to simplenews newsletter

The script below will add all users to your simplenews newsletter and subscribe them to the top most newsletter (if you have more that one newsletter, you can just loop through it)

This has only been tested in Drupal 5.x.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
<?php
   
    $user = "DB_USERNAME";
    $pass = "DB_PASSWORD";
    $db = "DB_NAME";

    $dbh = new PDO('mysql:host=localhost;dbname='.$db, $user, $pass);
   
   
        $sth = $dbh->prepare("
            SELECT users.name AS subscribed_mail, users.mail AS registered_users, users.uid, simplenews_subscriptions.snid, users.created
            FROM users
            LEFT JOIN simplenews_subscriptions ON users.mail = simplenews_subscriptions.mail
            UNION ALL
            SELECT users.name, simplenews_subscriptions.mail, users.uid, simplenews_subscriptions.snid, users.created
            FROM users
            RIGHT JOIN simplenews_subscriptions ON users.mail = simplenews_subscriptions.mail
        "
);
        $sth->execute();
        $row =$sth->fetchAll(PDO::FETCH_ASSOC);
       
        ?><table border="1">
            <tr>
                <th>Ref #</td>
                <th>UID</th>
                <th>Username</th>
                <th>Mail</th>
                <th>SNID</th>
                <th>Created (GMT)</th>
            </tr>
        <?php
            $notAdded = 0;
            $totalUsers = 0;
            foreach($row as $key=>$var){
                   
                    if($totalUsers % 2 == 0){
                        ?><tr style="background-color:#DDD"><?php
                    }else{
                        ?><tr><?php
                    }
                    ?>
                   
                        <td><?php echo $totalUsers+1; ?></td>
                        <td><?=$var['uid'];?></td>
                        <td><?=$var['subscribed_mail'];?></td>
                        <td><?=$var['registered_users'];?></td>
                        <td><?=$var['snid'];?></td>
                        <td><?php
                            if(!empty($var['created'])){
                                echo date("Y-m-d H:i:s", $var['created']);
                            };
                        ?></td>
                    </tr>
                    <?php
                   
                    $totalUsers++;
               
                //IF SNID IS EMPTY, IT ISN'T ADDED
                if(empty($var['snid']) && !empty($var['uid']) && !empty($var['registered_users'])){
                    $notAdded++;
                   
                    //ADD IT INTO SUBSCRIPTIONS
                    $sth = $dbh->prepare("INSERT INTO simplenews_subscriptions (mail) VALUES (?)");
                    $sth->execute(array($var['registered_users']));
                   
                   
                };
               
               
            }
        ?>
        </table><?php
       
        echo 'Number that needs to be added : '.$notAdded.'<br/>';
        echo 'Total users : '.$totalUsers.'<br/>';
       
        if($notAdded > 0){
            echo 'Added missing rows<br/>';
            $sth = $dbh->prepare("SELECT snid FROM simplenews_subscriptions");
            $sth->execute();
            $row = $sth->fetchAll(PDO::FETCH_ASSOC);
            //print_r($row);
            $news = $dbh->prepare("SELECT tid FROM simplenews_snid_tid GROUP BY tid");
            $news->execute();
            $news = $news->fetchAll(PDO::FETCH_ASSOC);
           
            if(!empty($news)){
                foreach($row as $key=>$var){
                    $sth = $dbh->prepare("INSERT INTO simplenews_snid_tid (snid,tid) VALUES (?,?)");
                    $sth->execute(array($var['snid'],$news[0]['tid']));
                }
            }
        }
   
?>

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>