Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Archive /

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

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

Bulk adding Members


Go to End


8 Posts   3638 Views

Avatar
Bruce

Community Member, 29 Posts

29 May 2008 at 10:47pm

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

Avatar
Willr

Forum Moderator, 5523 Posts

1 June 2008 at 1:16pm

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.

Avatar
Sean

Forum Moderator, 922 Posts

1 June 2008 at 4:39pm

Do you have them in CSV format?

Avatar
Bruce

Community Member, 29 Posts

2 June 2008 at 4:01pm

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."

Avatar
Bruce

Community Member, 29 Posts

13 July 2008 at 5:19pm

Edited: 13/07/2008 5:23pm

... 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');

Avatar
Bruce

Community Member, 29 Posts

13 July 2008 at 5:47pm

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.

Avatar
Sam

Administrator, 690 Posts

14 July 2008 at 3:53pm

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)

Avatar
artlung

Community Member, 10 Posts

4 October 2008 at 5:21am

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