Gotcha of the Day: Porting a WordPress Blog to Magento's Blogging Plugin

Magento has a pretty sweet Blogging plugin that one of my clients wanted to use. No problem, right? Except for one itty bitty catch, they've been blogging in WordPress and sure would love to port over their content.

Unfortunately, the Magento Blogging plugin doesn't offer any sort of importing capability, so I was going to need to get creative.

After a bit of poking around, I finally settled on a strategy: I would load both the WordPress and Magento databases into the same mysql server instance, and run SQL queries that would SELECT from WordPress tables and INSERT into Magento tables.

The strategy turned out to work surprisingly well. The queries (described below) weren't that tricky to write, and while it took a little practice to get all the data inserted properly, once I got the recipe right, the import went smoothly.

One word of caution: Magento will refuse to operate if you manage to insert duplicate IDs in the blogging or category tables. I solved this by making sure that before importing I cleared out whatever was in the Magento blogging database. This worked well because my client didn't have any articles written yet (only test stuff they wouldn't mind losing). In other words, mind the DELETE statements below - understand what they are doing before you run them :-).

In general, loading up two databases and SELECT/INSERT'ing between them is an approach I've found can work exceedingly well. After I implemented this solution, I remembered that months back I did something similar with WordPress and ExpressionEngine. Sure, I could have exported WordPress to RSS or CSV and written custom code to import it, but why bother with all that parsing when the database already has things structured so well?

Here's the import script. Note I've got two databases, one named wordpress and one named magento. For no particular reason, I'm changing magento at the top of the script and explicitly pulling from wordpress.

USE magento;

-- Target: aw_blog
DELETE FROM aw_blog; -- Careful!  Start off by deleting all our data

-- Magic happens here: fill up Magento's (aw_blog) post table from WordPress
INSERT INTO aw_blog(post_id, title, post_content, status, created_time, update_time, identifier,
                    user, update_user, meta_keywords, meta_description, comments, tags, short_content)
 SELECT
    p.ID,
    p.post_title,
    p.post_content,
    1,
    p.post_date,
    p.post_date,
    p.post_name,
    a.user_nicename,
    a.user_nicename,
    '',
    '',
    0,
    '',
    p.post_excerpt
  FROM wordpress.wp_posts p JOIN wordpress.wp_users a ON p.post_author = a.ID
  WHERE
    p.post_type = 'post'        AND
    p.post_status = 'publish';

-- The hard work is done. Now we need to fill up a few more tables with corresponding
-- IDs. Without these tables, Magento gets confused and won't be able to find posts.

-- Target: aw_blog_post_cat
DELETE FROM aw_blog_post_cat;

INSERT INTO aw_blog_post_cat(cat_id, post_id)
  SELECT DISTINCT 1, post_id FROM aw_blog;

-- Target: aw_blog_store
DELETE FROM aw_blog_store;

INSERT INTO aw_blog_store(post_id, store_id)
  SELECT DISTINCT post_id, 0 FROM aw_blog;

-- All Done!