Creating Users from Posts

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
marius93
Registered User
Posts: 3
Joined: Thu Nov 16, 2017 7:55 am

Creating Users from Posts

Post by marius93 » Thu Nov 16, 2017 8:03 am

Hello,

rather strange issue over here, i want to host a forum as an archive of an old forum (read-only). I just discovered that for some reason the backup of the old forum i have does not contain the phpbb_users table. I managed to merge the old db with a fresh phpbb installation, so far i can log onto the board and see all the old forums and latest posts but as soon as i enter a forum or click on a post it tells me that it doesn't exist.
I assume this is because the users who created the posts dont actually exist anymore, correct?

Is there any way i can somehow create a dummy account from the information i have in phpbb_posts for each user? I suppose through a complicated SQL command but my knowledge on that subject is too limited to wrap my head around it.

Thanks in advance!

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 47990
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Creating Users from Posts

Post by stevemaury » Thu Nov 16, 2017 4:31 pm

Well, let's find out what the minimum amount of data in the users table is necessary for the posts to show, if, indeed, the absence of the users table is the reason they don't show.

Try this first. Create a users table:

Code: Select all

CREATE TABLE `phpbb_users` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `user_type` tinyint(2) NOT NULL DEFAULT '0',
  `group_id` mediumint(8) UNSIGNED NOT NULL DEFAULT '3',
  `user_permissions` mediumtext COLLATE utf8_bin NOT NULL,
  `user_perm_from` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  `user_ip` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_regdate` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `username` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `username_clean` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_password` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_passchg` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `user_email` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_email_hash` bigint(20) NOT NULL DEFAULT '0',
  `user_birthday` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_lastvisit` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `user_lastmark` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `user_lastpost_time` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `user_lastpage` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_last_confirm_key` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_last_search` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `user_warnings` tinyint(4) NOT NULL DEFAULT '0',
  `user_last_warning` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `user_login_attempts` tinyint(4) NOT NULL DEFAULT '0',
  `user_inactive_reason` tinyint(2) NOT NULL DEFAULT '0',
  `user_inactive_time` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `user_posts` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  `user_lang` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_timezone` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_dateformat` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT 'd M Y H:i',
  `user_style` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  `user_rank` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  `user_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_new_privmsg` int(4) NOT NULL DEFAULT '0',
  `user_unread_privmsg` int(4) NOT NULL DEFAULT '0',
  `user_last_privmsg` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `user_message_rules` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `user_full_folder` int(11) NOT NULL DEFAULT '-3',
  `user_emailtime` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `user_topic_show_days` smallint(4) UNSIGNED NOT NULL DEFAULT '0',
  `user_topic_sortby_type` char(1) COLLATE utf8_bin NOT NULL DEFAULT 't',
  `user_topic_sortby_dir` char(1) COLLATE utf8_bin NOT NULL DEFAULT 'd',
  `user_post_show_days` smallint(4) UNSIGNED NOT NULL DEFAULT '0',
  `user_post_sortby_type` char(1) COLLATE utf8_bin NOT NULL DEFAULT 't',
  `user_post_sortby_dir` char(1) COLLATE utf8_bin NOT NULL DEFAULT 'a',
  `user_notify` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `user_notify_pm` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `user_notify_type` tinyint(4) NOT NULL DEFAULT '0',
  `user_allow_pm` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `user_allow_viewonline` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `user_allow_viewemail` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `user_allow_massemail` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `user_options` int(11) UNSIGNED NOT NULL DEFAULT '230271',
  `user_avatar` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_avatar_type` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_avatar_width` smallint(4) UNSIGNED NOT NULL DEFAULT '0',
  `user_avatar_height` smallint(4) UNSIGNED NOT NULL DEFAULT '0',
  `user_sig` mediumtext COLLATE utf8_bin NOT NULL,
  `user_sig_bbcode_uid` varchar(8) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_sig_bbcode_bitfield` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_jabber` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_actkey` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_newpasswd` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_form_salt` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_new` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  `user_reminded` tinyint(4) NOT NULL DEFAULT '0',
  `user_reminded_time` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `user_text_hover` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Now, let's create the Anonymous user:

Code: Select all

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) VALUES (1, 2, 1, 'Anonymous', 'anonymous', 0, '', '', 'en', 1, 0, '', 0, '', '', '', '', '', 't', 'a', 't', 'd', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0);
Next, backup the posts table.

Now, sort the posts table on the topic_id column and find a topic_id that isn't too long, and note its forum_id (say, Y) and in the forums table, find the name of that forum. Then, run this query:

Code: Select all

UPDATE phpbb_posts SET poster_id = 1 WHERE topic_id = X
X is the topic_id of the topic you selected above. This should result in a topic X in forum Y where all the posts are made by Anonymous. See if you can now see that topic. Post back.
For REALLY good and VERY inexpensive hosting CLICK HERE

All unsolicited PMs will be ignored.

marius93
Registered User
Posts: 3
Joined: Thu Nov 16, 2017 7:55 am

Re: Creating Users from Posts

Post by marius93 » Thu Nov 16, 2017 6:26 pm

Alright.
I used your instructions on the first topic in the "announcements" forum, however when i enter that forum it still says "There are no topics or posts in this forum."
I discovered something else tho, i created an admin account on the forum and assigned that account's id instead of the anonymous one, it still won't show up when i click on the forum but i can see all the posts when i go to my profile and select "Show your posts".

User avatar
AmigoJack
Registered User
Posts: 4999
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Creating Users from Posts

Post by AmigoJack » Fri Nov 17, 2017 8:57 am

Try it on a topic with only one post or with posts that are only from one user (and those posts should not have been edited by someone else).
The worst thing about censorship is ███████████

marius93
Registered User
Posts: 3
Joined: Thu Nov 16, 2017 7:55 am

Re: Creating Users from Posts

Post by marius93 » Sat Nov 18, 2017 12:06 am

I literally can't find any topic that doesn't have atleast one reply by someone other than it's creator :/.
I tried setting all posts to my user id, so now my profile shows that i have 185k posts (100%) and i can also view each one when i click on "Show your posts". I can't view any topics or access any forums tho, it still tells me they don't exist.

User avatar
3Di
Registered User
Posts: 11872
Joined: Mon Apr 04, 2005 11:09 pm
Location: Milano - Frankfurt
Name: Marco
Contact:

Re: Creating Users from Posts

Post by 3Di » Sat Nov 18, 2017 2:43 am

marius93 wrote:
Thu Nov 16, 2017 8:03 am
Is there any way i can somehow create a dummy account from the information i have in phpbb_posts for each user?
That's a very tricky situation, you need a script which basically creates an array of poster_ids (which are the user IDs) and for each ID creates a dummy user with a dummy name but the right ID. Example dummy_user_100 for (poster_id = 100), the creation of those dummies will be using the native function user_add(). That's just the begin of the flow-chart so to speak, there are also other data/tables involved, not an easy task but doable, assuming you are confident with the core code and PHP. Also it depends effectively on the "state" of your old backup.

At the moment you have all of those posts with just one user id, which is the admin account you created. Check the permissions of such admin and see if you need to set them for good, in order to view forum/topics or the likes.. that's related to this quote below..
marius93 wrote:
Sat Nov 18, 2017 12:06 am
I can't view any topics or access any forums tho, it still tells me they don't exist.
Want to compensate me for my interest? Donate
Please PM me only to request paid works. Thx.
Extensions, Scripts, MOD porting, Update/Upgrades

Post Reply

Return to “[3.2.x] Support Forum”

Who is online

Users browsing this forum: bonelifer, Crizzo, Noxwizard, Paul, xxx777 and 156 guests