mySQL query to subscribe all current users?
Ashford - August 16, 2008 - 15:53
| Project: | Subscriptions |
| Version: | 6.x-1.x-dev |
| Component: | Documentation |
| Category: | support request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Description
With my Drupal upgrade from 4.7 to 5, I noted the Notify.module was deprecated and replaced by the Subscription.module. Our organization's members are low-tech computer users. They depend on the volunteer webmaster, me, to automate most of their settings.
Is there a mySQL query I could run in phpMyAdmin to automatically subscribe all current Users to send Interval of daily? Would the query statement need to include the CCK content_types A,B,C for each uid?
#1
The exact query would depend on who you want subscribed and how (to all posts, to posts of certain node types, etc.).
What follows is not what you requested, but could equally be documented somewhere. The following script ensures that every one is subscribed to nodes they have posted, and to nodes where they have commented:
update subscriptions by node authors:
<?php
$result = db_query('SELECT * FROM {node} WHERE uid != 1');
while (
$node = db_fetch_object($result)) {
$result2 = db_query("SELECT * FROM {subscriptions} WHERE module = 'node' AND field = 'nid' AND value = %d AND recipient_uid = %d", $node->nid, $node->uid);
if (!mysql_num_rows($result2)) {
db_query("INSERT INTO {subscriptions} (module, field, value, recipient_uid, send_interval, author_uid, send_updates, send_comments)
VALUES ('node', 'nid', %d, %d, 1, -1, 1, 1)", $node->nid, $node->uid);
}
}
?>
Update subscription by comments:
<?php
$result = db_query('SELECT * FROM {comments} WHERE uid != 1');
while (
$comment = db_fetch_object($result)) {
$result2 = db_query("SELECT * FROM {subscriptions} WHERE module = 'node' AND field = 'nid' AND value = %d AND recipient_uid = %d", $comment->nid, $comment->uid);
if (!mysql_num_rows($result2)) {
db_query("INSERT INTO {subscriptions} (module, field, value, recipient_uid, send_interval, author_uid, send_updates, send_comments)
VALUES ('node', 'nid', %d, %d, 1, -1, 1, 1)", $comment->nid, $comment->uid);
}
}
?>
#2
@Ashford: Go to admin/settings/subscriptions/userdefaults to set your preferred default interval.
Create a subscription and then look inside the {subscriptions} table to see what the corresponding entry looks like. Create more of those for your users.
@beginner: Iterating over all nodes or comments has the potential to time out, leaving the database in an indetermined state. An operation as you suggest should use a single SELECT with a JOIN, so that it can be restarted and will pick up where it left off.
#3
I am looking to subscribe all users of a certain role to a Category (specific Forums) defined on the User defaults settings. With the Custom subscriptions module, this is possible for the Notifications, but not for the Subscriptions module. Is there a way to do it?
#4
No, see above.
#5
I don't understand. It *is* possible using Notifications, so why it isn't with your module?
#6
No such functionality is implemented. If you want to implement it, you're welcome to do so, in a separate add-on module. I will help by answering specific questions and reviewing your code.
You might want to look through the issues queue for previous issues where this has been discussed.