Thanks. My test was run on a local install (Vista, XAMPP, Evo 1.0.2) and I was executing the SQL commands from the phpMyadmin window/app. So what I posted did actually work. But adding the quotes might be the proper way?
So that’s the good news - a batch creation of users can be done via imports into the right tables. For the record, here is what I did which worked on the test setup noted above (WebLoginPE had already been installed and run before, so the "extended" database existed) :
- Created three CSV files which contained the info relevant to the three tables I wanted to edit : modx_web_users, modx_web_user_attributes and modx_web_user_attributes_extended. Here are sample entries from each of the CSV files, in their respective order :
"id","username","password","cachepwd"
1,"tempuser1","temp619415",
2,"tempuser2","temp782623",
"id","internalKey","fullname","role","email","phone","mobilephone","blocked","blockeduntil","blockedafter","logincount","lastlogin","thislogin","failedlogincount","sessionid","dob","gender","country","state","zip","fax","photo","comment"
1,1,"Member 1",0,"[email protected]","888-555-1212",,0,0,0,0,0,0,0,,0,0,,"VT","12345",,,
2,2,"Member 2, Inc.",0,"[email protected]","888-555-1212",,0,0,0,0,0,0,0,,0,0,,"VT","01234",,,
"id","internalKey","physicaladdress","mailingaddress","mailingaddress2","city","contact","category1","category2","category3","web","description","notes","keymember","membership","logolink","videolink"
1,1,"123 South Main St.","P.O. Box 123",,"Somewhere","Tom Smith","Service",,,"http://www.example1.com","Marketing and advertising to organizations. ",,0,1,,
2,2,"123 North Main St.","P.O. Box 456",,"Anywhere","Harry Smith","Manufacturing",,,"http://www.example2.com","Manufacturer of metal products. ",,0,1,,
NOTE : this install already had a webuser set up and their id was "1". So I ended up having the first two lines of the file (the header and the clashing user) ignored after I saw the the first attempt didn’t overwrite the existing entry. He could have been renumbered in the CSV though.
For importing the first CSV file here was the SQL (executed from within phpmyadmin) :
LOAD DATA LOCAL INFILE 'C:/local_folder/test_webusers.txt'
INTO TABLE modx_web_users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 2 LINES;
Then I needed to apply the MD5 function to the password. Initally I did this
UPDATE modx_web_users SET password = MD5( password );
But that also messed up the already existing user. So I had to manually reset their password in phpmyadmin. I think I could have limited the SQL command by appending something like
WHERE `modx_web_users`.`id` >1;
For importing the second set of data :
LOAD DATA LOCAL INFILE 'C:/local_folder/test_webusersattrib.txt'
INTO TABLE modx_web_user_attributes
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 2 LINES;
For the third set of data, the existing "extended" table had to be altered to have the custom fields I needed :
ALTER TABLE `modx_web_user_attributes_extended` ADD `physicaladdress` VARCHAR(255) NOT NULL, ADD
`mailingaddress` VARCHAR(255) NOT NULL, ADD `mailingaddress2` VARCHAR(255) NOT NULL, ADD `city` VARCHAR
(100) NOT NULL, ADD `contact` VARCHAR(100) NOT NULL, ADD `category1` VARCHAR(100) NOT NULL, ADD
`category2` VARCHAR(100) NOT NULL, ADD `category3` VARCHAR(100) NOT NULL, ADD `web` VARCHAR(255) NOT NULL,
ADD `description` VARCHAR(1024) NOT NULL, ADD `notes` VARCHAR(255) NOT NULL, ADD `keymember` INT(1) NOT
NULL, ADD `membership` INT(1) NOT NULL, ADD `logolink` VARCHAR(255) NOT NULL, ADD `videolink` VARCHAR
(255) NOT NULL
which then allowed this to work :
LOAD DATA LOCAL INFILE 'C:/local_folder/test_webusersattribext.txt'
INTO TABLE modx_web_user_attributes_extended
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 2 LINES;
This all allowed me to successfully log in as tempuser2 with the above noted password. And if I had indexed the id keys properly so as to avoid the clash with the existing id, I’m sure I could have logged in as tempuser1 as well.
The question now is this : are all these fields now available to WebLoginPE with only the addition of the custom fields parameter? In other words does it now know where to go and find all this info, allowing me to construct a form with fields from all three tables, without having to jump through more hoops?
At least I hope this helps others wondering how to get a bunch of users loaded into the system. And thanks again for your help, Susan!
MattC
☆ A M B ☆
