Simplenews is a quick and easy way to set up and manage newsletters on Drupal, but if you add it to a site which already has users, you might want a way to quickly subscribe all your users to your newsletter.
Rather than click through all users and manually subscribe them - which can be a little bit boring if you have thousands of users - you can run a few SQL queries to mass-subscribe everyone in one go.
Each user that is subscribed to ANY newsletter has an entry in the simplenews_subscriptions table, which contains their user id, their email address and an assigned subscription id. In addition to that, a user has a discrete entry in the simplenews_snid_tid table for each separate newsletter they're subscribed to.
The latter basically links the unique ID from the subscriptions table to the term ID from the Newsletter taxonomy.
So what I do first is select the uid and email address for all authenticated users (the anonymous user has uid 0, so I select only users with a higher uid) and insert those into the simplenews_subscriptions table.
INSERT INTO simplenews_subscriptions(activated,mail,uid) SELECT 1,mail,uid FROM users WHERE uid > 0 ORDER BY uid ASC;
This automatically assigns a snid (subscription id) to each entry. You'll notice that rather than selecting a column value into the activated field, I just set it to 1.
Next, I need to find the taxonomy term ID (tid) for the particular newsletter to which I want to subscribe all users. You can find this either by listing the terms in the Newsletter vocabulary or by listing all newsletters. In both cases the term id will be shown in your web browser's status bar if you hover the mouse over the edit link:
- http://example.com/admin/content/taxonomy/edit/term/42?destination=admin/content/taxonomy/12
- http://example.com/admin/content/simplenews/types/edit/42
On my Drupal site the tid is 42, so that is the newsletter number I want to subscribe my users to. I need to refer to them via the automatically assigned snid from the simplenews_subscriptions table, so I select that and insert it directory into the simplenews_snid_tid table, together with the correct tid.
INSERT INTO simplenews_snid_tid(snid,tid) SELECT snid,42 FROM simplenews_subscriptions;
... and now all users on my site are subscribed to the newsletter with tid 42.
