We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

[How To] Bulk insert users into webuser group

    • 16194
    • 103 Posts
    Konsum Reply #1, 19 years, 7 months ago
    Hi Folks!

    I have a large no of registered web users. I want to assign all of them to a new webuser group.
    Is there a way to do that automatically?

    Modx 0.95 Latest php/mysql
      Impossible is nothing - with ModX
      • 23491 ☆ A M B ☆
      • 1,056 Posts
      pixelchutes Reply #2, 19 years, 7 months ago
      Quote from: Konsum at Mar 28, 2007, 06:35 PM

      Hi Folks!

      I have a large no of registered web users. I want to assign all of them to a new webuser group.
      Is there a way to do that automatically?

      Modx 0.95 Latest php/mysql

      If you’re comfortable with SQL, you could very easily write a simple update statement smiley Would take seconds... Run via SQLYog, PHPMyAdmin, or last resort create a module/snippet and execute code-side.

      1. Have you created the new Group yet? If not, create it! (You’ll need to know the id from the `modx_web_groups` table.)
      2. Do you want ALL Web Users assigned to this new group, or only some?
      2a. If only some, which ones?
      Quote from: "Example"

      (e.g. All users currently in groups "webgroup-A" and ""webgroup-B" I want to move to "new-webgroup-C"...)
        Mike Reid - www.pixelchutes.com
        MODx Ambassador / Contributor
        [Module] MultiMedia Manager / [Module] SiteSearch / [Snippet] DocPassword / [Plugin] EditArea / We support FoxyCart
        ________________________________
        Where every pixel matters.
        • 16194
        • 103 Posts
        Konsum Reply #3, 19 years, 6 months ago
        Okay, I’m a newbie in SQL. But I guess it would have to look something like this in phpMyAdmin:

        SELECT id
FROM modx_web_users
WHERE id > 3;

INSERT INTO modx_web_groups (webgroup,webuser)
VALUES (2, id);


        I want to add all users with an id higher than 3 into user group 2. Could you help me to correct the code above?
          Impossible is nothing - with ModX
          • 23491 ☆ A M B ☆
          • 1,056 Posts
          pixelchutes Reply #4, 19 years, 6 months ago
          Quote from: Konsum at Mar 29, 2007, 09:16 AM

          I want to add all users with an id higher than 3 into user group 2. Could you help me to correct the code above?

          (FYI, I think I had my table names mixed up...No worries!)

          Run the following SQL:
          select * from modx_webgroup_names

          Please confirm that where "id = 2" is the correct Web Group name you want them moved to.


          Once you’re sure of the proper web_group id, then you would likely need two queries: an UPDATE statement for any users already assigned to a webgroup and then an INSERT statement for those that are have not. I would recommend running both queries (in order).



          First:
          UPDATE desired web user’s assigned webgroup id:
          [This will update any webuser > 3 record in modx_web_groups with webgroup id: 2]
          update modx_web_groups wg
inner join modx_web_users wu on wg.webuser = wu.id and wu.id > 3
set wg.webgroup = '2'


          Last:
          INSERT desired web users into assigned webgroup id:
          [This will add any webuser > 3 not already in modx_web_groups into modx_web_groups with webgroup id: 2]
          insert into modx_web_groups (webgroup,webuser)
select '2' as webgroup, wu.id as webuser
from modx_web_users wu
left join modx_web_groups wg on wg.webuser = wu.id 
where wg.webuser is null and wu.id > 3



          Let me know if this helps. Enjoy! cool
            Mike Reid - www.pixelchutes.com
            MODx Ambassador / Contributor
            [Module] MultiMedia Manager / [Module] SiteSearch / [Snippet] DocPassword / [Plugin] EditArea / We support FoxyCart
            ________________________________
            Where every pixel matters.