Importing from another MySQL into WordPress

WordPress Database MigrationSometimes data imports into WordPress can be a pain, but most of the time, it’s pretty darn easy. In a previous project I had to go through the trouble of outputting WXR files and then importing those, but it’s really a lot easier if you do a direct MySQL to WordPress import using the WordPress API. I did one this morning for Bergler, a client of ours, and wanted to quickly walk you through how I did it, as it’s something I guess some of you can benefit from.

The old database in this case was very very simple, I’ve renamed the column names as they were originally in Dutch, but that’s about all I changed.

So, first of all, we connect to the old database:

require("include/connect.inc");
$conn =	mysql_connect($DBHost,$DBUser,$DBPassword);
mysql_select_db($DBName,$conn);

The include/connect.inc file contained the variables used to connect to the database in this case.

Then we query for the news:

$results = mysql_query("SELECT * FROM news",$conn);

And then we loop through it, this is where most of the magic happens. To have the posts published immediately on import, we have to set post_status to ‘publish’. I also determined the category I wanted these posts to go into, number 4 in this case. In a lot of other cases you might have to map the original category to your new one, but that should be easy too. Finally I converted the date from a dd/mm/yyyy string to a date that WordPress understands, and then just put the appropriate fields in the old database into their new names:

$i = 0;
while ($row = mysql_fetch_array($results,MYSQL_ASSOC)) {
  $post = array();
  $post['post_status'] = 'publish';
  $post['post_category'] = array(4);
  $post['post_date'] = date('Y-m-d H:i:s',strtotime($row['newsitem_date']));
  $post['post_title'] = $row['newsitem_title'];
  $post['post_content'] = $row['newsitem_content'];
  $posts[$i] = $post;
  $i++;
}

As you can see I’ve now created a $posts array, I could have immediately inserted the posts into WordPress here but I wanted to prevent any clashes between the two databases. So the next step is to free the results and close the connection:

mysql_free_result($results);
mysql_close($conn);

Now we load up WordPress (this line is assuming you’ll be running this import script from the WordPress root directory):

require('./wp-load.php');

And finally we loop through the posts and insert them into the database:

foreach ($posts as $post) {
  wp_insert_post($post);
}

That’s it! Easy does it. If there’s a LOT of news you might have to play with script execution times etc, but this code just imported 200+ news items in under 5 seconds for me.

Yoast.com runs on the Genesis Framework

Genesis theme frameworkThe Genesis Framework empowers you to quickly and easily build incredible websites with WordPress. Whether you're a novice or advanced developer, Genesis provides you with the secure and search-engine-optimized foundation that takes WordPress to places you never thought it could go.

Read our Genesis review or get Genesis now!

13 Responses

  1. Gerron MulderBy Gerron Mulder on 2 December, 2009

    Wouldn’t it be easier to do the wp_insert_post() within the while loop? This way you don’t have to put your entire table into one array. It will save some memory and you won’t get stuck if your table is to big.

    • Joost de ValkBy Joost de Valk on 2 December, 2009

      As I said in the post: in this case that gave some weirdness, but usually that is the better thing to do indeed.

  2. RaoniBy Raoni on 2 December, 2009

    Hi, that’s very nice.

    How could I also import some images to each post???
    I have some News in one table and some photos from each news in another table……..it is possible to import?????
    Thanks very much!!!

    • Joost de ValkBy Joost de Valk on 7 December, 2009

      It is possible to import the images too, but that’d require a bit more coding, perhaps I’ll do an example of that some day too :)

  3. Andrew @ WeBuildYourBlog.comBy Andrew @ WeBuildYourBlog.com on 3 December, 2009

    I will definitely bookmark this in case I need to do this in the future. Thanks for sharing.

  4. LuciBy Luci on 3 December, 2009

    Great! I’ve been looking out for something like this for awhile, now I know where to come when it needs to be done. Think I’ll still need to re-read it a couple of times to make sure I understood it completely though. Theoretically, after the news has been imported to a new database, could the old one be deleted?

  5. SamanthaBy Samantha on 3 December, 2009

    Hi guys
    I’m also interested with this question. Hope you answer it.
    “How could I also import some images to each post???”

    Kind Regards
    Sam
    X

  6. GraemeBy Graeme on 4 December, 2009

    Thanks for this information. I have a big project converting a database of over 2000 articles to WordPress. I haven’t had the chance to look to much into the WordPress database structure, but from your article it looks relatively easy.

    Cheers,
    Graeme

  7. DanielBy Daniel on 4 December, 2009

    Mate, where were you a couple weeks ago? I’ve gone through this process all alone and almost died :D
    Ha.
    Btw, noticed you’ve got a Raven SEO banner on the side. Aren’t they the best? Such a powerful SEO tool.

  8. TonyBy Tony on 7 December, 2009

    I have this bookmarked as I will be going through this process in a few months. This will come in really handy.

  9. Immobilier lyonBy Immobilier lyon on 7 December, 2009

    Hi Joost de Valk

    Thx for this post.
    I will surely use your tip in order to migrate my database from the CMS called spip to the wordpress CMS !

    Thanks again for this great post !

  10. Leonardo StorchBy Leonardo Storch on 14 December, 2009

    Awesome post!
    I’m also interested with “How could I also import some images to each post???”!

    Bookmarked! =)