1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

bbPress Importer 1.0.0

Set of SQL Queries to import Data from bbPress over to xenForo

Tags:
  1. shabbirbhimani
    Compatible XF Versions:
    • 1.5
    License:
    Creative Commons license
    NOTE: This has been tested to work on a blank xenForo forum only.

    I have attached the SQL script that imports the data from bbPress into Xenforo. I could not find any script to import bbPress 2.5+ data into xenForo and even tried bbPress to phpBB to xenForo but those 2 step process had their own issues.

    So I created SQL script based on the bbPress to phpBB importer by iamsteadman on github. It imports users, forums, threads and posts. Users will need to reset their password to login to the new xenForo system.

    Here is the complete SQL code. The Wordpress table prefix is wp_qjpq_ and change as per your configuration.

    Code:
    START TRANSACTION;
    
    SET @user_id_jump = 2;
    
    INSERT INTO
        xf_user (
            user_id,
            username,
        email,
            user_group_id,
        register_date,
        last_activity
        )
    SELECT
        ID + @user_id_jump /* New ID */,
        LOWER(REPLACE(user_login, '.', '_')) /* Sanitised username */,
        user_email /* Email address */,
        2 /* Group ("Registered") */,
        UNIX_TIMESTAMP(user_registered) /* Date the user signed up */,
        UNIX_TIMESTAMP(user_registered) /* Date of the user's last activity */
    FROM
        wp_qjpq_users
    WHERE
    ID > 1;
    
    INSERT INTO
        xf_user_privacy (
            user_id,
        allow_post_profile,
            allow_send_personal_conversation
        )
    SELECT
        ID + @user_id_jump /* New ID */,
        'members',
        'members'
    FROM
        wp_qjpq_users
    WHERE
    ID > 1;
    
    INSERT INTO
        xf_user_authenticate (
            user_id,
            scheme_class
    )
    SELECT
        ID + @user_id_jump /* New ID */,
        'XenForo_Authentication_Core12'
    FROM
        wp_qjpq_users
    WHERE
    ID > 1;
    
    INSERT INTO
        xf_user_option (
            user_id
    )
    SELECT
        ID + @user_id_jump /* New ID */
    FROM
        wp_qjpq_users
    WHERE
    ID > 1;
    
    INSERT INTO xf_node (
        node_id,
        title,
        description,
        node_type_id,
        parent_node_id,
        depth
    ) SELECT
        f.id, /* forumid */
        f.post_title /* Title */,
        f.post_content /* Description */,
        'Forum',
        1,
        1
    FROM
        wp_qjpq_posts AS f
    WHERE
    f.post_type = 'forum';
    
    INSERT INTO xf_forum (
        node_id,
        message_count,
        discussion_count,
        last_post_id,
        last_post_user_id,
        last_post_username,
        last_thread_title
    ) SELECT
        f.id,
        (
            SELECT
                COUNT(*)
            FROM
                wp_qjpq_posts AS r
            INNER JOIN
                wp_qjpq_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_qjpq_posts AS t
            WHERE
                t.post_parent = f.id
            AND
                t.post_type = 'topic'
    ) /* Number of topics */,
        (
            SELECT
                id
            FROM
                wp_qjpq_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_qjpq_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
                u.user_login
            FROM
                wp_qjpq_posts AS p
            INNER JOIN
                wp_qjpq_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 */,
        (
            SELECT
                post_title
            FROM
                wp_qjpq_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 */
    FROM
        wp_qjpq_posts AS f
    WHERE
    f.post_type = 'forum';
    
    INSERT INTO
        xf_thread (
            thread_id,
        node_id,
        title,
        user_id,
        username,
        post_date
    )
    SELECT
        t.id /* Topic ID */,
        t.post_parent /* Forum ID */,
        t.post_title /* Topic title */,
        CASE t.post_author
            WHEN 1 THEN
                t.post_author + 1
            ELSE
                t.post_author + @user_id_jump
        END,
        IFNULL(
            (
                SELECT
                    lu.user_login
                FROM
                    wp_qjpq_posts AS l
                INNER JOIN
                    wp_qjpq_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) */,
        UNIX_TIMESTAMP(t.post_date) /* Date it was posted */
    FROM
        wp_qjpq_posts AS t
    INNER JOIN
        wp_qjpq_users AS u ON t.post_author = u.id
    WHERE
    t.post_type = 'topic';
    
    INSERT INTO
        xf_post (
            thread_id,
            user_id,
            username,
            post_date,
        message
        )
    SELECT
        p.id /* Topic 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 */,
        u.user_login /* Username of the poster */,
        UNIX_TIMESTAMP(p.post_date) /* Date of the post */,
        p.post_content /* Post text */
    FROM
        wp_qjpq_posts AS p
    INNER JOIN
        wp_qjpq_users AS u ON p.post_author = u.id
    WHERE
        p.post_type = 'topic';
    
    INSERT INTO
        xf_post (
            post_id,
            thread_id,
            user_id,
            username,
            post_date,
        message
        )
    SELECT
        p.id /* Post ID */,
        t.id /* Topic 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 */,
        u.user_login /* Username of the poster */,
        UNIX_TIMESTAMP(p.post_date) /* Date of the post */,
        p.post_content /* Post text */
    FROM
        wp_qjpq_posts AS p
    INNER JOIN
        wp_qjpq_users AS u ON p.post_author = u.id
    INNER JOIN
        wp_qjpq_posts AS t ON p.post_parent = t.id
    WHERE
        p.post_type = 'reply'
    AND
        t.post_type = 'topic';
    
    UPDATE xf_user AS user
    SET message_count = (
        SELECT COUNT(*)
        FROM xf_post AS post
        LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
        WHERE post.user_id = user.user_id
        AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
        GROUP BY post.user_id
    );
    COMMIT;
    Once done rebuild threads (with position and post count ticked), forums and users. Node tree will show fine in admin but not in the front end and so create a dummy forum and then delete it to rebuild the forum tree for home page.

    Will be more than happy to help with any issues that may come along.

Recent Reviews

  1. TheComputerGuy
    TheComputerGuy
    5/5,
    Version: 1.0.0
    Great job! This works flawlessly! I was able to import a 2.5+ bbpress into an empty xenforo install. This was great.