Converting Simple:Press to phpBB3

By Endy • August 6, 2012 @ 9:01 am • 38 comments

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.

-- 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';


-- 0. BACKUP!

-- really, *backup* your default installation of phpBB3

-- if something goes wrong, you can get it back to what it was and start over
-- 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
-- 1. clear tables

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;

-- there is a difference in user_id.

-- in WordPress, your admin was 1, in phpBB3 is 2 (Anonymous is 1).

-- phpBB3 also has 50 bots preinstalled, thus member user_ids from WordPress must be pushed up:

--  2 becomes 53, 3 -> 54 etc.
SET @user_id_jump = 51;
-- now clear all users except Anon, Admin and Bots

DELETE FROM phpbb_user_group WHERE user_id > (@user_id_jump+1);

DELETE FROM phpbb_users WHERE user_id > (@user_id_jump+1);

DELETE FROM phpbb_acl_users WHERE user_id > (@user_id_jump+1);
-- clears user registration dates

UPDATE phpbb_users

SET user_regdate = '';
-- 2. transfer users
-- first add lastvisit column to wp_users, only need to run once otherwise commented out

ALTER TABLE wp_users

ADD lastvisit datetime;
-- 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

UPDATE wp_users

SET lastvisit = (SELECT lastvisit FROM wp_sfmembers WHERE user_id = ID)

WHERE ID > 1;
-- 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, 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;
-- sets original registration date for admin account because admin account does get moved over

UPDATE phpbb_users

SET user_regdate = (SELECT unix_timestamp(user_registered) FROM wp_users WHERE ID= 1) WHERE user_id = 2;
-- updates unix timestamp for timezone (GMT -6) and daylight savings years 2011 and 2012

UPDATE phpbb_users

SET user_regdate = (SELECT CASE

WHEN user_regdate < 1299981600 AND (user_id = 2 OR user_id > @user_id_jump + 1) THEN user_regdate - 21600

WHEN user_regdate >= 1299981600 AND user_regdate < 1320544800 AND (user_id = 2 OR user_id > @user_id_jump + 1) THEN user_regdate - 18000

WHEN user_regdate >= 1320544800 AND user_regdate < 1331431200 AND (user_id = 2 OR user_id > @user_id_jump + 1) THEN user_regdate - 21600

WHEN user_regdate >= 1331431200 AND user_regdate < 1351994400 AND (user_id = 2 OR user_id > @user_id_jump + 1) THEN user_regdate - 18000

ELSE user_regdate

END);
-- 2b. update user_group table

INSERT INTO phpbb_user_group (

group_id, user_id, group_leader, user_pending

)

SELECT DISTINCT group_id, user_id, 0, 0

FROM phpbb_users

WHERE user_id > (@user_id_jump+1);
-- 3. transfer forums

-- assumes that regular forums have one parent and no subforums

-- forum_type: 0 == Parent/Group Forum

-- forum_type: 1 == Regular Forum

SET @max_group_id = (SELECT MAX(group_id) FROM wp_sfgroups);
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 )

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

FROM wp_sfforums f;
-- UPDATE phpbb_forums f

-- SET

-- forum_id = (SELECT f.forum_id + MAX(group_id) FROM wp_sfgroups);
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 )

SELECT group_id, 0, 0, 0, group_name, '', 0, 0, 0, 0, 0, 0, '', 0, '', 48

FROM wp_sfgroups f;
-- 3a. update last post* data

UPDATE phpbb_forums f

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),

forum_last_poster_name = (SELECT username FROM phpbb_users u WHERE u.user_id = f.forum_last_poster_id),

forum_last_post_time = (SELECT unix_timestamp(post_date) FROM wp_sfposts WHERE post_id = f.forum_last_post_id);
-- 3b. update left_id and right_id

-- see: http://www.sitepoint.com/article/hierarchical-data-database/2

-- Install phpBB3 Support Tool Kit and Run Fix Left/Right ID's, you may have to rearrange some forums after this is done
-- 3c. set forum ACL -- this sets required permissions for the forums, based on groups

--  set READONLY for GUESTS group

INSERT INTO phpbb_acl_groups (

group_id, forum_id, auth_role_id

) SELECT 1, forum_id, 17

FROM phpbb_forums;

--  set FORUMSTANDARD+POLL for REGISTERED group

INSERT INTO phpbb_acl_groups (

group_id, forum_id, auth_role_id

) SELECT 2, forum_id, 21

FROM phpbb_forums;

--  set FORUMSTANDARD+POLL for REGISTERED COPPA group

INSERT INTO phpbb_acl_groups (

group_id, forum_id, auth_role_id

) SELECT 3, forum_id, 21

FROM phpbb_forums;

--  set FORUMFULL for GLOBAL_MODS group

INSERT INTO phpbb_acl_groups (

group_id, forum_id, auth_role_id

) SELECT 4, forum_id, 14

FROM phpbb_forums;

--  set FORUMFULL for ADMINS group

INSERT INTO phpbb_acl_groups (

group_id, forum_id, auth_role_id

) SELECT 5, forum_id, 14

FROM phpbb_forums;

--  set BOTS for BOTS group

INSERT INTO phpbb_acl_groups (

group_id, forum_id, auth_role_id

) SELECT 6, forum_id, 19

FROM phpbb_forums;

-- set FORUMSTANDARD+POLL for Newly Registered

INSERT INTO phpbb_acl_groups (

group_id, forum_id, auth_role_id

) SELECT 7, forum_id, 21

FROM phpbb_forums;
-- 4. transfer topics

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 )

SELECT t.topic_id, t.topic_name, unix_timestamp(t.topic_date),

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

FROM wp_sftopics t;
-- 4a. update poster-related data

UPDATE phpbb_topics t

SET

forum_id = (SELECT forum_id + MAX(group_id) FROM wp_sfgroups),

topic_last_post_time = (SELECT unix_timestamp(post_date) FROM wp_sfposts WHERE post_id = t.topic_last_post_id),

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),

topic_first_post_id = (SELECT min(post_id) FROM wp_sfposts p WHERE p.topic_id = t.topic_id),

topic_first_poster_name = (SELECT username FROM phpbb_users u WHERE t.topic_poster = u.user_id),

topic_last_poster_name = (SELECT username FROM phpbb_users u WHERE t.topic_last_poster_id = u.user_id),

topic_replies_real = topic_replies;
-- 5. transfer posts

INSERT INTO phpbb_posts ( post_id, topic_id, forum_id, poster_id, poster_ip, post_time, post_username, post_subject, post_text )

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

FROM wp_sfposts p;
UPDATE phpbb_posts

SET

forum_id = (SELECT forum_id + MAX(group_id) FROM wp_sfgroups);
-- 6. update connection between users and topics

INSERT INTO phpbb_topics_posted ( user_id, topic_id, topic_posted )

SELECT DISTINCT topic_poster, topic_id, 1

FROM phpbb_topics;
-- 7. re-sync and re-count forums, topics and posts in phpBB3 admin, on General page

-- Install phpBB3 Support Tool Kit and Run Fix Left/Right ID's, you may have to rearrange some forums after this is done

-- Run Update Email Hashes from Support Tool Kit

-- If you copied tables over from WordPress and Simple:Press, you can delete them now

-- SET SQL_MODE=@OLD_SQL_MODE; -- 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”

Tom on August 20, 2012 at 10:10 am said:

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
Endy on August 20, 2012 at 12:25 pm said:

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.
Tom on August 20, 2012 at 1:15 pm said:

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
Endy on August 20, 2012 at 2:19 pm said:

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.
Tom on August 20, 2012 at 5:56 pm said:

ah ok, i’ll give it a try tommorrow. thanks again for the help 🙂
Tom on August 21, 2012 at 2:27 am said:

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
Tom on August 21, 2012 at 2:58 am said:

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;
Endy on August 21, 2012 at 3:11 pm said:

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.
Tom on August 22, 2012 at 3:00 am said:

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 😀

just the bit of code for the users. taking the wordpress usernames over to the phpbb usernames
Tom on August 22, 2012 at 3:11 am said:

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 🙂
Endy on August 22, 2012 at 11:42 am said:

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.
Tom on August 22, 2012 at 11:53 am said:

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
Endy on August 22, 2012 at 4:11 pm said:

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?
Tom on August 22, 2012 at 4:33 pm said:

no just the bit you gave me above. should i have?
Endy on August 22, 2012 at 6:19 pm said:

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.
Tom on August 23, 2012 at 3:44 am said:

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 ^^
Endy on August 23, 2012 at 3:05 pm said:

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.
Tom on August 24, 2012 at 5:39 am said:

ok i’ll give that a try, thanks for the quick response again 🙂
Ruben Schouten on September 15, 2012 at 6:53 am said:

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
Endy on September 15, 2012 at 9:55 am said:

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. 🙂
Rob on October 16, 2012 at 12:51 am said:

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
Endy on October 16, 2012 at 2:44 pm said:

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.
Rob on October 16, 2012 at 11:17 pm said:

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
Endy on October 17, 2012 at 4:43 pm said:

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.
Rob on October 22, 2012 at 1:41 am said:

Thanks Endy and Oops Joomla was a slip of the tongue !!
Rob on October 22, 2012 at 2:43 am said:

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 ?
Endy on October 22, 2012 at 11:19 am said:

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.
Rob on October 22, 2012 at 8:43 pm said:

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.
Rob on October 22, 2012 at 9:44 pm said:

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’]);
Rob on October 22, 2012 at 9:57 pm said:

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
Endy on October 23, 2012 at 7:40 pm said:

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.
TK on December 11, 2012 at 1:50 am said:

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’
Endy on December 11, 2012 at 7:48 am said:

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.
John Wells on January 14, 2013 at 3:36 am said:

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.
John Wells on January 14, 2013 at 3:37 am said:

the special characters were stripped in the above post. They are of course <, >, & and “.
Endy on January 14, 2013 at 6:50 am said:

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.
Josh on March 30, 2014 at 4:47 pm said:

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.
Endy on March 30, 2014 at 8:42 pm said:

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.