How to Preserve Threads When Importing LiveJournal Comments to WordPress

[rusty_halo]

http://rusty-halo.com/wordpress/?p=2535

This is a follow up to my big long guide to migrating your blog from LiveJournal to WordPress. One of my biggest frustrations is that the importing process loses comment threading, which makes it a huge pain to read through old comments, especially if you’ve got lots of threaded conversations. So I decided to figure out a way.

This tutorial requires that you directly edit your WordPress database and PHP files. If you’re a complete beginner, you might not want to bother with this. If you’re familiar with MySQL and PHP, it shouldn’t be too difficult, and I’m happy to answer questions if you run into trouble.

To use this tutorial, you need:

* A copy of your journal with comments in XML format. See my migration guide for details; the program you'll probably use is LJArchive.
* An installation of WordPress on your own server, with a threaded comments plugin (I used WP Thread Comment and I think this will probably also work with Brian's Threaded Comments).
* Access to phpMyAdmin, which is an interface for managing your databases. Most webhosts will let you access phpMyAdmin via cPanel (yourdomain.com/cpanel).

Before you do anything, back up your database. Go into phpMyAdmin, select the database where WordPress is installed, and click "Export." The default selections are fine (SQL format, full data and structure), just check "Save as file" and put it somewhere safe on your computer. This way if you screw up, you can just re-import the whole database and start over.

Now, to begin the comment migration process:

1. If you've already imported your journal into WordPress, you'll need to delete the unthreaded comments that you imported the first time. If you haven't imported anything yet, skip to the next step.

Log in to phpMyAdmin. Your databases and tables are listed in the left panel. Click the database where you installed WordPress, then click on the comments table; it's probably called "wp_comments" (by default, WordPress uses "wp_" for table prefixes; if you've used something else, make sure to change my instructions to match your prefix). Now you have two choices:

a) If you're working on an installation of WordPress that's actually in use, and you have comments that exist only on WordPress, you want to delete only the old imported comments, not the new WordPress-only comments. Hit "browse" then click "comment_date" to sort comments by date. Find where the old comments start and delete them all, making sure not to delete the newer WordPress-only comments.

b) If you have no WordPress-only comments, you can empty the entire table. Click "Empty" in the top right corner.

2. Now you want to add two new columns to the "wp_comments" table. Click the "SQL" tab and paste in this code:

ALTER TABLE `wp_comments` ADD `comment_lj_id` INT( 11 ) NOT NULL ,
ADD `comment_lj_parent` INT( 11 ) NOT NULL ;

This creates columns for the comment's ID on LiveJournal and the ID of its parent comment on LiveJournal.

3. Now we're going to run the comment importing process again, but this time we're going to capture the comment's LiveJournal ID and parent LiveJournal ID. To do this, we have to modify the import script that comes with WordPress to tell it to pick up these fields.

a) Open the import file in a text editor (it should be wp-admin/import/livejournal.php).

b) Find where it starts importing the content from the XML file (under line 94, // Clean up content). You should see this:

preg_match('|<email>(.*?)</email>|is', $comment, $comment_author_email);
$comment_author_email = $wpdb->escape(trim($comment_author_email[1]));

Under that, add the following:

preg_match('|<itemid>(.*?)</itemid>|is', $comment, $comment_lj_id);
$comment_lj_id = $wpdb->escape(trim($comment_lj_id[1]));
preg_match('|<parent_itemid>(.*?)</parent_itemid>|is', $comment, $comment_lj_parent);
$comment_lj_parent = $wpdb->escape(trim($comment_lj_parent[1]));

That's telling it to grab the itemid (its LiveJournal ID number) and parent_itemid (the LiveJournal ID of its parent comment).

Shortly below that, you should see this:

$commentdata = compact('comment_post_ID', 'comment_author', 'comment_author_email', 'comment_date', 'comment_content', 'comment_approved';

You just want to modify it to also include the comment_lj_id and comment_lj_parent, like so:

$commentdata = compact('comment_post_ID', 'comment_author', 'comment_author_email', 'comment_date', 'comment_content', 'comment_approved', 'comment_lj_id', 'comment_lj_parent');

c) Okay, you've modified the LiveJournal import script! Yay! But you also have to modify the function that imports comments, to tell it to insert the data into those fields. So open up the file that defines that function; it's wp-includes/comment.php. The function is "wp_insert_comment" and should be around line 337. Find this:

$result = $wpdb->query("INSERT INTO $wpdb->comments
(comment_post_ID, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_date, comment_date_gmt, comment_content, comment_approved, comment_agent, comment_type, comment_parent, user_id)
VALUES
('$comment_post_ID', '$comment_author', '$comment_author_email', '$comment_author_url', '$comment_author_IP', '$comment_date', '$comment_date_gmt', '$comment_content', '$comment_approved', '$comment_agent', '$comment_type', '$comment_parent', '$user_id')
");

and change it to this (the only things modified are "comment_lj_id" and "comment_lj_parent" added to the columns and data lists):

$result = $wpdb->query("INSERT INTO $wpdb->comments
(comment_post_ID, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_date, comment_date_gmt, comment_content, comment_approved, comment_agent, comment_type, comment_parent, user_id, comment_lj_id, comment_lj_parent)
VALUES
('$comment_post_ID', '$comment_author', '$comment_author_email', '$comment_author_url', '$comment_author_IP', '$comment_date', '$comment_date_gmt', '$comment_content', '$comment_approved', '$comment_agent', '$comment_type', '$comment_parent', '$user_id', '$comment_lj_id', '$comment_lj_parent')
");

Keep this file open; once you're done with the importing, change it back to the way it was, so it doesn't try to find a LJ ID and LJ parent for every comment.

You're done editing files. Thank god.

4. Okay, now it's time to import the comments. Go to your WordPress admin section and hit "Manage" -> "Import" -> "LiveJournal." Now import your journal, using that XML file you already have.

If you'd already imported your journal, it should skip over entries and just import comments. If you're importing from scratch, it will import everything. (Note: if you already imported your journal and then changed the titles of some of your entries, it will re-import the entries with different titles. This is because, during the importing process, it checks to see whether an entry already exists on that date with that title. To prevent it from re-importing re-titled entries, you have to temporarily modify the post_exists function to check by date only and not title. Change that in wp-admin/import/livejournal.php on line 68 and wp-admin/includes/post.php on line 172; just take out the parts referring to title and content, then put them back when you're done. Let me know if you need more details on that.)

Once that has run, you should have all your comments back--this time with values for comment_lj_id and comment_lj_parent. (Look in phpMyAdmin to make sure it worked; go to "wp_comments" and hit "Browse"; all imported comments should have a number under comment_lj_id, and sub-comments should have values under comment_lj_parent too).

5. Now go back to phpMyAdmin, go to the WordPress database, click the "SQL" tab, and run the following query:

SELECT pc.comment_ID, tc.comment_ID FROM wp_comments pc INNER JOIN wp_comments tc ON pc.comment_lj_id = tc.comment_lj_parent WHERE tc.comment_lj_parent > 0 GROUP BY tc.comment_ID;

This'll give you a page of results. Scroll down and click "Export" then select "CSV" and change "Fields enclosed by" to a single quote ('). Check "Save as file" and save the corresponding file to your computer.

The reason we're doing this is that LiveJournal IDs don't match up with WordPress IDs, so relating a comment to its LiveJournal parent won't work--you have to relate a comment to its WordPress parent. This query gives us a list of WordPress IDs and their corresponding WordPress parent IDs based on the LiveJournal IDs and LiveJournal parent IDs.

6. Open that file in a text editor that allows you to find and replace line breaks. It should look something like this, only a lot longer:

'20333';'20334'
'20254';'20666'

You want to edit it into query format, so that you get the following query:

UPDATE wp_comments SET comment_parent = 'PARENT_COMMENT_ID' WHERE comment_ID = 'THIS_COMMENT_ID';

where PARENT_COMMENT_ID is the first value and THIS_COMMENT_ID is the second value; for example (only a lot longer):

UPDATE wp_comments SET comment_parent = '20333' WHERE comment_ID = '20334';
UPDATE wp_comments SET comment_parent = '20254' WHERE comment_ID = '20666';

To do that, find

;

and replace it with

(space) WHERE comment_ID = (space)

Then find

(linebreak)

and replace it with

;
UPDATE wp_comments SET comment_parent = (space)

* (linebreak) should be you pressing "enter" on your keyboard
* (space) should be you pressing the spacebar on your keyboard
* once you've done this, you'll have to fix the first and last entries by hand

Once you've got it all, paste it into "SQL" and go. (If it's hugely long, you'll have to break it up into small enough portions to paste into that window without overflowing the character limit. I did about 2,000 lines at a time.)

(I'm fairly certain that steps 5 and 6 could be combined into one MySQL query, but I couldn't figure out what it would be. If you know someone brilliant at MySQL, you might want to ask them and save yourself the trouble of all that exporting.)

Anyway. This method worked for me, and I hope it helps someone else. :)

Originally published at rusty-halo.com. Please click here to comment.

Current Mood: geeky