0

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 :-)

2
  • I would think your client has a serious issue with CMS organization if you have to consider resorting to raw SQL queries to solve this. I might have something for you tomorrow, but suggest to your client some future refactoring. That sounds rough. Also, if I do throw something out there you can try, please have a throw-away dev server to test it on first.
    – jrothafer
    Dec 10, 2019 at 12:32
  • Hi jrothafer, many thanks for the feedback on this, much appreciated. To be honest it's a site I have recently inhereted so the framework/infrastructure was already developed and in place, however there are numerous tasks I would have done differenty and will suggest to re-develop over time. Many thanks in advance for SQL solution when ready and yes everything is full tested at local/staging servers before live implementation, so all covered from that aspect. Dec 10, 2019 at 16:26

1 Answer 1

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

2
  • 1
    Hi jcogs, the above solution worked a treat ans succesfully updated every member group with the required new channel ID number! I've made a full template of all the member group numbers hard coded into the SQL script provided above and all I need to do in the future is replace the existing channel ID in the script with a new one as required. Many thanks once again, this is an excellent solution! :-) Mar 21, 2020 at 0:32
  • Thanks for letting me know it worked - good news! :) Mar 21, 2020 at 0:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.