MoshuBlog.com

Thanks for visiting! If you're new here, you may want to subscribe to my RSS feed. My blog posts useful tips, ideas, tutorials and guides for WordPress bloggers - and articles about the life on the Canadian Prairies. If you are familiar with RSS readers, just add my feeds to your reader! You can also receive updates by email if you submit your email address in the box on the right.

I decided to move over here all my WP related posts from my “cross-cultural” blog, so the task was to extract (export) a few posts and their comments and to insert/import them into the database of this blog.
This post is a follow-up to my question posted in the WP forum.

As I was advised I used the phpMyAdmin offered by my host to perform this operation. Actually it turned out to be easier than expected. Here are the steps I have followed:

1. Being in the proper database click on the wp_posts table name in the left panel (your tables might have a different prefix, this is just an example!)
2. In the next panel click Browse on the top
3. Select the the checkbox at the beginning of the rows that you want to export (for the sake of simplicity I’ll describe it here as exporting 1 single post=row)
4. At the bottom of the rows where it says “With selected” - select “Export”
5. On the next panel deselect Structure; leave selected Data, and check Save as file, then GO
6. You will get a prefix_posts.sql file
7. Open the file in your favourite text editor (definitely not MS Word); I used Notepad++
8. Using Search/Replace change the wp_ part everywhere to target_ - depending on the prefix of the tables where you want to move the post
9. If you move the post to a new, empty database, you are ready to go. However if the target blog has already posts in its database, you have some more work to do:
   • — check in the database what is the ID# of your last post in the target blog
   • — Go back to the .sql file and take a look at the first line below Dumping data for table.... It should be something like:

   INSERT INTO `target_posts` VALUES (34, 1, '2005-03-31 03:13:10', ...

   • — That very first number after the VALUE is the post ID#. Change it to your last post ID#+1, i.e. if the last post in your target blog has ID# 53, make it 54. Save the file.
     
     Warning! You can chose any number, even 1,000 - but be aware that your next post made in the usual way from the admin Write Post panel will have 1001 as ID# in this case!
10. Now go the the DB of the target blog, and select the target_posts table
11. From the menu at the top select SQL
12. On the next page select Location of the textfile: Browse and find the changed and saved .sql file on your computer; click Open and, when the path is displayed in the field, click GO
13. If you are lucky, phpMyAdmin will report a successful action. The post is in your new/target blog. If you are concerned about the order where it appears, you can simply go to your blog admin panel and change the time stamp.
14. Possible problems. If you are importing from a DB that was created by earlier WP versions, you may encounter an "EROOR #1136 - Column count doesn't match value count at row 1". I got this error due to the two columns in the posts table: ‘post_lat’ and ‘post_lon’ that were there for the geo-location in WP 1.2 and earlier versions (see my post in the forum)
15. What I did: Checked the .sql file and found the two 'NULL', 'NULL' values after the text portion of the post (it was NULL since I’ve never used that feature) and simply deleted them. Now the number of columns was the same, and the import successful.
16. Another issue and warning. Be aware that some plugins create their own table in the WP database and this can cause the same Error as above. Check and compare the two databases and if there is any column created by a plugin, you have two choices:
    • — install the very same plugin into the DB that is missing it
    • — delete the values referring to that column from the the source .sql file
17. When you are done just do the import (SQL) query

With comments the procedure is very similar. For the post moved above I checked “on the surface” and saw there were 7 comments. Knowing that the post ID# used to be 34 I checked the wp_comments table in Browse mode to find the comments attached to that post. Checked all the 7 rows and exported them.
Next, opened the wp_comments.sql file in the text editor and

1. changed the table prefix (see above)
2. changed the post ID in the comments

For this latter you have to look at the .sql file and you can see for every comment exported there is a line starting like:

INSERT INTO `target_comments` VALUES (69, 37, 'Moshu',...etc.
INSERT INTO `target_comments` VALUES (71, 37, 'John Doe',...etc.

Search/Replace “37″ with the post’s new ID# - which in my case was 54, see above. The first number is the comment’s own ID#.
If done, import it via the SQL query as we did with the post.
Warning: There might be error messages if the comment ID# is already in use. Just go back to editing, change the number to something that probably doesn’t exist, and try again the query.

I am sure there might be some other way to do this, especially if you are “fluent” in MySQL commands, but for those less skilled it can be helpful.