Add new Channel ID access to multiple member groups via SQL Query

Question

I have created a new channel (in this case ID#32) on an existing EE site install, and I need to assign it to 270 existing member groups.

Rather than clicking into every single member group and manually adding the the new channel under the 'Allowed Channels' list, I was thinking it would be easier to write an SQL Query to the 'exp_channel_member_groups' table so all group_id's (that do not have a row stating the new/required channel_id #32 number to have a row created etc, so you would have (for example) group_id 01 channel_id 32 on so on.

Can anyone advise on the correct EE/SQL scripting I would need in order to get this to happen?

Many thanks in advance for any help on this :-)

Answer 1

If you have access to a SQL Utility like Sequel Pro and a code editor it is pretty easy to do.

First you need to get a list of the groups that don't have the channel_id set.

Using the SQL Query tab of your utility enter a command like this (replace "7" with the channel_id of the one you've added):

SELECT DISTINCT group_id FROM exp_channel_member_groups WHERE channel_id <> 7;

This will give you a list of group_id's in the output area of the Query window.

Select the list of group_id's and copy them.

Now create a bit of SQL in a code editor (if it is any good you can paste in the list of group_ids and build the VALUES rows in one hit) to get some code that looks a bit like this:

INSERT INTO `exp_channel_member_groups` (`group_id`, `channel_id`)
VALUES
    (Your_group_id, channel_id_from_list_1),
    (Your_group_id, channel_id_from_list_2),
    (Your_group_id, channel_id_from_list_3),
    .... etc ... 
    (Your_group_id, channel_id_from_list_last);

Run that in your SQL utility query area and you'll get the updated data into the table.

HTH