How I migrated Drupal Forum replies to WordPress. Part 2

In the first part, we have reached the point where users and forum topics are imported. There are no forum replies yet. Before we start playing with MySQL part make a backup of the whole WordPress database if you break something. And you should be at least a bit comfortable with SQL. Considering myself, I know just basics of SQL syntax, the other part is the logic and browsing internet if stuck. My SQL statements are straightforward using only insert, select, where statements. These are the most basic query structure elements and not always the most optimally used. But the logic behind is most apparent.

Next part is to take care of the Drupal database source. If your Drupal database is on the same server, you can perform queries directly from it and this is how I’ve done this. But this adds keep off the track while writing queries. You have to include database name before tables when writing queries. I recommend copying required Drupal tables into the WordPress database – the names should not interfere. For this, you should export each table individually. There is a convenient menu in phpMyAdmin.

All you need is to select the desired table (for instance ‘node’) and go to the Export tab. Select Quick and go. You will be prompted to save the SQL file locally.

In this part, we are focusing on importing forum replies. So we will need the following tables:

  • forum_index
  • users
  • comment
  • field_data_comment_body

You need to import those tables into the WordPress database before beginning. All you have to do is to tick on the WordPress database in phpMyAdmin and go to import tab. Where you can browse for exported tables before and perform GO.

Once you have imported all the required tables we can proceed to SQL. Before importing forum replies, it is convenient to create three temporary tables where we can store links between Drupal and WordPress records IDs. I created temporary tables with prefix x_, so I could later delete them. So I created two tables to track user IDs  and forums IDs:

CREATE TABLE `x_forumids` (

  `nid` int(11) NOT NULL,

  `id` int(11) NOT NULL,

  `title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL

)

And:

CREATE TABLE `x_userids` (

  `uid` int(11) NOT NULL,

  `id` int(11) NOT NULL,

  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL

)

In the x_forumids table, I have added nid – drupal forum id and id – WordPress forum id. The title serves as the link between them.

In the x_userids is same: uid – Drupal user id and id – WordPress user id. And their email is unique binder.

Now we can insert forum ids from both CMS so we don’t lose track where comments belong:

INSERT INTO x_forumids(nid, id, title)

SELECT   forum_index.nid,

         wp_posts.ID,

         wp_posts.post_title

FROM    forum_index,

        wp_posts

WHERE forum_index.title = wp_posts.post_title

The same goes with user IDs:

INSERT INTO x_userids(uid, id, email)

SELECT users.uid,

       wp_users.ID,

       wp_users.user_email

FROM users,

       wp_users

WHERE users.mail = wp_users.user_email

 

In the WordPress bbpress all forum replies are stored as posts with post type ‘reply’. So we need to construct the query to insert drupal replies from comments into WordPress wp_posts table. For this we the query is as follows.

INSERT INTO wp_posts(      post_author,

                           post_date,

                           post_date_gmt,

                           post_content,

                           post_title,

                           post_status,

                           comment_status,

                           ping_status,

                           post_parent,

                           post_type)

SELECT  

            x_userids.id as useridwp,

            FROM_UNIXTIME(comment.created) as datecreated,

            FROM_UNIXTIME(comment.created) as gmt,       

            field_data_comment_body.comment_body_value as commentbody,

            comment.subject,

            'publish',

            'closed',

            'closed',

            x_forumids.id as forumidwp,

            'reply'

FROM    comment,

        x_forumids,

        x_userids,

        field_data_comment_body

WHERE comment.uid = x_userids.uid

AND comment.nid = x_forumids.nid

AND comment.cid = field_data_comment_body.entity_id

 

Note that in Drupal time is saves in UNIX format, while in WordPress is a normal date. So before inserting this date-time should be converted using FROM_UNIXTIME() function.

This is it. I didn’t bother to transfer statistics from Drupal forum and other in my case unnecessary things. All I wanted is to bring forum topics and replies. Don’t forget to repair forums in WordPress to recalculate all necessary links inside (Tools – > Forums – > Repair Forums).

Once forums were in place the next step was to transfer posts and comments. This will be discussed in part 3.

One Comment:

  1. Let me know if you are interested in part 3, where I import Drupal posts and comments in to wordpress.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.