SQL to Subscribe Users to Channels?

[cmsouth]

I've searched thru the forum and google, but have not found sql that others have posted to update the channel subscriptions of users - is there a location that others have posted sql they have used?

We have an forum for company only users and want to subscribe them to all channels. I had a developer subscribe everyone to threads, but i've learned that you need to subscribe to channels to get new topics. I can't find any doc on the table structure to do a query by trial/ error, so even that would be helpful. Thx

[delicjous]

Hi,

if you don't discover the answer by yourself I wrote you al little query which selects all users and nodes to insert it into the subsribediscussion.

It is not that easy like it sounds at first, because you have to check some things at first.

At first you have to choose which contenttypes should subscribed by all users.
If you are in phpmyadmin or your sql database you have to check the contenttypeids at the table contenttypes (depends on vB-Version). Then you could compare those ids with your nodes table and change the contenttype (s) at the following query. The publishdate selects the publishdate of all nodes installed by installed of vBulletin.
The publishdate thing only works if you have an clean fresh install which never updated --> because if vBulletn installed a new contenttype or examplenode with an update, it have a higher publishdate
Usually the css examplepage (yourdomain/special/css-examples) have an newer publishdate than the other nodes so I sort that one out by title!
So this is only a short instruction how to do it and you have to change it for your conditions!

If you want to subscribe all user to all channels than you could use the following query:
(try this select at first to see the titles you will subscribe to all users)

SELECT nodeid, title
from `vB_node` a
    inner join `vB_contenttype` b ON b.`contenttypeid` = a.`contenttypeid`
   where b.`contenttypeid` =23
    and a.title <> 'CSS Examples'
   and a.publishdate > (select publishdate from vB_node where nodeid = 1)

Subscribe ...:

insert into `vB_subscribediscussion` (userid, discussionid, emailupdate)
select s1.userid, s2.nodeid,1
from 
(select userid from vB_user) as s1
cross join 
(SELECT nodeid 
   from `vB_node` a
    inner join `vB_contenttype` b ON b.`contenttypeid` = a.`contenttypeid`
   where b.`contenttypeid` =23
     and a.title <> 'CSS Examples'
     and a.publishdate > (select publishdate from vB_node where nodeid = 1)) as s2

Hope it helps you to do it by your self!