[教學] 大量新增使用者帳號的方法

phpBB-TW Knowledge Base
收集網友們在竹貓所發表的教學主題或文章,以利分享!
(僅供瀏覽,由版主群維護)

[教學] 大量新增使用者帳號的方法

文章心靈捕手 » 2008-02-01 21:04

前言:

這個做法只是方便大量新增帳號供學生使用.
此處的新增帳號, 並無考慮設定預設 Email;
也就是說, 最好關閉論壇發送 Email 的功能.

否則, 可得請學生自行修改其慣用的 Email.

新增帳號的方法也許很多, 但是這裡所示範的是利用 phpMyAdmin 執行 SQL 語法;
主要利用安裝論壇時, 資料庫預設更新語法, 加以直接修改 (預設語法轉貼在最後頭).
因此, 請先確認您已經安裝好 phpMyAdmin 這個程式.

作法:

舉例: 如果您要新增 5 筆學生帳號, 那麼可以輸入以下語法:

以全新 phpBB 2.0.22 論壇為例
#
#-----[ SQL ]-----
#
代碼: 選擇全部
# 新增 5 筆學生帳號 -- Users
INSERT INTO phpbb_users (user_id, username, user_level, user_regdate, user_password, user_email, user_icq, user_website, user_occ, user_from, user_interests, user_sig, user_viewemail, user_style, user_aim, user_yim, user_msnm, user_posts, user_attachsig, user_allowsmile, user_allowhtml, user_allowbbcode, user_allow_pm, user_notify_pm, user_allow_viewonline, user_rank, user_avatar, user_lang, user_timezone, user_dateformat, user_actkey, user_newpasswd, user_notify, user_active) VALUES ( 11, '50101', 0, 0, '81dc9bdb52d04dc20036dbd8313ed055', '', '', '', '', '', '', '', 0, NULL, '', '', '', 0, 0, 1, 1, 1, 0, 1, 1, NULL, '', '', 8, '', '', '', 0, 1);
INSERT INTO phpbb_users (user_id, username, user_level, user_regdate, user_password, user_email, user_icq, user_website, user_occ, user_from, user_interests, user_sig, user_viewemail, user_style, user_aim, user_yim, user_msnm, user_posts, user_attachsig, user_allowsmile, user_allowhtml, user_allowbbcode, user_allow_pm, user_notify_pm, user_allow_viewonline, user_rank, user_avatar, user_lang, user_timezone, user_dateformat, user_actkey, user_newpasswd, user_notify, user_active) VALUES ( 12, '50102', 0, 0, '81dc9bdb52d04dc20036dbd8313ed055', '', '', '', '', '', '', '', 0, NULL, '', '', '', 0, 0, 1, 1, 1, 0, 1, 1, NULL, '', '', 8, '', '', '', 0, 1);
INSERT INTO phpbb_users (user_id, username, user_level, user_regdate, user_password, user_email, user_icq, user_website, user_occ, user_from, user_interests, user_sig, user_viewemail, user_style, user_aim, user_yim, user_msnm, user_posts, user_attachsig, user_allowsmile, user_allowhtml, user_allowbbcode, user_allow_pm, user_notify_pm, user_allow_viewonline, user_rank, user_avatar, user_lang, user_timezone, user_dateformat, user_actkey, user_newpasswd, user_notify, user_active) VALUES ( 13, '50103', 0, 0, '81dc9bdb52d04dc20036dbd8313ed055', '', '', '', '', '', '', '', 0, NULL, '', '', '', 0, 0, 1, 1, 1, 0, 1, 1, NULL, '', '', 8, '', '', '', 0, 1);
INSERT INTO phpbb_users (user_id, username, user_level, user_regdate, user_password, user_email, user_icq, user_website, user_occ, user_from, user_interests, user_sig, user_viewemail, user_style, user_aim, user_yim, user_msnm, user_posts, user_attachsig, user_allowsmile, user_allowhtml, user_allowbbcode, user_allow_pm, user_notify_pm, user_allow_viewonline, user_rank, user_avatar, user_lang, user_timezone, user_dateformat, user_actkey, user_newpasswd, user_notify, user_active) VALUES ( 14, '50104', 0, 0, '81dc9bdb52d04dc20036dbd8313ed055', '', '', '', '', '', '', '', 0, NULL, '', '', '', 0, 0, 1, 1, 1, 0, 1, 1, NULL, '', '', 8, '', '', '', 0, 1);
INSERT INTO phpbb_users (user_id, username, user_level, user_regdate, user_password, user_email, user_icq, user_website, user_occ, user_from, user_interests, user_sig, user_viewemail, user_style, user_aim, user_yim, user_msnm, user_posts, user_attachsig, user_allowsmile, user_allowhtml, user_allowbbcode, user_allow_pm, user_notify_pm, user_allow_viewonline, user_rank, user_avatar, user_lang, user_timezone, user_dateformat, user_actkey, user_newpasswd, user_notify, user_active) VALUES ( 15, '50105', 0, 0, '81dc9bdb52d04dc20036dbd8313ed055', '', '', '', '', '', '', '', 0, NULL, '', '', '', 0, 0, 1, 1, 1, 0, 1, 1, NULL, '', '', 8, '', '', '', 0, 1);

說明:
第一個欄位 user_id 值, 必須自 2 (不含 2) 以後, 依序填入
第二個欄位 username 值, 必須自行依序輸入 (例如: 學生的學號)
第五個欄位 user_password 值, 可以統一指定 (例如: 1234), 但是必須轉換為 md5 (例如: 81dc9bdb52d04dc20036dbd8313ed055)
倒數第六個欄位 user_timezone 值, 可以修改為 8 (此乃針對台灣的時區設定)
最後一個欄位 user_active 值, 修改為 1
#
#-----[ SQL ]-----
#
代碼: 選擇全部
# 新增 5 筆學生帳號 -- User -> Group
INSERT INTO phpbb_user_group (group_id, user_id, user_pending) VALUES (11, 11, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending) VALUES (12, 12, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending) VALUES (13, 13, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending) VALUES (14, 14, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending) VALUES (15, 15, 0);

說明:
第二個欄位 user_id 值, 對應資料表 phpbb_users 新增者, 依序填入
第一個欄位 group_id 值, 填入與 user_id 相同的數值

以全新 phpBB 3.0.0 論壇為例
#
#-----[ SQL ]-----
#
代碼: 選擇全部
# 新增 5 筆學生帳號 -- Users
INSERT INTO phpbb_users (user_id, user_type, group_id, username, username_clean, user_regdate, user_password, user_email, user_lang, user_style, user_rank, user_colour, user_posts, user_permissions, user_ip, user_birthday, user_lastpage, user_last_confirm_key, user_post_sortby_type, user_post_sortby_dir, user_topic_sortby_type, user_topic_sortby_dir, user_avatar, user_sig, user_sig_bbcode_uid, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd, user_allow_massemail, user_timezone) VALUES (11, 0, 2, '50101', '50101', 0, '81dc9bdb52d04dc20036dbd8313ed055', '', 'tw', 1, 0, '', 0, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 8);
INSERT INTO phpbb_users (user_id, user_type, group_id, username, username_clean, user_regdate, user_password, user_email, user_lang, user_style, user_rank, user_colour, user_posts, user_permissions, user_ip, user_birthday, user_lastpage, user_last_confirm_key, user_post_sortby_type, user_post_sortby_dir, user_topic_sortby_type, user_topic_sortby_dir, user_avatar, user_sig, user_sig_bbcode_uid, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd, user_allow_massemail, user_timezone) VALUES (12, 0, 2, '50102', '50102', 0, '81dc9bdb52d04dc20036dbd8313ed055', '', 'tw', 1, 0, '', 0, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 8);
INSERT INTO phpbb_users (user_id, user_type, group_id, username, username_clean, user_regdate, user_password, user_email, user_lang, user_style, user_rank, user_colour, user_posts, user_permissions, user_ip, user_birthday, user_lastpage, user_last_confirm_key, user_post_sortby_type, user_post_sortby_dir, user_topic_sortby_type, user_topic_sortby_dir, user_avatar, user_sig, user_sig_bbcode_uid, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd, user_allow_massemail, user_timezone) VALUES (13, 0, 2, '50103', '50103', 0, '81dc9bdb52d04dc20036dbd8313ed055', '', 'tw', 1, 0, '', 0, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 8);
INSERT INTO phpbb_users (user_id, user_type, group_id, username, username_clean, user_regdate, user_password, user_email, user_lang, user_style, user_rank, user_colour, user_posts, user_permissions, user_ip, user_birthday, user_lastpage, user_last_confirm_key, user_post_sortby_type, user_post_sortby_dir, user_topic_sortby_type, user_topic_sortby_dir, user_avatar, user_sig, user_sig_bbcode_uid, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd, user_allow_massemail, user_timezone) VALUES (14, 0, 2, '50104', '50104', 0, '81dc9bdb52d04dc20036dbd8313ed055', '', 'tw', 1, 0, '', 0, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 8);
INSERT INTO phpbb_users (user_id, user_type, group_id, username, username_clean, user_regdate, user_password, user_email, user_lang, user_style, user_rank, user_colour, user_posts, user_permissions, user_ip, user_birthday, user_lastpage, user_last_confirm_key, user_post_sortby_type, user_post_sortby_dir, user_topic_sortby_type, user_topic_sortby_dir, user_avatar, user_sig, user_sig_bbcode_uid, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd, user_allow_massemail, user_timezone) VALUES (15, 0, 2, '50105', '50105', 0, '81dc9bdb52d04dc20036dbd8313ed055', '', 'tw', 1, 0, '', 0, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, 8);

說明:
開頭, 可以自行新增第一個欄位 user_id 值, 必須自 2 (不含 2) 以後, 依序填入
第二個欄位 user_type 值, 修改為 0
第三個欄位 group_id 值, 修改為 2
第四個欄位 username 值, 必須自行依序輸入 (例如: 學生的學號)
第五個欄位 username_clean 值, (同上)
第七個欄位 user_password 值, 可以統一指定 (例如: 1234), 但是必須轉換為 md5 (例如: 81dc9bdb52d04dc20036dbd8313ed055)
這個欄位 user_lang 值, 可以修改為 tw (此乃針對正體中文設定, 您必須考量自己使用的資料夾名稱做修改)
最後, 可以自行新增一個欄位 user_timezone 值, 8 (此乃針對台灣的時區設定)
#
#-----[ SQL ]-----
#
代碼: 選擇全部
# 新增 5 筆學生帳號 -- User -> Group
INSERT INTO phpbb_user_group (group_id, user_id, user_pending, group_leader) VALUES (2, 11, 0, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending, group_leader) VALUES (2, 12, 0, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending, group_leader) VALUES (2, 13, 0, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending, group_leader) VALUES (2, 14, 0, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending, group_leader) VALUES (2, 15, 0, 0);

說明:
第二個欄位 user_id 值, 對應資料表 phpbb_users 新增者, 依序填入

後記:

依實際測試, 若在 phpBB 2.0.22 新增 phpbb_users 使用者, 則可省略後面 phpbb_user_group 的操作;
但是, 若在 phpBB 3.0.0 新增 phpbb_users 使用者, 則務必得同時完成 phpbb_user_group 的操作.

如果您的論壇運作已久, 而途中要大量加入使用者帳號的話,
那麼只要在新增 user_id 以及 username 時, 記得不要重複使用即可.

###############################################################################
以下為 phpBB 2.0.22 以及 phpBB 3.0.0 安裝時,
新增預設的使用者 (anonymous, admin) 帳號語法

###############################################################################
# phpBB 2.0.22 mysql_basic.sql
代碼: 選擇全部
# -- Users
INSERT INTO phpbb_users (user_id, username, user_level, user_regdate, user_password, user_email, user_icq, user_website, user_occ, user_from, user_interests, user_sig, user_viewemail, user_style, user_aim, user_yim, user_msnm, user_posts, user_attachsig, user_allowsmile, user_allowhtml, user_allowbbcode, user_allow_pm, user_notify_pm, user_allow_viewonline, user_rank, user_avatar, user_lang, user_timezone, user_dateformat, user_actkey, user_newpasswd, user_notify, user_active) VALUES ( -1, 'Anonymous', 0, 0, '', '', '', '', '', '', '', '', 0, NULL, '', '', '', 0, 0, 1, 1, 1, 0, 1, 1, NULL, '', '', 0, '', '', '', 0, 0);

# -- username: admin    password: admin (change this or remove it once everything is working!)
INSERT INTO phpbb_users (user_id, username, user_level, user_regdate, user_password, user_email, user_icq, user_website, user_occ, user_from, user_interests, user_sig, user_viewemail, user_style, user_aim, user_yim, user_msnm, user_posts, user_attachsig, user_allowsmile, user_allowhtml, user_allowbbcode, user_allow_pm, user_notify_pm, user_popup_pm, user_allow_viewonline, user_rank, user_avatar, user_lang, user_timezone, user_dateformat, user_actkey, user_newpasswd, user_notify, user_active) VALUES ( 2, 'Admin', 1, 0, '21232f297a57a5a743894a0e4a801fc3', 'admin@yourdomain.com', '', '', '', '', '', '', 1, 1, '', '', '', 1, 0, 1, 0, 1, 1, 1, 1, 1, 1, '', 'english', 0, 'd M Y h:i a', '', '', 0, 1);

# -- User -> Group
INSERT INTO phpbb_user_group (group_id, user_id, user_pending) VALUES (1, -1, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending) VALUES (2, 2, 0);

# phpBB 3.0.0 schema_data.sql
代碼: 選擇全部
# -- Users / Anonymous user
INSERT INTO phpbb_users (user_type, group_id, username, username_clean, user_regdate, user_password, user_email, user_lang, user_style, user_rank, user_colour, user_posts, user_permissions, user_ip, user_birthday, user_lastpage, user_last_confirm_key, user_post_sortby_type, user_post_sortby_dir, user_topic_sortby_type, user_topic_sortby_dir, user_avatar, user_sig, user_sig_bbcode_uid, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd, user_allow_massemail) VALUES (2, 1, 'Anonymous', 'anonymous', 0, '', '', 'en', 1, 0, '', 0, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0);

# -- username: Admin    password: admin (change this or remove it once everything is working!)
INSERT INTO phpbb_users (user_type, group_id, username, username_clean, user_regdate, user_password, user_email, user_lang, user_style, user_rank, user_colour, user_posts, user_permissions, user_ip, user_birthday, user_lastpage, user_last_confirm_key, user_post_sortby_type, user_post_sortby_dir, user_topic_sortby_type, user_topic_sortby_dir, user_avatar, user_sig, user_sig_bbcode_uid, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd) VALUES (3, 5, 'Admin', 'admin', 0, '21232f297a57a5a743894a0e4a801fc3', 'admin@yourdomain.com', 'en', 1, 1, 'AA0000', 1, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '');

# -- User -> Group
INSERT INTO phpbb_user_group (group_id, user_id, user_pending, group_leader) VALUES (1, 1, 0, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending, group_leader) VALUES (2, 2, 0, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending, group_leader) VALUES (4, 2, 0, 0);
INSERT INTO phpbb_user_group (group_id, user_id, user_pending, group_leader) VALUES (5, 2, 0, 1);
施比受有福,歡迎來信賜教,謝謝 & 再見!
祝福您 好運 ^_^
歡迎加入★★心靈捕手★★ :: 討論區!!
http://wang5555.hopto.org/phpBB3/
p.s. 奉老婆之命:在晚上十一點前,得關機睡覺!!!
頭像
心靈捕手
默默耕耘的老師
默默耕耘的老師
 
文章: 8565
註冊時間: 2004-04-30 01:54
來自: Taiwan

回到 教學文件庫

誰在線上

正在瀏覽這個版面的使用者:Google [Bot] 和 1 位訪客

Partnership: phpBBHacks.com