Migrating a Wordpress site including Users to Drupal 6
This post is not a tutorial howto migrate your Wordpress site to Drupal 6 using the wordpress_import module. While that module is a great 'fire and forget' approach, it does have its limitations. Instead, we look at a more generic approach using table wizard and migrate tools.
Registered users on your Wordpress site are not included in the WXR file during export. This makes it impossible to migrate users using the Drupal wordpress import module who might have registered on your Wordpress site and may have associated comments. See also this issue.
In my case, this was a real problem. The Wordpress site has 3000+ registered users, who commented 15.000+ comments on almost a thousand posts. Many comments may be anonymous but still you don't want to lose your existing community, seo, and valuable information like email-addresses, newlsetter subscriptions etc. - because of a migration.
Use the excellent data import modules table wizard and migrate. See a great tutorial on lullabot how to use these modules. Using these modules, you can literally import anything which is already available as a mysql table in your database. The table wizard module will expose any tables in your database and expose it as a View (which is awesome functionality by itself!).
Next, the migrate module offers batch processing to map your data into Drupal. Various modules are supported, like CCK and profile (core), and many more. Now you can easily migrate all WP posts, comments, users and categories into their Drupal counterpart.
However, all these things are related to each other. And while you can provide relationship (joins) between certain tables using table wizard, you won't be able to use the same post-id you had in Wordpress for your nid in Drupal.
My workaround for this was to write a php script which recovers the relationships between various tables. With a bit (or a lot ;) work, you could create your own Drupal module which hooks into the migrate_api and does this for you.
In my case, I needed a quick solution (as I didn't had time to figure out how to use the API) so I went for custom MySQL queries, but I do want to share which tables needed to be updated, which should make your life a little easier.
Posts were migrated from Wordpress into the default content type 'Story'.
The original Wordpress table is called wp_posts. (wp being the prefix) In Drupal I created a CCK field in content-type 'Story' to hold the old wp_id. This way, I could pull data from the table 'content_type_story' which held the old wp_post_id and new nid (node id).
As with nodes, we want to store the old wp_user_id in a profile field so we can easily use it to map the comments to the right owner. For this I used the core profile module (it's disabled by default) and mapped the old wp_user_id to a custom profile field I created.
[caption id="attachment_1193" align="aligncenter" width="574" caption="Screenshot of migrate module: mapping wordpress users to drupal users"][/caption]
Comments where also first migrated from the 'wp_comments' table to your Drupal comments. Make sure you map the wp_user_id and wp_post_id into Drupal, because you will need them to map the comments to your nodes and users.
After import, you might notice they won't show up under 'comments' in your administration screen. That's because they aren't linked to any nodes yet. You need to create a script which checks each node in 'content_type_story' and searches for a comment with the same old wp_post_id. If it finds one, it is replaced with the new nid. All comments should get a new nid.
Actually, you can import the comments and give them the right parent node id straight away. You just need to set up the right table wizard relationships (MySQL joins) so you can access the right fields from content_type_story.
Secondly, we want to link all comments to the right owners (if available, they could be anonymous in my case). This is easily done by pulling all data from the Drupal 'profile_values' table (created by profile in the step under 'Users' above) which will provide you with the old and new uid. Now you need to loop through all comments and replace the old wp_user_id with the new uid.
Again, using the migrate module we import the Wordpress categories to taxonomy terms. This one is a bit trickier though, because there was no field readily available in Drupal taxonomy to hold the old wp_category_id. In my case, I only had a handful of categories so I made a quick array to hold the mappings between the wp_category_id and Drupal tid (term id).
Then, foreach content_type_story row, you can loop through the posts and using the wp_post_id in that table, retrieve the old wp_category_id by pulling it from 'wp_post2cat'. Next, you translate it to the new term (I used my mapping array) and finally insert everyting in the drupal table 'term_node' which holds the term <-> node relationships for Drupal.
Note: 'term_node' table has 3 columns: 'nid', 'vid' and 'tid'. The first and last speak for itself but 'vid' is not the vocabulary id as you might think. Actually it's the version-id of the term-node (which is used for revision control). To avoid conflicts, I used the same value for vid as for nid.