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:

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.

Originally posted at http://cafuego.net/2009/12/06/simplenews-mass-subscribe on 6 Dec 2009.

Comments

Thanks so much!

You really saved my life!

Chad

Thank you very much for sharing this.
Amedeo

nice stuff

Hi..
Thanks for this great tip.

I got some sql error on second step:

INSERT INTO simplenews_snid_tid(snid,tid) SELECT snid,X FROM simplenews_subscriptions;

#1062 - Duplicate entry '1-1' for key 1

Anybody know what can be a problem?

Thanks.

It causes that you've probably already subscribed from any account to newsletters. Simply truncate the table and run the query again.

BUG FIX - It didn't work with the latest (7.x-1.0) version, because the database structure have been changed. I rewrote the queries, here they are:
INSERT INTO simplenews_subscriber(activated,mail,uid) SELECT 1,mail,uid FROM users WHERE uid > 0 ORDER BY uid ASC;

INSERT INTO simplenews_subscription(snid,tid) SELECT snid,100202 FROM simplenews_subscriber;