Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Convert newer-style bbPress forums (where content is stored as WordPress posts) to phpBB. Based on code by primehifi (http://mark.tl/1l9HyRR)
/* If anything goes wrong, let's not muck up the database */
START TRANSACTION;
/* Clear tables and reset IDs */
TRUNCATE TABLE phpbb_forums;
TRUNCATE TABLE phpbb_topics;
TRUNCATE TABLE phpbb_topics_posted;
TRUNCATE TABLE phpbb_topics_track;
TRUNCATE TABLE phpbb_posts;
DELETE FROM phpbb_acl_groups WHERE forum_id > 0;
/* There is a difference in user_id.
In bbPress, your admin was 1, in phpBB3 is 2 (Anonymous is 1).
phpBB3 also has 50 bots preinstalled, thus member user IDs from bbPress must
be pushed up: 2 becomes 53, 3 becomes 54 etc. */
SET @user_id_jump = 51;
/* Set the style ID. I'm using the Oxygen style but you can change this
to something else */
SET @style_id = 3;
/* Clear all users except anonymous, admins 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);
/* Transfer non-admin users */
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 /* New ID */,
0 /* User type ("Normal user") */,
2 /* Group ("Registered") */,
user_login /* Username */,
LOWER(REPLACE(user_login, '.', '_')) /* Sanitised username */,
user_pass /* Passwords */,
1 /* Convert passwords */,
user_email /* Email address */,
user_url /* URL */,
UNIX_TIMESTAMP(user_registered) /* Date of the user's last activity */,
UNIX_TIMESTAMP(user_registered) /* Date the user signed up */,
1 /* Default timezone */,
1 /* Overbse aylight saving time */,
'en' /* Language */,
'D, d M Y, G:i' /* Date format */,
@style_id /* Style (theme, basically) */,
895 /* Flags */
FROM
wp_users
WHERE
ID > 1;
/* Transfer membership to groups */
INSERT INTO
phpbb_user_group (
group_id,
user_id,
group_leader,
user_pending
)
SELECT DISTINCT
group_id /* Group ID */,
user_id /* User ID */,
0 /* Group leader */,
0 /* User join request is pending */
FROM
phpbb_users
WHERE
user_id > @user_id_jump + 1;
/* Transfer forums */
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
f.id /* Forum ID */,
0 /* Parent ID */,
0 /* Left ID */,
0 /* Right ID */,
f.post_title /* Title */,
f.post_content /* Description */,
1 /* Forum type ("post-based") */,
(
SELECT
COUNT(*)
FROM
wp_posts AS r
INNER JOIN
wp_posts AS t ON r.post_parent = t.id
WHERE
t.post_parent = f.id
AND
r.post_type = 'reply'
AND
t.post_type = 'topic'
) /* Number of posts */,
(
SELECT
COUNT(*)
FROM
wp_posts AS t
WHERE
t.post_parent = f.id
AND
t.post_type = 'topic'
) /* Number of topics */,
(
SELECT
COUNT(*)
FROM
wp_posts AS t
WHERE
t.post_parent = f.id
AND
t.post_type = 'topic'
) /* Number of topics */,
(
SELECT
id
FROM
wp_posts AS p
WHERE
p.post_parent = f.id
AND
p.post_type = 'topic'
ORDER BY
p.post_date DESC LIMIT 1
) /* Last post ID */,
(
SELECT
post_author + @user_id_jump
FROM
wp_posts AS p
WHERE
p.post_parent = f.id
AND
p.post_type = 'topic'
ORDER BY
p.post_date DESC LIMIT 1
) /* Last poster ID */,
(
SELECT
post_title
FROM
wp_posts AS p
WHERE
p.post_parent = f.id
AND
p.post_type = 'topic'
ORDER BY
p.post_date DESC LIMIT 1
) /* Last post subject */,
(
SELECT
UNIX_TIMESTAMP(post_date)
FROM
wp_posts AS p
WHERE
p.post_parent = f.id
AND
p.post_type = 'topic'
ORDER BY
p.post_date DESC LIMIT 1
) /* Last post time */,
(
SELECT
u.user_login
FROM
wp_posts AS p
INNER JOIN
wp_users AS u ON p.post_author = u.id
WHERE
p.post_parent = f.id
AND
p.post_type = 'topic'
ORDER BY
p.post_date DESC LIMIT 1
) /* Last poster name */,
32 /* Flags */
FROM
wp_posts AS f
WHERE
f.post_type = 'forum';
-- Update Left and Right IDs for forums
UPDATE
phpbb_forums
SET
left_id = (forum_id - 1) * 2 + 1,
right_id = (forum_id - 1) * 2 + 2;
/* Set read-only permissions for the Guests group */
INSERT INTO
phpbb_acl_groups (
group_id,
forum_id,
auth_role_id
)
SELECT
1,
forum_id,
17
FROM
phpbb_forums;
/* Set standard permissions for the Registered Users group */
INSERT INTO
phpbb_acl_groups (
group_id,
forum_id,
auth_role_id
)
SELECT
2,
forum_id,
15
FROM
phpbb_forums;
/* Set standard permissions for the COPPA group */
INSERT INTO
phpbb_acl_groups (
group_id,
forum_id,
auth_role_id
)
SELECT
3,
forum_id,
15
FROM
phpbb_forums;
/* Set poll permissions for mods */
INSERT INTO
phpbb_acl_groups (
group_id,
forum_id,
auth_role_id
)
SELECT
4,
forum_id,
21
FROM
phpbb_forums;
/* Set full permissions for adsmin */
INSERT INTO
phpbb_acl_groups (
group_id,
forum_id,
auth_role_id
)
SELECT
5,
forum_id,
14
FROM
phpbb_forums;
/* Add other permissions for bots */
INSERT INTO
phpbb_acl_groups (
group_id,
forum_id,
auth_role_id
)
SELECT
6,
forum_id,
19
FROM
phpbb_forums;
/* Transfer the 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.id /* Topic ID */,
t.post_title /* Topic title */,
UNIX_TIMESTAMP(t.post_date) /* Date it was posted */,
UNIX_TIMESTAMP(t.post_date) /* Date it was updated */,
CASE t.post_author
WHEN 1 THEN
t.post_author + 1
ELSE
t.post_author + @user_id_jump
END /* Set the poster ID, remembering to up the user ID if it wasn't posted by an admin */,
IFNULL(
(
SELECT
lu.user_login
FROM
wp_posts AS l
INNER JOIN
wp_users AS lu ON l.post_author = lu.id
WHERE
l.post_parent = t.id
AND
l.post_type = 'reply'
ORDER BY
l.post_date
LIMIT 1
),
u.user_login
) /* Username of the first poster (or topic creator if none exists) */,
IFNULL(
(
SELECT
CASE l.post_author
WHEN 1 THEN
l.post_author + 1
ELSE
l.post_author + @user_id_jump
END
FROM
wp_posts AS l
WHERE
l.post_parent = t.id
AND
l.post_type = 'reply'
ORDER BY
l.post_date DESC
LIMIT 1
),
CASE t.post_author
WHEN 1 THEN
t.post_author + 1
ELSE
t.post_author + @user_id_jump
END
) /* Set the last poster (or topic creator, if non exists) ID, remembering to up the user ID if it wasn't an admin */,
(
IFNULL(
(
SELECT
lu.user_login
FROM
wp_posts AS l
INNER JOIN
wp_users AS lu ON l.post_author = lu.id
WHERE
l.post_parent = t.id
AND
l.post_type = 'reply'
ORDER BY
l.post_date DESC
LIMIT 1
),
u.user_login
)
) /* Username of the last poster (or topic creator if none exists) */,
t.post_parent /* Forum ID */,
(
SELECT
COUNT(*)
FROM
wp_posts AS r
WHERE
r.post_parent = t.id
AND
r.post_type = 'reply'
) /* Number of replies */,
(
SELECT
r.id
FROM
wp_posts AS r
WHERE
r.post_parent = t.id
AND
r.post_type = 'reply'
ORDER BY
r.post_date
LIMIT 1
) /* First reply ID */,
(
SELECT
r.id
FROM
wp_posts AS r
WHERE
r.post_parent = t.id
AND
r.post_type = 'reply'
ORDER BY
r.post_date DESC
LIMIT 1
) /* Last reply ID */
FROM
wp_posts AS t
INNER JOIN
wp_users AS u ON t.post_author = u.id
WHERE
t.post_type = 'topic';
/* Transfer replies */
INSERT INTO
phpbb_posts (
post_id,
topic_id,
forum_id,
poster_id,
poster_ip,
post_time,
post_username,
post_subject,
post_text
)
SELECT
p.id /* Post ID */,
t.id /* Topic ID */,
f.id /* Forum ID */,
CASE
p.post_author
WHEN 1 THEN
p.post_author + 1
ELSE
p.post_author + @user_id_jump
END /* The ID of the poster, upping the number for non-admins */,
(
SELECT
m.meta_value
FROM
wp_postmeta AS m
WHERE
m.post_id = p.id
AND
meta_key = '_bbp_author_ip'
) /* Poster IP address */,
UNIX_TIMESTAMP(p.post_date) /* Date of the post */,
u.user_login /* Username of the poster */,
'' /* Empty subject */,
p.post_content /* Post text */
FROM
wp_posts AS p
INNER JOIN
wp_users AS u ON p.post_author = u.id
INNER JOIN
wp_posts AS t ON p.post_parent = t.id
INNER JOIN
wp_posts AS f ON t.post_parent = f.id
WHERE
p.post_type = 'reply'
AND
t.post_type = 'topic'
AND
f.post_type = 'forum';
/* Update connections 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;
/* Rehash email addresses */
UPDATE
phpbb_users
SET
user_email_hash = CONCAT(CRC32(LOWER(user_email)), LENGTH(user_email));
COMMIT;
/* If we're happy, let's save our changes */
@geigy

This comment has been minimized.

Copy link

commented Feb 2, 2016

Fascinated by this example. I am wondering about attachments?

@andrewhl

This comment has been minimized.

Copy link

commented May 31, 2016

What version of PHPBB was this script intended for? My table names are prefixed phpbbfi, and the phpbb_forums table has different columns (e.g., phpbb_forums.forum_posts doesn't exist).

@pann

This comment has been minimized.

Copy link

commented Feb 7, 2018

I forked this and made an update that seems to work for me to export from bbpress 1.8.3 to ubuntu release 3.0.14-1ubuntu1.

@rioforce

This comment has been minimized.

Copy link

commented Apr 22, 2018

Thanks for this script! I got it to work with phpBB 3.0... ALMOST. The newer versions have a different database structure. I say almost because it didn't import topic posts for some reason, just replies. And user accounts didn't connect, but that could be due to the faulty bbPress.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.