Support Forum

I use PostNuke for management of my high school's webiste. I now want to get 250 accounts on line.

I have their usernames, email address and passwords on a spreadsheet. Is there a way for me to upload this info and not have to get all of them to register individually?

I tried searching for this to no avail.

Thanks in advance

Simon

I use PN for our school web site as well. What I do is generate an SQL statement like ...

Code

INSERT INTO nuke_users  (
  pn_uid,
  pn_name,
  pn_uname,
  pn_email,
  pn_femail,
  pn_url,
  pn_user_avatar,
  pn_user_regdate,
  pn_user_icq,
  pn_user_occ,
  pn_user_from,
  pn_user_intrest,
  pn_user_sig,
  pn_user_viewemail,
  pn_user_theme,
  pn_user_aim,
  pn_user_yim,
  pn_user_msnm,
  pn_pass,
  pn_storynum,
  pn_umode,
  pn_uorder,
  pn_thold,
  pn_noscore,
  pn_bio,
  pn_ublockon,
  pn_ublock,
  pn_theme,
  pn_commentmax,
  pn_counter,
  pn_timezone_offset,
VALUES ( ... insert your comma seperated data here ... );

It's ugly and you will have to run a similar SQL on the nuke_group_membership table. Something like this ...

Code

INSERT INTO nuke_group_membership (
  pn_gid,
  pn_uid)
VALUES
( insert comma seperated data here);

Hope this helps!

hmm - thanks.

The problem is that I have not got a clue how to do that?

Any ideas where to start?

Sorry,
I assumed that you would know. That's what happens when we assume. So let me back up a bit.

First, you will need some sort of access to the database you are using for your PN Site. Many people install and run phpMyAdmin ( http://www.phpmyadmin.net/ ) on their servers. Check with your hosting provider if they have this installed for you or, if you have shell access to your web server, you can install it yourself. Go to the phpMyAdmin site. Click on the link to download the version you will need. 2.5.1 is the latest vesion. If you are installing to an Apache server, you will click the link that ends .tar.gz. If you are using a Windows server (IIS) then you click the .zip file link. Installation is well documented in the README files or DOCS files once you unzip it. Once installed you can see all the database tables (usually http://www.yourdomain.com/phpmyadmin ). Be sure to password protect this directory or use the password function in phpMyAdmin. See the documentation for that.

I'm making more assumptions - your user data is in an MS Excel spreadsheet, right? That will complicate things a bit. I am also assuming you know a fair amount about Excel. I would suggest that your user data only contain information critical to log in to your site: user_id, real name, user name, email, and password (pn_uid, pn_name, pn_uname, pn_email, pn_password). TRUST me it is easier this way. I would also make sure the data is in that order as well.

Before you begin, look at your user data and break them into different spreadsheets depending upon what group they will be in on your site (STUDENTS, TEACHERS, PARENTS, etc.)

Now use your web site's User Administration screen to add a phony user with a generic password. Make different phony user for each group you are going to add. Use a DIFFERENT generic password for each group. If you have phpMyAdmin installed or some other method of viewing the database, open the nuke_users table and look at the record for the phony user. Copy the data in the pn_password field and paste it into your data file for each user. You could skip this step but then no user passwords are set. Of course doing it this way will make each user the same. Use whatever method you are most comfortable using. You should also add a pn_uid field (column) by adding that field at the beginning of the spreadsheet and filling it down with id's such as 1,2,3,4 (making sure no id repeats an id already in the database). Check your database table pn_users to see what user ids are taken. Start the new ids with the first id after the last id already in the table.

Make these adjustments in your data file and save it as a different file name so you don't lose any other data that you may want to add later. Of course if you need it all, simply list all of the field names in the SQL we will build below. All field names are listed in the example I used in my previous message.


Here is what I had to do:

• 
  
• 1. Open the MS Excel Sheet where you have the data
  
• 2. Save the file as "CSV" (In the Save As Type box at the bottom of the window) Remeber to use a different name for this file.
  
• 3. Open the CSV file you just saved with WORD PAD (not Notepad or MS Word) Be sure to select ALL FILES in the Files of Type box at the bottom of the window).
  
• 4. You should see your data seperated by commas. These are you VALUES that you want to store in the PN Database. You are going to need to add the appropriate SQL language to get them in there.
  
• 5. We have to clean up the VALUES so it will go into the database. All the values have to be added individully. So.. this is where we have to use Word Pad
  
• 6. Go to the Edit menu and select REPLACE. A little window will open up.
  
• 7. In the FIND WHAT box, type a comma
  
• 8. In the REPLACE WITH box, type a single quote followed by a comma followed by another single quote.
  
• 9. Click FIND NEXT and look at what is highlighted.
  
• 10. Click REPLACE ALL and the single comma (,) will be replaced by (',').
  
• 11. You will need to add an opening parenthesis ( at the begining of each VALUES link starting with the second VALUES and a single quote followed by a closing parenthesis followed by a semi-colon => '); at the end of EACH VALUES line. Copy and Paste is the easiest way to go.
  
• 12. Next, skip a line at the top of the page and add this SQL language to it
  Code

  [*]INSERT into pn_users (pn_uid, pn_name, pn_uname, pn_email, pn_pass)
  [*]VALUES (

  
  
• 13. Be sure you have listed the table fields you wish to insert in the SAME order as they appear on the VALUES lines from your data file. It should look like this:
  
• Code

  [*]INSERT into pn_users (pn_uid, pn_name, pn_uname, pn_email, pn_pass)
  [*]VALUES ('
  

• 23','Admin','Admin','tony@adytumsolutions.com','MY ENCRYPTED PASSWORD');
  [*]
  
• 14. Repeat step 12 FOR EACH VALUE LINE. Copy and paste is the easiest way to go.
  
• 15. Save the file as a text file
  
• 16. Open up phpMyAdmin (usually www.yourdomain.com/phpmyadmin )
  
• 17. I am using an older version so I hope this is the same for a newer version
  
• 18. Find the nuke_users table on the left side of the screen and click on it
  
• 19. Click on the SQL tab. There will be a large text box and below it a small text line box.
  
• 20. CLick BROWSE to go to the data text file you just created. Be sure to select "ALL FILES" in the FILES OF TYPE box in the File Upload window.
  
• 21. Double Click the file you want to upload
  
• 22. Click GO on the SQL page.
  
• 23. After several seconds you should get a message that your operation was successful and that xxx commands were executed.
  

Next you will have to update the nuke_group_membership table. It will be similar to what you just did with the user data, but a little easier.

To do this you will need to know what groups you have in your database and the group id for each group. To check this, use phpMyAdmin to open the nuke_groups table. Note the id for each group. For example you may have a TEACHERS group with the group id (pn_gid) of 1. Once you have determined the group id, do the following.

• 1. Open you MS Excel file you used to create the user data that you just imported.
  
• 2. DELETE all fields (columns) except for user id column (pn_uid).
  
• 3. INSERT a column to the left of pn_uid column so that it is the FIRST column in the spreadsheet. This will be the group id column (pn_gid).
  
• 4. Fill the pn_gid column with whatever group id is appropriate for the users you imported into your website.
  
• 5. SAVE this new file as a CSV file with a name like pn_group_teacher.csv
  
• 6. Use Word Pad to open the file you just saved.
  
• 7. Fortunately, when adding only numbers to the table, you don't need the single quote around the text. Just the comma between numbers.
  
• 8. You will need to add an opening parenthesis ( at the begining of each VALUES link starting with the second VALUES and closing parenthesis followed by a semi-colon => ); at the end of EACH VALUES line. Copy and Paste is the easiest way to go.
  
• 9. Skip a line at the top of the file and add this SQL language
  Code

  [*]INSERT INTO nuke_group_membership
  [*]VALUES ('
  

• 
  
• 10. Repeat step 8 for each group id and user id pair. Again, copy and paste.
  
• 11. Your SQL should look like this for each VALUE:
  Code

  [*]INSERT into nuke_group_membership (pn_gid,pn_uid)
  [*]VALUES (1,2);
  [*]

  
  
• 12. The pn_gid will all be the same while the pn_uid will be different for each entry.
  
• 
  

I hope this helps. Here is a good SQL tutorial that will help as well.
http://www.w3schools.com/sql/default.asp

By the way. I'd love to see your site. Our site is http://www.tjmiddle.org

I hope this helps. Good luck! :D

Wow, thanks for that.

I have phpMyAdmin installed and I am going to go ahead and do that. What a great mail and you are a star for taking the time to write it.

I will let you know how I get on.

Kind regards

Simon

No problem. I hope if there is an easier way that someone will come forth to let us know.

Now it is a small world.

I teach at Bangkok Patana School http://www.patana.ac.th in Thailand and the web is for my new school (which I have not started working at yet) which is United World College of the Adriatic in Italy http://www.uwcad.it I am just getting used to the system before transfering the files over to my school's server when I arrive there. http://www.simonfoley.com/pn It is really in no shape at the moment.

However, just by chance I know Frederick MD well. As a Brit, there is no reason for this other than that I worked as a camp counsellor when I was a student at Camp Airy, Thurmont. I did this from 90-96 and know the area only too well.

Simon