bbPress to phpBB3 - SQL converter

Converting from other board software? Good decision! Need help? Have a question about a convertor? Wish to offer a convertor package? Post here.
Scam Warning
Endy
Registered User
Posts: 76
Joined: Sat Dec 29, 2001 10:32 pm
Location: Iowa
Contact:

Re: bbPress to phpBB3 - SQL converter

Post by Endy » Mon Aug 06, 2012 2:34 pm

Just wanted to say thank you because this helped me. I used it as a base and changed it to convert Simple:Press to phpBB3. I started with a fresh install of phpBB3 with the Separate Login and User Name mod installed.

Code: Select all

-- 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 >= 132054480 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;
There are more details about what I did here.

edgars
Registered User
Posts: 1
Joined: Fri Aug 17, 2012 7:30 am

Re: bbPress to phpBB3 - SQL converter

Post by edgars » Fri Aug 17, 2012 8:00 am

I`m running bbpress forum with 15 000 posts and ~900 files/pictures what are attached to topics using "bbPress Attachments" plug-in. As separate bbpress forum development is stopped and now its like WP plug-in, I`m looking around to migrate it to phpbb.

Has somebody migrated to phpbb pictures/files attached to bbpress post with this script or any other tool? Or any hints how to make it manually with some sql commands?

atariguy
Registered User
Posts: 5
Joined: Mon Oct 07, 2013 6:51 pm

Re: bbPress to phpBB3 - SQL converter

Post by atariguy » Wed Oct 09, 2013 5:00 am

Anyone have a converter for the newer version of bbpress? It uses tables like wp_users and wp_usermeta and all the forum postings are stored with everything else in wp_posts.

jaruzelskie
Registered User
Posts: 2
Joined: Thu Dec 12, 2013 3:19 pm

Re: bbPress to phpBB3 - SQL converter

Post by jaruzelskie » Thu Dec 12, 2013 5:57 pm

It would be great if someone could write it for the newest version of bbpress.
Meanwhile I'm trying to do it on my own..

jaruzelskie
Registered User
Posts: 2
Joined: Thu Dec 12, 2013 3:19 pm

Re: bbPress to phpBB3 - SQL converter

Post by jaruzelskie » Fri Dec 13, 2013 1:00 pm

I've used the cms2cms demo and it works except attachments. To move all of my data the price was something above 150$.. its to much for me and still i will try on my own.

parcs
Registered User
Posts: 15
Joined: Tue Feb 04, 2014 12:46 pm

Re: bbPress to phpBB3 - SQL converter

Post by parcs » Tue Feb 04, 2014 12:49 pm

Hi, I successfully used the script, and I managed to migrate my bbpress forum into phpbb3. The only thing I couldn't migrate were the Private Messages. I have lots of them, does anybody knows how to migrate them?
Thank you very much!

thekendog
Registered User
Posts: 1
Joined: Wed Oct 29, 2014 6:36 pm

Re: bbPress to phpBB3 - SQL converter (bbPress 2.0)

Post by thekendog » Wed Oct 29, 2014 6:40 pm

This worked for me. Got the script from here...

https://gist.github.com/iamsteadman/9684093

Had to modify lines 481-488 in order to get the topics to show up as the first post in the topic. Worked like a charm for me. No guarantees it'll work for you. Just sharing what worked for me.

Code: Select all

/* 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 OR p.id = t.id
INNER JOIN
    wp_posts AS f ON t.post_parent = f.id
WHERE
    p.post_type in ('reply','topic')
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 */

tkosmidis
Registered User
Posts: 3
Joined: Fri Apr 03, 2015 8:07 am

Re: bbPress to phpBB3 - SQL converter

Post by tkosmidis » Fri Apr 03, 2015 8:11 am

from the above code i get:
¨SQL query: Documentation

Code: Select all

/* 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 [...]

MySQL said: Documentation
[b]#1054 - Unknown column 'user_pass_convert' in 'field list' [/b]
wp and phpbb tables are all in same DB...
any idea what to do?

Locked

Return to “[3.0.x] Convertors”