By enthusaroo on

Hey Drupal,

I've been building this Drupal web site for a non-profit organization over the last month. One problem that I just realized is that even though under User Settings "Require e-mail verification when a visitor creates an account" is unchecked... Drupal still requires that an e-mail address be listed to add a new account.

The reason this is a problem is because we the administrator to be able to add about 200 new accounts for the 200 volunteers at the organization. However, for most of the volunteers we do not have their e-mail addresses.

If possible, we must find a way to either:
A) Disable an e-mail address from being required when creating a new account
OR
B) Allow the same e-mail address to be used twice.

It would be OK to sign up all 200 users using the administrator e-mail, e.g: admin@ngo.com. But when we try to do this Drupal says that the e-mail address is already registered.

Please, please, please help with this issue. We're completely stuck here and cannot proceed...

⋅ Categories: Drupal 5.x

Comments

gareth_w’s picture

gareth_w commented

Can you not add all the users straight into the database, without going through the account sign-up stage?

Should then be easy to write a script (or even Excel Macro) that adds an e-mail address of user_0001 etc... into the SQL query for each new user.

If you're not up with SQL it's not as scary as it sounds - especially if you have a tool such as phpmyadmin to administer the db with.

Gareth

ianr’s picture

ianr commented

I agree, just do it with anything that can talk sql, ie you could have all their details in spread sheet and just upload them directly into the db.

enthusaroo’s picture

enthusaroo commented

Could you please give me an example of how I could make such a spreadsheet file as you described here?

gareth_w’s picture

gareth_w commented

Just the standard user details in Drupal, and are you OK using Excel, and can you get to phpMyAdmin for your database?

I need to write this table for myself, and I can try and post something tomorrow if that's OK by you.

Gareth

enthusaroo’s picture

enthusaroo commented

Dear Gareth,

I just got back to work. The organization I'm volunteering for is in China. I think we might have a time difference. :p

>>Just the standard user details in Drupal,

Yes, that's correct. Everything is standard.

>>and are you OK using Excel,

To be honest, I haven't used speadsheets very often in my life. That's why I may need some clarification (if possible) about how and what data to enter into the spreadsheet.

>>and can you get to phpMyAdmin for your >>database?

Yes, I have root access. I'm familiar with working with phpMyAdmin, but I'm by no means an expert. That's why I need more help with this issue.

>>I need to write this table for myself, and I can try and post something tomorrow if that's OK by you.

That is really cool of you. Thank you! I really just need to see an example of what you're talking about, and then I should be able to figure it out from there.

Cheers!

gareth_w’s picture

gareth_w commented

It's pretty basic, but I *think* it does the job. I got the code from going into phpmyadmin, navigating to the table, inserting a row and copying the code it produced.

To use, you first need to enter the start user number in Cell A2. This is the number for the next available row in your users table. No idea what will happen if you leave gaps.

You need to enter your domain name in cell D2 for the email addresses to be created - or rewrite the formula in D6 to have a single email address for all accounts.

The password lives in cell C6. The password is never stored in the database, just a hash of it. You either need to generate the hash by hand (don't know how to do this) or use a nonsense value and have the user request a new password to get this to work.

Finally, set the db name in table U2. You can get this fairly easily from phpmyadmin.

The SQL now lives in cells U6 and V6. There are two queries, one to set the user table and the next to set the user role - I believe 2 equates to authenticated user. If you need to build more fields on the profile, thats another query, but as that is custom to your site, it's beyond the scope here.

I'm struggling to upload the file to this site, but you should be able to grab it from here:

http://www.bkks.org.uk/downloads/drupal_user_db_SQL_generator.xls

Hope this at least points you in the right direction.

enthusaroo’s picture

enthusaroo commented

We had a long weekend, so just now back to work on the web site.

Dear Gareth: That file looks great! I apologize though, because I thought it would be easy to use as an example spreadsheet as a way of doing it myself. But I still have some questions...

I have some newbie questions regarding the spreadsheet file, because I'm afraid that I'll do something wrong and mess up the database. :p

1) In the "my domain" column does it need to be exact? Our Drupal site is on a subdomain, plus we recently moved it from a subdirectory of the subdomain to the root directory of the subdomain. I'm not sure if this makes a difference or not.

2) "User role" is set to "2" in your example. Does this make them authenticated users?

3) Under the SQL column I see: "INSERT INTO 'username_drupal52'.'users'..." = Does the "drupal52" indicate that you're using drupal5.2? Do I need to change that to 5.5 since I'm using 5.5?

gareth_w’s picture

gareth_w commented

1) In the "my domain" column does it need to be exact? Our Drupal site is on a subdomain, plus we recently moved it from a subdirectory of the subdomain to the root directory of the subdomain. I'm not sure if this makes a difference or not.

Not especially - but this is what it uses to create the addresses and it can be anything you want - just think how this is carried through.

2) "User role" is set to "2" in your example. Does this make them authenticated users?

I believe so - you may want to check an authenticated user in your setup (it could potentially be different) to double check this.

3) Under the SQL column I see: "INSERT INTO 'username_drupal52'.'users'..." = Does the "drupal52" indicate that you're using drupal5.2? Do I need to change that to 5.5 since I'm using 5.5?

NO - this is the name of the database. I called mine 52 seeing as that it what it was being used for, going to 55 this will not change. You will need to set this to your database name, with any concatentation that your host requires.

Note: you should **ALWAYS** back up your database before using code provided by idiots on the net - like me :)

However, if you just execute one of these queries by hand, the absolute worst I believe it could do is insert an incorrect user, because:
* If the SQL is wrong, mySQL should give you an error
* it uses INSERT not UPDATE so should not overwrite an existing row.

The most sensitive setting I can think of is the starting row number - get this right and there should be no issues.

I'll try and answer more questions if you have them,
Gareth

enthusaroo’s picture

enthusaroo commented

Dear Gareth,

Thank you for your most excellent reply!

After reading your reply, I now feel like I understand perfectly everything regarding the spreadsheet file that you uploaded, and how to imput data into it.

>>I'll try and answer more questions if you have them,

Now there's just one other thing... Actually, getting it into the database. :D I'm not sure if it's due to the web host's configuration, but MySQL only will give the option to upload a .SQL format file. I tried anyways to import the .XLS file, but of course the import failed with lots of errors. I tried saving the .XLS file in the .SQL file format in Open Office, but I tried uploading that file, and it also got the same errors.

I'm not very familiar with how to import this data into the database now that I have the necessary user data in the spreadsheets. Do you know how I could import it?

Cheers :)

gareth_w’s picture

gareth_w commented

In one of your earlier posts, you mention that you have access to phpmyadmin. I run this simply by going to phpmyadmin, opening up the database, going to the sql tab, and copy-and-paste the code in. You can do this line by line to test out that this works before doing it all in one go.

To do multiple lines, then open an editor such as notepad, and copy the column from Excel and paste into Notepad - repeat with the second column underneath this. You can then copy and paste in one go into phpmyadmin.

I do not know what it would want as a SQL formatted file, but I would suggest using the notepad method above and saving this out as a plain text file may satisfy it's demands.

Gareth