Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » Bulk adding Members

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

Page: 1
Go to End
Author Topic: 2832 Views
  • Bruce
    Avatar
    Community Member
    29 Posts

    Bulk adding Members Link to this post

    Hi,
    I need to add about 250 new members from an older site to a SS site.
    I am not planning to enter them one by one through the CMS GUI.
    Do you have a script which would be useful?
    I can extract the member records into any format, but I see no "import" functions on the Silverstripe side.
    If I end up using some mysql "INSERT INTO ..." scripting, what are the *essentual* fields, I can't find much in the way of documentation of this part of the app. ... soz I don't break the database for the front-end.
    //BWS

  • Willr
    Avatar
    Forum Moderator
    5464 Posts

    Re: Bulk adding Members Link to this post

    Hmm there might be a CSV importer someones used to do this. I would just do it via mysql - look at the member table and just fill those in. For the critical fields open PHPMyAdmin on the member table and view the structure for what you need to pass through, whats optional and what has defaults.

  • Sean
    Avatar
    Forum Moderator
    921 Posts

    Re: Bulk adding Members Link to this post

    Do you have them in CSV format?

  • Bruce
    Avatar
    Community Member
    29 Posts

    Re: Bulk adding Members Link to this post

    Yeah, I have control of the database they are coming from, so I can create them in any format (from csv to sql )
    I was more concerned about the sensitivity of Silverstripe & what field *needed* to have stuff in them.
    (... again, where's the documentation!)

    //BWS

    To quote an respected engineer I worked with: "Without documentation, there is no design."

  • Bruce
    Avatar
    Community Member
    29 Posts

    Re: Bulk adding Members Link to this post

    ... after a long pause ....
    I have constructed an sql script which is valid when I import via phpmyadmin,
    but the inserted members do not appear in the security group "members" (created earlier) when viewed via the CMS.
    (even after .../db/build)
    So ... are there some *necessary* fields, or have I got something wrong in the sql
    sample here?
    How are they associated with the security groups?

    INSERT INTO `Member` (`ClassName`,`FirstName`,`Surname`,`Email`,`Password`,`Occupation`,`Country`) VALUES
    ('Member','Alan ','Todd ','Alan Todd','NULL',' Magnate','NZ'),
    ('Member','Bob ','Robb ','Bob Robb','NULL',' Associate Member','NZ');

  • Bruce
    Avatar
    Community Member
    29 Posts

    Re: Bulk adding Members Link to this post

    Ahhh ha!
    So there's an *index* file (`Group_Members`) which associates the member to the group, thus requiring another sql script.
    So if the two new members are given index 15 & 16, and the target group is index "5" in the `Group` table, then:

    INSERT INTO `Group_Members` (`ID` ,`GroupID` ,`MemberID`)VALUES
    (NULL , '5', '15'),
    (NULL , '5', '16');

    It then means somehow capturing the autoincrement values assigned to the new members from the first sql insert.

  • Sam
    Avatar
    Administrator
    679 Posts

    Re: Bulk adding Members Link to this post

    One option for determining the IDs is to set the member IDs yourself. As long as you don't choose an ID which is already taken, there's no reason why you can't bypass the auto incrementor.

    INSERT INTO Member (ID, Email) VALUES (10, 'sam@example.com')

    INSERT INTO Group_Member (MemberID, GroupID) VALUES (10, 5)

  • artlung
    Avatar
    Community Member
    10 Posts

    Re: Bulk adding Members Link to this post

    I was trying to extend the CMS to add a field to bulk add users, but my time is better spent elsewhere, so I'm doing this directly through MySQL. Once I have added the users (and all the fields I added were Email, Surname, FirstName, Password - these are the steps I take to update the database. In the code below the number 5 represents regular users:

    -- update groups to include all members, all these users will
    -- get set as User Group 5

    INSERT INTO Group_Members (MemberID,GroupID)
    SELECT ID,5
    FROM Member where ID not in (
    Select distinct MemberID from Group_Members
    )

    -- group maintenance
    -- remove leftover group member items

    DELETE FROM Group_Members WHERE MemberID NOT IN (
    Select ID from Member
    );

    -- here's how i updated the created field, and forced the
    -- password to be in plaintext

    UPDATE Member set Created=Now()
    , PasswordEncryption='none'
    , Locale='en_US'
    WHERE ISNULL(Created) ;

    2832 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.