Converting Simple:Press to phpBB3

Not having been 100% happy with the Simple:Press forums to begin with, their decision to start charging for access to their support forums without warning was the last straw for me. I decided to go back to using reliable phpBB. There was just one glaringly big problem with that. There was no converter to go from Simple:Press to phpBB.

Despite searching all over, I couldn’t find one. The best I could find were some clunky workarounds or half solutions. These usually involved attempting o convert Simple:Press over to something else first and even the converters for that usually weren’t fully developed. Or they involved converting just the members from a WordPress site over to phpBB. I really wasn’t happy with any of the options that I came across.

I came across this topic here and tentatively decided that converting to bbPress first and then phpBB after that was my best option. The biggest problem with this solution is that I had never worked directly on a database before. Maybe I had gone into phpMyAdmin and edited a user name or other similar simple things, but that’s not exactly the same thing. I also quickly found that this was going to take more customization than I thought and because of that, I thought, “Hey, why don’t I just use this script as a template and convert directly from Simple:Press?” So that’s exactly what I did.

I thought it would be nice to share this so that it might help others who find themselves in the same position that I was in.

 SQL |  Copy to Clipboard |? 
001
-- SET NAMES utf8;
002
-- SET SQL_MODE='';
003
-- SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
004
-- SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
005
 
006
 
007
 
008
-- 0. BACKUP!
009
-- really, *backup* your default installation of phpBB3
010
-- if something goes wrong, you can get it back to what it was and start over
011
 
012
-- if your WordPress/Simple:Press database is not the same as your phpBB3 database then copy wp_users, wp_sfmembers, wp_sfforums, wp_sfgroups, wp_sftopics and wp_sfposts to your phpbb3 database
013
 
014
 
015
 
016
-- 1. clear tables
017
DELETE FROM phpbb_forums;
018
DELETE FROM phpbb_topics;
019
DELETE FROM phpbb_topics_posted;
020
DELETE FROM phpbb_topics_track;
021
DELETE FROM phpbb_posts;
022
DELETE FROM phpbb_acl_groups WHERE forum_id > 0;
023
-- there is a difference in user_id.
024
-- in WordPress, your admin was 1, in phpBB3 is 2 (Anonymous is 1).
025
-- phpBB3 also has 50 bots preinstalled, thus member user_ids from WordPress must be pushed up:
026
--  2 becomes 53, 3 -> 54 etc.
027
 
028
SET @user_id_jump = 51;
029
 
030
-- now clear all users except Anon, Admin and Bots
031
DELETE FROM phpbb_user_group WHERE user_id > (@user_id_jump+1);
032
DELETE FROM phpbb_users WHERE user_id > (@user_id_jump+1);
033
DELETE FROM phpbb_acl_users WHERE user_id > (@user_id_jump+1);
034
 
035
-- clears user registration dates
036
UPDATE phpbb_users
037
SET user_regdate = '';
038
 
039
 
040
 
041
-- 2. transfer users
042
 
043
-- first add lastvisit column to wp_users, only need to run once otherwise commented out
044
ALTER TABLE wp_users
045
ADD lastvisit datetime;
046
 
047
-- this gets the last visit date and time from Simple:Press and gets it ready for phpBB without having to deal with mismatched user id's
048
UPDATE wp_users
049
SET lastvisit = (SELECT lastvisit FROM wp_sfmembers WHERE user_id = ID)
050
WHERE ID > 1;
051
 
052
-- 2a. initial users transfer - assumes all users except 1st (which is admin) are normal users. admin is not transferred.
053
-- group_id: 2 == REGISTERED
054
-- user_type: 0 == USER_NORMAL
055
-- user_style: 2 == my newly installed style. 1 = prosilver, 2 = my style
056
-- user_permissions: ?! == how is this calculated?
057
-- user_email_hash: ?! == how is this calculated?
058
INSERT INTO phpbb_users
059
(user_id, user_type, group_id, username, username_clean, user_password, user_pass_convert, user_email, user_website, user_lastvisit, user_regdate, user_timezone, user_dst, user_lang, user_dateformat, user_style, user_options, loginname, loginname_clean)
060
SELECT ID + @user_id_jump AS user_id, 0, 2, display_name, LOWER(REPLACE(display_name, '.', '_')), REPLACE(user_pass, '$P$', '$H$'), 0, user_email, user_url, unix_timestamp(lastvisit), unix_timestamp(user_registered), 0, 0, 'en_us', 'F jS, Y, g:i a', 1, 230271, user_login, LOWER(REPLACE(user_login, '.', '_'))
061
FROM wp_users
062
WHERE ID > 1;
063
 
064
-- sets original registration date for admin account because admin account does get moved over
065
UPDATE phpbb_users
066
SET user_regdate = (SELECT unix_timestamp(user_registered) FROM wp_users WHERE ID= 1) WHERE user_id = 2;
067
 
068
-- updates unix timestamp for timezone (GMT -6) and daylight savings years 2011 and 2012
069
UPDATE phpbb_users
070
SET user_regdate = (SELECT CASE
071
WHEN user_regdate < 1299981600 AND (user_id = 2 OR user_id > @user_id_jump + 1) THEN user_regdate - 21600
072
WHEN user_regdate >= 1299981600 AND user_regdate < 1320544800 AND (user_id = 2 OR user_id > @user_id_jump + 1) THEN user_regdate - 18000
073
WHEN user_regdate >= 1320544800 AND user_regdate < 1331431200 AND (user_id = 2 OR user_id > @user_id_jump + 1) THEN user_regdate - 21600
074
WHEN user_regdate >= 1331431200 AND user_regdate < 1351994400 AND (user_id = 2 OR user_id > @user_id_jump + 1) THEN user_regdate - 18000
075
ELSE user_regdate
076
END);
077
 
078
 
079
 
080
-- 2b. update user_group table
081
INSERT INTO phpbb_user_group (
082
group_id, user_id, group_leader, user_pending
083
)
084
SELECT DISTINCT group_id, user_id, 0, 0
085
FROM phpbb_users
086
WHERE user_id > (@user_id_jump+1);
087
 
088
 
089
 
090
-- 3. transfer forums
091
-- assumes that regular forums have one parent and no subforums
092
-- forum_type: 0 == Parent/Group Forum
093
-- forum_type: 1 == Regular Forum
094
SET @max_group_id = (SELECT MAX(group_id) FROM wp_sfgroups);
095
 
096
INSERT INTO phpbb_forums ( forum_id, parent_id, left_id, right_id, forum_name, forum_desc, forum_type, forum_posts, forum_topics, forum_topics_real, forum_last_post_id, forum_last_poster_id, forum_last_post_subject, forum_last_post_time, forum_last_poster_name, forum_flags )
097
SELECT forum_id + @max_group_id, f.group_id, 0, 0, f.forum_name, f.forum_desc, 1, f.post_count, f.topic_count, f.topic_count, post_id, 0, '', 0, '', 64
098
FROM wp_sfforums f;
099
 
100
-- UPDATE phpbb_forums f
101
-- SET
102
-- forum_id = (SELECT f.forum_id + MAX(group_id) FROM wp_sfgroups);
103
 
104
INSERT INTO phpbb_forums ( forum_id, parent_id, left_id, right_id, forum_name, forum_desc, forum_type, forum_posts, forum_topics, forum_topics_real, forum_last_post_id, forum_last_poster_id, forum_last_post_subject, forum_last_post_time, forum_last_poster_name, forum_flags )
105
SELECT group_id, 0, 0, 0, group_name, '', 0, 0, 0, 0, 0, 0, '', 0, '', 48
106
FROM wp_sfgroups f;
107
 
108
 
109
 
110
-- 3a. update last post* data
111
UPDATE phpbb_forums f
112
SET forum_last_poster_id = (SELECT CASE user_id WHEN 1 THEN user_id+1 ELSE user_id+@user_id_jump END AS user_id FROM wp_sfposts p, wp_sfforums sff WHERE p.post_id = sff.post_id AND sff.post_id = f.forum_last_post_id),
113
forum_last_poster_name = (SELECT username FROM phpbb_users u WHERE u.user_id = f.forum_last_poster_id),
114
forum_last_post_time = (SELECT unix_timestamp(post_date) FROM wp_sfposts WHERE post_id = f.forum_last_post_id);
115
 
116
-- 3b. update left_id and right_id
117
-- see: http://www.sitepoint.com/article/hierarchical-data-database/2
118
-- Install phpBB3 Support Tool Kit and Run Fix Left/Right ID's, you may have to rearrange some forums after this is done
119
 
120
-- 3c. set forum ACL -- this sets required permissions for the forums, based on groups
121
--  set READONLY for GUESTS group
122
INSERT INTO phpbb_acl_groups (
123
group_id, forum_id, auth_role_id
124
) SELECT 1, forum_id, 17
125
FROM phpbb_forums;
126
--  set FORUMSTANDARD+POLL for REGISTERED group
127
INSERT INTO phpbb_acl_groups (
128
group_id, forum_id, auth_role_id
129
) SELECT 2, forum_id, 21
130
FROM phpbb_forums;
131
--  set FORUMSTANDARD+POLL for REGISTERED COPPA group
132
INSERT INTO phpbb_acl_groups (
133
group_id, forum_id, auth_role_id
134
) SELECT 3, forum_id, 21
135
FROM phpbb_forums;
136
--  set FORUMFULL for GLOBAL_MODS group
137
INSERT INTO phpbb_acl_groups (
138
group_id, forum_id, auth_role_id
139
) SELECT 4, forum_id, 14
140
FROM phpbb_forums;
141
--  set FORUMFULL for ADMINS group
142
INSERT INTO phpbb_acl_groups (
143
group_id, forum_id, auth_role_id
144
) SELECT 5, forum_id, 14
145
FROM phpbb_forums;
146
--  set BOTS for BOTS group
147
INSERT INTO phpbb_acl_groups (
148
group_id, forum_id, auth_role_id
149
) SELECT 6, forum_id, 19
150
FROM phpbb_forums;
151
-- set FORUMSTANDARD+POLL for Newly Registered
152
INSERT INTO phpbb_acl_groups (
153
group_id, forum_id, auth_role_id
154
) SELECT 7, forum_id, 21
155
FROM phpbb_forums;
156
 
157
 
158
 
159
-- 4. transfer topics
160
INSERT INTO phpbb_topics ( topic_id, topic_title, topic_time, topic_last_post_time, topic_poster, topic_first_poster_name, topic_last_poster_id, topic_last_poster_name, forum_id, topic_replies, topic_first_post_id, topic_last_post_id )
161
SELECT t.topic_id, t.topic_name, unix_timestamp(t.topic_date),
162
0, CASE t.user_id WHEN 1 THEN t.user_id+1 ELSE t.user_id+@user_id_jump END, '', 0, '', t.forum_id, t.post_count - 1, 0, t.post_id
163
FROM wp_sftopics t;
164
 
165
-- 4a. update poster-related data
166
UPDATE phpbb_topics t
167
SET
168
forum_id = (SELECT forum_id + MAX(group_id) FROM wp_sfgroups),
169
topic_last_post_time = (SELECT unix_timestamp(post_date) FROM wp_sfposts WHERE post_id = t.topic_last_post_id),
170
topic_last_poster_id = (SELECT CASE user_id WHEN 1 THEN user_id+1 ELSE user_id+@user_id_jump END AS user_id FROM wp_sfposts p WHERE p.post_id = t.topic_last_post_id),
171
topic_first_post_id = (SELECT MIN(post_id) FROM wp_sfposts p WHERE p.topic_id = t.topic_id),
172
topic_first_poster_name = (SELECT username FROM phpbb_users u WHERE t.topic_poster = u.user_id),
173
topic_last_poster_name = (SELECT username FROM phpbb_users u WHERE t.topic_last_poster_id = u.user_id),
174
topic_replies_real = topic_replies;
175
 
176
 
177
 
178
-- 5. transfer posts
179
INSERT INTO phpbb_posts ( post_id, topic_id, forum_id, poster_id, poster_ip, post_time, post_username, post_subject, post_text )
180
SELECT p.post_id, p.topic_id, p.forum_id, CASE p.user_id WHEN 1 THEN p.user_id+1 ELSE p.user_id+@user_id_jump END AS user_id, p.poster_ip, unix_timestamp(p.post_date), (SELECT display_name FROM wp_users WHERE ID = p.user_id), '', p.post_content
181
FROM wp_sfposts p;
182
 
183
UPDATE phpbb_posts
184
SET
185
forum_id = (SELECT forum_id + MAX(group_id) FROM wp_sfgroups);
186
 
187
 
188
 
189
-- 6. update connection between users and topics
190
INSERT INTO phpbb_topics_posted ( user_id, topic_id, topic_posted )
191
SELECT DISTINCT topic_poster, topic_id, 1
192
FROM phpbb_topics;
193
 
194
 
195
 
196
-- 7. re-sync and re-count forums, topics and posts in phpBB3 admin, on General page
197
-- Install phpBB3 Support Tool Kit and Run Fix Left/Right ID's, you may have to rearrange some forums after this is done
198
-- Run Update Email Hashes from Support Tool Kit
199
-- If you copied tables over from WordPress and Simple:Press, you can delete them now
200
 
201
 
202
-- SET SQL_MODE=@OLD_SQL_MODE;
203
-- SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

This was used on a WordPress 3.4.1 install with Simple:Press 5.1.1 and a brand new install of phpBB 3.0.10 with the Separate Login and User Name 1.0.10 mod. If you plan to use this, you will need to customize it for your particular setup. (Don’t forget to back everything up first! It’s also a good idea to test this on a developer website and not on your live website.)

To get all the data I wanted for the members, I went ahead and pulled them from WordPress itself instead of Simple:Press. However, that didn’t allow me to get the last visit date, so I added that to wp_users from wp_sfmembers first. Also, I want to mention that the passwords transfer over just fine and there is no need for members to have to recover their passwords. WordPress and phpBB use the same password scheme and I just had to replace the $P$ at the beginning with $H$.

This also doesn’t transfer the admin account. Assuming that your admin account is 1 in WordPress and the default for the install of phpBB, this should be able to handle that and even pull in the registration date from WordPress.

Registration and last visit date/times turned out to be a small problem as well. Members who had registered but never logged in after that had last visit dates that were before their registration dates. Turned out to be a small timezone issue. I also had to take into account daylight savings to finally get the date/times right. The query I made to handle this only goes back to 2010 since that is when I started using WordPress.

The hardest part was transferring the forums. This is where having the phpBB Support Tool Kit comes in handy. Most of the forum info transfers ok, but I wasn’t going to try and figure out how to fix the left and right id’s. Luckily, the STK has a tool to do just that.

Not everything that is transferred over is transferred flawlessly either. This is why it’s important to Resynchronize statistics and Resynchronize post counts from the General Tab of the phpBB Admin Control Panel. It’s also a good idea to go to the Forums tab and resync each forum individually. You’ll probably also want to go to the Maintenance tab and click on Search Index and rebuild the search indexes.

The purpose of this script was to transfer the members and the posts. This does not include private messages or anything else. If those are important to you, you will have to add those queries yourself.

For my own forum, I went a little farther than this. I had an old vBulletin forum and decided that I wanted to merge them with my new phpBB3 forum. That, however, is a topic for another time.

This is not the most elegant way of transferring Simple:Press to phpBB3. I’m sure if I had to do it over again, I could do a much better job of it. However, for my first attempt at working with a database, I think I did a decent job.

This seemed like a daunting and potentially overwhelming task when I started on it, but I started on it by breaking it up into little pieces instead of one big job. Each little piece helped me to learn the basics and once I got the hang of the basics, I found that making the modifications I needed wasn’t too hard at all.



38 comments on “Converting Simple:Press to phpBB3

  1. hi Endy, this is exactly what i need but have little knowledge of databases etc. it would be great if you could help me through using this script so i can migrate my users and posts from simple press to phpbb

  2. Hi Tom,

    I don’t have a lot of knowledge of databases myself and working on this was pretty much my introduction.

    If you have more specific questions, I’ll do my best to answer them.

  3. thanks for the quick reply.

    basically i have a site at http://www.adelysium.com/ running a simple press forum. but i dont like the fact they want to charge you for support with simple press so im looking to export all my users and posts from it to phpbb3.

    i have it installed here http://www.adelysium.com/phpBB3/ but need to get the data from one to the other.

    ive looked online for all types of converts but there doesnt seem a specific one for simple press to phpbb3.

    i have full access into the phpmyadmin in the control panel of the host and both site/forum i linked are in the same database.

    i just wondered how you execute that script to copy the data over from simple press to phpbb3.

    is it loggin into phpmyadmin and running the script, or am i thinking way off mark and its no where near as easy as that

    thanks again,
    tom

  4. I think this would actually be considered more a collection of SQL Queries than a script. It does not all have to be run at once.

    Once you are in phpmyadmin, select the database that phpBB is using. Then click on the SQL tab and you can paste the query in there and then hit ‘Go’. That’s it in a nutshell.

    Remember to make a backup of your database or databases before doing anything! There’s a good chance that you will need to make some modifications to the queries to get it to work for you.

    I was constantly making changes to the queries and rerunning them while I was figuring everything out.

  5. hi Endy, im inputting the script in segments into my phpmyadmin but im getting a message

    #1046 – No database selected

    when i try and execute any line of script.

    phpbb_forums; etc matched what i have in my table of the left but i dont understand how i tell it what database it should be looking at. i would assume as im logged into that database it would know ^^ or do i have to have an extra line at the start of the script code telling it to look in a specific database?

    thanks again

  6. ok so i sorted that and im able to start using the script. i seem to have moved the posts and topics over fine but im having an issue with the users.

    this bit of code is giving me an error

    #1054 – Unknown column ‘loginname’ in ‘field list’

    below is the script

    INSERT INTO phpbb_users
    (user_id, user_type, group_id, username, username_clean, user_password, user_pass_convert, user_email, user_website, user_lastvisit, user_regdate, user_timezone, user_dst, user_lang, user_dateformat, user_style, user_options, loginname, loginname_clean)

    SELECT ID + @user_id_jump AS user_id, 0, 2, display_name, LOWER(REPLACE(display_name, ‘.’, ‘_’)), REPLACE(user_pass, ‘$P$’, ‘$H$’), 0, user_email, user_url, unix_timestamp(lastvisit), unix_timestamp(user_registered), 0, 0, ‘en_us’, ‘F jS, Y, g:i a’, 1, 230271, user_login, LOWER(REPLACE(user_login, ‘.’, ‘_’))

    FROM wp_users

    WHERE ID > 1;

  7. This is why I mentioned that I was using the Separate Username and Login mod for phpBB. WordPress by default can have a login name that is not the displayed user name. phpBB by default has just a user name.

    I like the little bit of added security of not having the displayed user name be the same as the login name, so to carry that over from WordPress to phpBB, I had to add the Separate Username and Login mod.

    If that’s not important, then you will have to change the query so that it doesn’t try to transfer something into loginname and loginname_clean. This may mean that you also want to change what gets transferred into username and username_clean as well.

    I don’t know if you noticed, but for every entry in that query for phpbb_users there is a corresponding one from wp_users.

  8. ok il will give it a try and edit the query. i know im a pain but i may have trouble doing it or mess it up in some way.

    next time you see this would it be possible for you to paste the edited query here incase i get into a mess :D

    just the bit of code for the users. taking the wordpress usernames over to the phpbb usernames

  9. if i remove the login and login clean i get

    #1136 – Column count doesn’t match value count at row 1

    i know you said i would need to change the corresponding one in the wp_user but im having trouble seeing what to change.

    if you could paste me what i would need just for a straight up copy across of the usernames from one to the other i would really appreciate it.

    thanks again :)

  10. Since I don’t know whether you want to use the login name or the display name from WordPress as the user name in phpBB, I’m not exactly sure what the query should look like.

    INSERT INTO phpbb_users
    (user_id, user_type, group_id, username, username_clean, user_password, user_pass_convert, user_email, user_website, user_lastvisit, user_regdate, user_timezone, user_dst, user_lang, user_dateformat, user_style, user_options)

    SELECT ID + @user_id_jump AS user_id, 0, 2, display_name, LOWER(REPLACE(display_name, ‘.’, ‘_’)), REPLACE(user_pass, ‘$P$’, ‘$H$’), 0, user_email, user_url, unix_timestamp(lastvisit), unix_timestamp(user_registered), 0, 0, ‘en_us’, ‘F jS, Y, g:i a’, 1, 230271

    FROM wp_users

    WHERE ID > 1;

    I think that should use the display name from WordPress as the username for phpBB.

  11. yeah the display name from wordpress for the username in phpbb is fine.

    running that script im getting the following message :(

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘:i a’, 1, 230271 FROM wp_users WHERE ID > 1′ at line 4

  12. I’m not seeing anything that jumps out at me. The error is basically saying that the SQL syntax isn’t right and giving the general location where it’s wrong.

    When you ran that last query, did you use SET @user_id_jump = 51; before it?

  13. Yes. The @user_jump_id is basically a variable and so you need to set that variable when you run a query that uses it. So you would have the SET @ user_jump_id = 51; and the other part all in the query box.

  14. hi endy i tried,

    SET @user_id_jump = 51;

    INSERT INTO phpbb_users
    (user_id, user_type, group_id, username, username_clean, user_password, user_pass_convert, user_email, user_website, user_lastvisit, user_regdate, user_timezone, user_dst, user_lang, user_dateformat, user_style, user_options)

    SELECT ID + @user_id_jump AS user_id, 0, 2, display_name, LOWER(REPLACE(display_name, ‘.’, ‘_’)), REPLACE(user_pass, ‘$P$’, ‘$H$’), 0, user_email, user_url, unix_timestamp(lastvisit), unix_timestamp(user_registered), 0, 0, ‘en_us’, ‘F jS, Y, g:i a’, 1, 230271

    FROM wp_users

    WHERE ID > 1;

    and i get the following

    —————————————-

    Error

    SQL query:

    INSERTINTO phpbb_users(

    user_id,
    user_type,
    group_id,
    username,
    username_clean,
    user_password,
    user_pass_convert,
    user_email,
    user_website,
    user_lastvisit,
    user_regdate,
    user_timezone,
    user_dst,
    user_lang,
    user_dateformat,
    user_style,
    user_options
    )
    SELECT ID + @user_id_jumpAS user_id, 0, 2, display_name, LOWER( REPLACE( display_name, ‘.’, ‘_’ )) , REPLACE( user_pass, ‘$P$’, ‘$H$’ ) , 0, user_email, user_url, unix_timestamp( lastvisit ) , unix_timestamp( user_registered ) , 0, 0, ‘en_us’, ‘F jS, Y, g : i a’, 1, 230271
    FROM wp_users
    WHERE ID >1;

    MySQL said:
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘:i a’, 1, 230271

    FROM wp_users

    WHERE ID > 1′ at line 4

    ——————————

    to be honest without your help i would have given up a long time ago and just carried on with simple press. but now i have the theme all sorted and the posts and topics have copied across the only thing i need now are these users and im all set.

    if you have a contact email address i would be happy to sort you out the phpmyadmin details and forum login etc so you could have a look yourself ^^

  15. I don’t mind answering questions, but I’m not comfortable with going in and doing this for someone else and taking responsibility for it. Then it becomes more of a service than just friendly help.

    However, you are really close, so don’t get too frustrated. I know it took me at least a week to get everything done and to transfer my forum. (Might have been longer, my memory ain’t what it used to be! :) )

    What you may want to try is to restore a backup of your database from just before you tried to run any of the queries. Then make the changes to the query above and rerun the whole thing like it’s the first time.

    I have this feeling that it’s getting hung up on fragments of incomplete changes made by the previously ran queries. This is why when I was working on it I was constantly restoring previous backups.

  16. Thanks a lot for this script! It really helped me a lot to convert my Simply:Crap to phpBB! I only had about 2000 posts from 8 users, so I personally choose the route of manually adding the users to phpBB, using the exact same user-ID’s (shifting the ones taken by bots upwards) and also created all 40 board manually, again using the exact same ID’s. That way there was no need for fancy tracking and editing post and user-ID’s, everything remained the same.

    The only problem I had in the end was that all posts had no title in the database. Not sure if that was because of your script or my edits.

    Normally, you won’t notice missing titles, because phpBB will take the topic_id and read the title from the topic instead. But I ran into serious crap when trying to edit posts (then the title was missing) or when indexing posts (then all titles of all posts would be gone).

    So, if anyone has the same problem of missing titles in posts, I wrote a little script that will blindly run through ALL your POSTS, get the title from the TOPIC and write it back to the POST. Furthermore, it will add “Re: ” in front if the post is a reply and not the first post in the topic.

    You can find the script here: http://ruben.cc/fix-phpbb-post-titles

    Be sure to read all the warnings and stuff first!!! ;-) I’m not a PHP or SQL guru, so the code is probably sloppy, but it works.

    Thanks again for helping me convert from Simply:Crap to phpBB

  17. When transferring the posts, post_subject is indeed made blank. I don’t recall how Simple:Press handles them, but to have them transfer it may be as simple as pointing the query for post_subject to the relevant database entry in Simple:Press.

    I haven’t had any issues myself, but I went ahead and ran your script anyways. It worked flawlessly. Thanks Ruben. :)

  18. I’m game to give this a go – I have installed phpbb3 in a folder on the website. What I don’t understand is where to start? At the beginning there is ‘

    — SET NAMES utf8;

    002

    — SET SQL_MODE=”;

    003

    — SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

    004

    — SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO';

    005

    ‘ which I assume is setting up some things in PHPBB?
    After that do I take each query and paste it into the relevant database, and copy the answers over ?

    Sorry to be so dumb. I’ve done a few things in MySql but nothing like this, never used the query option.

    Hearing that your code works is very encouraging and I’ll learn by doing this, if only I had a clear idea of the basic steps to take- from the beginning!!

    Please can you can get me started ?
    Rob

  19. Hi Rob,

    Anything that starts off with “–” is commented out, meaning that it will be ignored. In most cases, that will be directions or to explain what something is.

    Typically this will be copy and pasted as a sql query in phpMyAdmin. If you are unsure how to access phpMyAdmin, you’ll have to check with your hosting provider for the details.

    You may want to review the comments by Tom and my replies to them. It might be easier to start off by installing the Separate Login and User Names Mod for phpBB3.

    Remember to backup your databases before starting and I do highly recommend trying this out in a test environment first. Being unfamiliar with databases when I started off, most of my time seemed to be spent deleting and restoring the database while I tried to figure out how to get this to work the way I wanted to.

    Also remember, this is not one-size-fits-all solution, it will most likely need some tweaks to work the way you want it to work.

    If you have any more questions, feel free to ask.

  20. Thanks Endy, I have changed the MD5# passwords and a few things like that in MTSql. I will certainly backup the existing DB
    The new installation of PHPBB3 is on the same website, in a folder.
    At worst I was going to close the old forum and start afresh – it’s a car club and isn’t very active. So anything I do manage to import is a better outcome than that!
    I have read through the comments here and I will be reading through a few more times!!
    I understand commenting out – I’m just unsure on how to do these things, but like Tom, I expect it will become clearer as I get started.

    As the PHPBB is a clean install do I still need to do the deleting?

    I can see I have to move the user ID up, but is that in Joomla? or Simple Press forum?
    I’m sorry to ask questions, i don’t want you to regret posting your very handy code.
    Rob

  21. The deleting that I was referring to was after testing something. I would then go back in and restore the database from the backup until I had the whole thing working the way I wanted it to. This was in a test environment, of course.

    When you say Joomla, did you mean to say WordPress? Because Joomla and WordPress are 2 different things. I’m going to assume you meant WordPress since Simple Press is made for WordPress.

    I won’t go into all of the details, but to get all of the user information I wanted transferred from Simple Press and WordPress, I had to pull from both databases, not just one. So the answer is, you are moving the user id up from both.

    Don’t apologize for asking questions. I do my best to answer them, though I am by no means an expert.

  22. OK, I am such a noobe at MySql – I have transferred wp_users, wp_sfmembers, wp_sfforums, wp_sfgroups, wp_sftopics and wp_sfposts to the phpbb3 database

    Next thing was to see where to run the next instructions- I guessed it was entered in the box in the SQl page?

    I pasted in the uncommented scripts, at first one at a time, like
    ‘DELETE FROM phpbb_forums;’

    sometimes I put a few lines in ie
    “DELETE FROM phpbb_topics_track;
    DELETE FROM phpbb_posts;
    DELETE FROM phpbb_acl_groups WHERE forum_id > 0;”

    I didn’t get any error messages until I pasted in ‘INSERT INTO phpbb_users
    (user_id, user_type, group_id, username, username_clean, user_password, user_pass_convert, user_email, user_website, user_lastvisit, user_regdate, user_timezone, user_dst, user_lang, user_dateformat, user_style, user_options, loginname, loginname_clean)’

    and the error message is “#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 2″

    Am I on the right track? Pasting in the right place? What is the error, I can’t see any quote marks ?

  23. You are on the right track.

    When you tried the INSERT INTO, did you include the complete statement? That would be the INSERT INTO, SELECT, FROM, and WHERE.

    Another thing to keep in mind that this is where Tom had some issues. If you do not have the Separate Login and User Names installed on your phpBB forum, you will need to make some changes to the queries dealing with users.

  24. Ok, I should have copied the complete set of code – thanks, and obviously my guess to insert this into the box in the SQl page was correct.
    Because i got an error, i assume nothing was done and therefore the db isn’t now corrupt?

    I don’t have the Separate Login and User Names installed on my phpBB forum- I’ll go look for that and see how to do that, because I think it will be easier if I copy what you did exactly.

  25. I’ve installed automod and then used it to install Separate Login and User Names
    I got 5 errors – I’m hoping that is because the tables on the new forum are still empty?

    ALL ERRORS: ‘The Find specified by the MOD could not be found ‘
    1Find
    function login_db(&$username, &$password)

    2 Find
    $sql = ‘SELECT user_id, username, user_password, user_passchg, user_pass_convert, user_email, user_type, user_login_attempts
    FROM ‘ . USERS_TABLE . ”
    WHERE username_clean = ‘” . $db->sql_escape(utf8_clean_string($username)) . “‘”;

    3 Find
    if (($data[‘new_password’] || ($auth->acl_get(‘u_chgemail’) && $data[‘email’] != $user->data[‘user_email’]) || ($data[‘username’] != $user->data[‘username’] && $auth->acl_get(‘u_chgname’) && $config[‘allow_namechange’])) && !phpbb_check_hash($data[‘cur_password’], $user->data[‘user_password’]))

    4
    Find
    $messenger->headers(‘X-AntiAbuse: Username – ‘ . $user->data[‘username’]);

    5 Find
    $messenger->headers(‘X-AntiAbuse: Username – ‘ . $user->data[‘username’]);

  26. I continued with your script, from where I failed yesterday -ran

    INSERT INTO phpbb_users
    (user_id, user_type, group_id, username, username_clean, user_password, user_pass_convert, user_email, user_website, user_lastvisit, user_regdate, user_timezone, user_dst, user_lang, user_dateformat, user_style, user_options, loginname, loginname_clean)
    SELECT ID + @user_id_jump AS user_id, 0, 2, display_name, LOWER(REPLACE(display_name, ‘.’, ‘_’)), REPLACE(user_pass, ‘$P$’, ‘$H$’), 0, user_email, user_url, unix_timestamp(lastvisit), unix_timestamp(user_registered), 0, 0, ‘en_us’, ‘F jS, Y, g:i a’, 1, 230271, user_login, LOWER(REPLACE(user_login, ‘.’, ‘_’))
    FROM wp_users
    WHERE ID > 1;

    and received this error – #1054 – Unknown column ‘loginname’ in ‘field list’

    I don’t see loginname mentioned in the errors while installing the mod, so unsure if that is related.

    Should I start again, from the beginning, now that I have that mod installed? I’m feeling a lot more confident about what I’m doing now, thanks for the help, sorry for 3 posts in a row. This is an interesting challenge and I’m learning heaps.

    I should also mention that when I began, I ignored this
    — SET NAMES utf8;
    — SET SQL_MODE=”;
    — SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
    — SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO';
    Should I uncomment that and use it at the beginning?

    I also ignored these, because the forum was a clean, empty, installation
    DELETE FROM phpbb_forums;
    DELETE FROM phpbb_topics;
    DELETE FROM phpbb_topics_posted;
    DELETE FROM phpbb_topics_track;
    DELETE FROM phpbb_posts;
    DELETE FROM phpbb_acl_groups WHERE forum_id > 0

    I’m thinking now I should have followed the instructions more closely!!
    Rob

  27. You need to finish installing the Separate Login and User Names. What I can tell you about those errors are that you need to manually go in and make the changes for those 5 things that Automod couldn’t Find.

    The error #1054 – Unknown column ‘loginname’ in ‘field list’ is because the query is looking for a column named ‘loginname’ in ‘phpbb-users’, but since the plugin hasn’t been fully installed, that column doesn’t exist yet.

    Do not worry about uncommenting that stuff at the beginning. I think that’s one of those, “If you don’t know what it means, leave it alone” things.

    As for the DELETE FROM’s, I think I misunderstood what you were asking the other day. Even on a clean install, you want to go ahead and run the DELETE FROM’s. That’s to make sure there are no conflicts when importing the new data. On a fresh install there is always the example forum and post made and all of that and we want that gone before attempting to import the new data.

    Another potential problem that you may run into is when you get to “– updates unix timestamp for timezone (GMT -6) and daylight savings years 2011 and 2012″. That may have just been me trying to be a little too perfectionist. I can’t remember what happens if you comment that whole section out, but you may want to try without using that query first and see what happens. That’s also one of those things that you can go back in later and play around with once everything else is working if you need to.

  28. Im trying to convert, i have modified the script to deal with the newer version of phpbb.

    But i having this problem atm.

    — 2a. initial users transfer – assumes all users except 1st (which is admin) are normal users. admin is not transferred. — group_id: 2 == REGISTERED — user_type: 0 == USER_NORMAL — user_style: 2 == my newly installed style. 1 = prosilver, 2 = my style — user_permissions: ?! == how is this calculated? — user_email_hash: ?! == how is this calculated? INSERT INTO phpbb_users ( user_id, user_type, group_id, username, username_clean, user_password, user_pass_convert, user_email, user_website, user_lastvisit, user_regdate, user_timezone, user_dst, user_lang, user_dateformat, user_style, user_options ) SELECT user_id+@user_id_jump as user_id, 0, 2, user_login_attempts, lower(replace(user_login_attempts, ‘.’, ”)), user_password, 1, user_email, user_website, unix_timestamp(user_regdate), unix_timestamp(user_regdate), 1, 1, ‘sr’, ‘D, d M Y, G:i’, 2, 895 FROM phpbb_users WHERE user_id > […]

    MySQL sa:
    #1062 – Duplicate entry ‘0’ for key ‘username_clean’

  29. TK, you have a couple of problems going on there.

    First, the error you are getting I think is because you are trying to INSERT data where data already exists. You need to clear the data that’s there before you can insert. Otherwise you need to be using ALTER instead of INSERT.

    Second, the changes you have made to the query won’t work. You’ve changed “FROM wp_users” to “FROM phpbb_users”. This means you are pulling data from where you are trying to put it.

    You’ve also changed display_name in the query to user_login_attempts. They are two very different things.

    That’s what I see at the moment. If you have any other questions or if you need me to clarify anything let me know.

  30. Just a note on passwords. Although the hashes are compatible, by just switching $H$ and $P$, there are some caveats. Specifically, phpBB trims and entitises special characters input in the password field before generating a hash. So any passwords with , &, “, spaces, etc. would stop working.

    WP-United works around this problem — so one way would be to install that and import users across from WP to phpBB using that, after using your script above to import all the forums and posts.

  31. It’s good to see that WP-United is being worked on again. I will be looking into it.

    One problem that I see in importing users using WP-United, is that you still need to match up the user id’s. I’m not sure how WP-United handles that when it imports, but that would need to be taken care of or you end up with forum posts that are associated with the wrong forum member.

    Another issue is that if you are converting from Simple:Press, there is user information you need from Simple:Press that won’t be picked up just by importing users from WP-United.

    That’s not to say that it can’t be done, just that it would have it’s own problems that need to be worked through. My solution is far from perfect and is certainly not polished and can take some work to get it working as well.

    As far as the passwords, if a user ended up not being able to log in after the conversion they can reset their password with phpBB using the “I forgot my password” link.

  32. Thanks for the post. I know this post isn’t updated these days, but I thought I’d take a shot. Messed with your code and got it working about 3 hours later. All is moved over, followed your instructions to a T. Now I try to log in with several different user accounts and it doesn’t recognize any of them. Tried to reset passwords in PHPMyAdmin and nothing. Tried to register an account and can’t. So something is broken somewhere. I recognize the ID numbers aren’t the same as they were in WP. But I ran your code exactly as written. So not sure if that’s it. But to not be able to log in at all, and create a new user in PHPMyAdmin and still not login, is bizarre! Anyone else have any issues? Thanks for any help you may have.

  33. Hey Josh, did you have the Separate Login and User Name mod installed for phpBB before you ran the script? If you didn’t I can think of ways that would possibly interfere with logging, however, I can’t think of what else might cause problems with registering.

    I will say that I migrated another forum a few months ago and I had some problems myself. That made me realize that I need to post an update and do things just a little bit differently.

    My plan is to break the script up into parts instead of having it be run all at once. I think that’s where a lot of the problems come from. I’ll probably also show how to do it without needing the Separate Login and User Name mod.


Add Comment Register

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>