Sunday, August 29, 2010

Importing from b2evolution 1.9.3 to wordpress

There's several scripts out there to import from b2evo to wordpress.  I decided to go to wordpress since it is the major standard out there. 

B2evo 1.9.3 should have been updated.  If it's >=2.0 b2evo, then the usual scripts out there work well (see wordpress site).  The scripts from this site were designed for older <2.0 b2evo blogs: http://blog.realnitro.be/2007/02/21/convert-a-b2evolution-blog-to-wordpress/ seemed to "almost" work.  Except that I kept getting an error about categories.

So I created this modified script below that works.  Categories get lost, but to me that was a small price to pay (vs. using RSS to import manually which took forever).   One glitch, I had to add a field in wp_posts called post_category using phpmyadmin or something similar otherwise the script will crash (at least for me b2evo 1.9.3 -> wordpress).  If someone could perfect this further, that'd be great, but it's hard to spend too much time on a system you are about to abandon.

<?php
// Tom Everett - March 3, 2005
// WP "post_date" is now populated from B2 "post_issue_date"
// Post statuses are now migrated across
// Image URLs are now updated in posts, and made relative urls rather than absolute urls

// Jens "RealNitro" Geiregat - February 21, 2007
// Updated this script to convert a b2evolution 1.8.6 blog to WordPress 2.1
// It does not (yet?) copy post and category names (the ones used in urls),
// so you'll either have to update this script, add them manually, or
// just don't use them. (I added them manually using phpmyadmin.)
// For more info, check my blog at
// http://blog.realnitro.be/2007/02/21/convert-a-b2evolution-blog-to-wordpress/
// Source: http://ppleyard.org.uk/index.php?p=72
// Warning: use this script with care! If you enter the wrong data in
// the fields, you could overwrite an other WordPress blog!

$step = $_GET['step'];
if (!$step)
 $step = 0;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>WordPress &rsaquo; Import b2evolution Data</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<style media="screen" type="text/css">
    <!--
 body {
  font-family: Georgia, "Times New Roman", Times, serif;
  margin-left: 15%;
  margin-right: 15%;
 }
 #logo {
  margin: 0;
  padding: 0;
  background-image: url(http://wordpress.org/images/logo.png);
  background-repeat: no-repeat;
  height: 60px;
  border-bottom: 4px solid #333;
 }
 #logo a {
  display: block;
  height: 60px;
 }
 #logo a span {
  display: none;
 }
 p, li {
  line-height: 140%;
 }
    -->
 </style>
</head>
<body>
<h1 id="logo"><a href="http://wordpress.org"><span>WordPress</span></a></h1>
<?php

switch($step) {
 case 0:
?>
<p>Welcome to the WordPress b2evolution import utility. Before getting started, we need some information on your b2evolution database.</p>
<form method="post" action="b2evo2wp.php?step=1">
  <p>Below you should enter your b2evolution database connection details. If you're not sure about these, contact your host. </p>
  <table>
    <tr>
      <th scope="row">Database Name</th>
      <td><input name="b2db" type="text" size="45" value="b2evolution" /></td>
      <td>The name of the database you have b2evolution installed in. </td>
    </tr>
    <tr>
      <th scope="row">User Name</th>
      <td><input name="b2usr" type="text" size="45" value="username" /></td>
      <td>Your MySQL username</td>
    </tr>
    <tr>
      <th scope="row">Password</th>
      <td><input name="b2pwd" type="password" size="45" value="password" /></td>
      <td>...and MySQL password.</td>
    </tr>
    <tr>
      <th scope="row">Database Host</th>
      <td><input name="b2host" type="text" size="45" value="localhost" /></td>
      <td>99% chance you won't need to change this value.</td>
    </tr>
    <tr>
      <th scope="row">B2 URL</th>
      <td><input name="b2url" type="text" size="45" value="www.sparky.com" /></td>
      <td>The URL Path to b2, such as "www.myblog.org".</td>
    </tr>
  </table>
  <input name="submit" type="submit" value="Submit" />
</form>
<?php
 break;
 
 case 1:
  $b2_db = $_POST['b2db'];
  $b2_usr = $_POST['b2usr'];
  $b2_pwd = $_POST['b2pwd'];
  $b2_host = $_POST['b2host'];
  $b2_url = $_POST['b2url'];

        // connect to the b2evo database
        $resB2=mysql_connect($b2_host,$b2_usr,$b2_pwd);
        if (!$resB2) {
            exit("Connection failed! host: $b2_host, user: $b2_usr, pass: $b2_pwd");
        }
        if (!mysql_select_db($b2_db,$resB2)) {
            exit("Couldn't select database: $b2_db");
        }
        // get all blogs
        $result=mysql_query('SELECT blog_ID,blog_name FROM evo_blogs',$resB2);
        if (!$result) {
            exit("No blogs returned from the b2evolution database!");
        }
?>
<p>Now select the b2evolution blog to import and provide information about your WordPress database.</p>
<form method="post" action="b2evo2wp.php?step=2">
  <table>
    <tr>
      <th scope="row">b2evolution Blog</th>
      <td>
        <select name="b2blog">
<?php
        while ($row = mysql_fetch_row($result)) {
?>
          <option value="<? echo $row[0] ?>" selected><? echo $row[1]?></option>
<?php
        }
?>
        </select>
      </td>
      <td>The name of the b2evo blog you want to import. </td>
    </tr>
    <tr>
      <th scope="row">Database Name</th>
      <td><input name="wpdb" type="text" size="45" value="wordpress" /></td>
      <td>The name of the database you have WordPress installed in. </td>
    </tr>
    <tr>
      <th scope="row">User Name</th>
      <td><input name="wpusr" type="text" size="45" value="username" /></td>
      <td>Your MySQL username</td>
    </tr>
    <tr>
      <th scope="row">Password</th>
      <td><input name="wppwd" type="password" size="45" value="password" /></td>
      <td>...and MySQL password.</td>
    </tr>
    <tr>
      <th scope="row">Database Host</th>
      <td><input name="wphost" type="text" size="45" value="localhost" /></td>
      <td>99% chance you won't need to change this value.</td>
    </tr>
    <tr>
      <th scope="row">Table Prefix</th>
      <td><input name="wppref" type="text" id="wppref" value="wp_" size="45" /></td>
      <td>If you run multiple WordPress installations in a single database, specify the correct prefix for the installation you want.</td>
    </tr>
  </table>
  <input name="b2db" type="hidden" id="b2db" value="<? echo $b2_db; ?>">
  <input name="b2usr" type="hidden" id="b2usr" value="<? echo $b2_usr; ?>">
  <input name="b2pwd" type="hidden" id="b2pwd" value="<? echo $b2_pwd; ?>">
  <input name="b2host" type="hidden" id="b2host" value="<? echo $b2_host; ?>">
  <input name="b2url" type="hidden" id="b2url" value="<? echo $b2_url; ?>">
  <input name="submit" type="submit" value="Submit" />
</form>
<?php
        mysql_close($resB2);
 break;
 
 case 2:
  $b2_db = $_POST['b2db'];
  $b2_usr = $_POST['b2usr'];
  $b2_pwd = $_POST['b2pwd'];
  $b2_host = $_POST['b2host'];
         $b2_blog = $_POST['b2blog'];
  $wp_db = $_POST['wpdb'];
  $wp_usr = $_POST['wpusr'];
  $wp_pwd = $_POST['wppwd'];
  $wp_host = $_POST['wphost'];
  $wp_pref = $_POST['wppref'];
  $b2_url = $_POST['b2url'];
       
 // connect to the b2evo database
        $resB2 = mysql_connect($b2_host,$b2_usr,$b2_pwd);
        if (!$resB2) {
            exit("Connection failed! host: $b2_host, user: $b2_usr, pass: $b2_pwd");
        }
        if (!mysql_select_db($b2_db,$resB2)) {
            exit("Couldn't select database: $b2_db");
        }           
        // connect to the WP database
        $resWP = mysql_connect($wp_host,$wp_usr,$wp_pwd,TRUE);
        if (!$resWP) {
            exit("Connection failed! host: $wp_host, user: $wp_usr, pass: $wp_pwd");
        }
        if (!mysql_select_db($wp_db,$resWP)) {
            exit("Couldn't select database: $wp_db");
        }
?>
<p>All right sparky, this is where the actual import takes place! Do you feel lucky today? :p</p>
<?php
        // setup arrays to store ID changes
        $arUser = array();
        $arCat = array();
  $arUser[1] = 1;

        // get the paths for image files
        $sql = "select option_value from wp_options where option_name='fileupload_url'";
        $result = mysql_query($sql,$resWP) or die("Invalid query: " . mysql_error() . "<BR /> SQL : " . $sql);
        if ($result) {
            $row = mysql_fetch_array($result, MYSQL_ASSOC);           
            $filepath_wp = $row['option_value']."/";           
        }

        $filepath_b2 = "http://".$b2_url."/media/";

        echo "b2Evolution file path: ".$filepath_b2."<br>";
        echo "WordPress file path: ".$filepath_wp."<br><br>";

        // get authors for blog
        echo "Importing User records ... <BR />";
    $sql = "SELECT DISTINCT evo_users.* FROM evo_users INNER JOIN evo_blogusers " .
   "ON evo_users.user_ID = evo_blogusers.bloguser_user_ID WHERE " .
   "evo_blogusers.bloguser_blog_ID = ".$b2_blog." AND `user_login`<>'admin'";
        $result = mysql_query($sql,$resB2) or die("Invalid query: " . mysql_error() . "<BR /> SQL : " . $sql);
        if ($result) {
            $cnt = 0;
            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                $sql = trim(str_replace("\n","","INSERT INTO `". $wp_pref ."users` ".
                    "(`user_login`,`user_pass`,`user_nicename`," .
                    "`user_email`,`user_url`,`user_registered`,`user_status`," .
     "`display_name`)" .
                    " VALUES ('".$row['user_login']."','".$row['user_pass']."','".$row['user_login']."','".$row['user_email'].      "','".$row['user_url']."','".$row['dateYMDhour']."','".$row['user_status']."','".
  $row['user_login']."');"));
                $q = mysql_query($sql, $resWP) or die("Invalid query: " . mysql_error() . "<BR /> SQL : " . $sql);
                $id = mysql_insert_id($resWP);
                $arUser[$row['ID']] = $id;
                $cnt = $cnt + 1;
            }
            echo $cnt . " User record(s) imported! <BR />";
        } else {
            echo "No User records found!<BR />";
        }
        // get entries for blog
        echo "Importing Entry records ... <BR />";
        $sql = "SELECT DISTINCT evo_posts.* " .
 "FROM evo_posts " .
 "INNER JOIN evo_categories ON evo_posts.post_main_cat_ID = evo_categories.cat_ID " .
 "WHERE evo_categories.cat_blog_ID = " . $b2_blog;
        $result = mysql_query($sql,$resB2) or die("Invalid query: " . mysql_error() . "<BR /> SQL : " . $sql);
        if ($result) {
            $cnt = 0;
            $cntCom = 0;
            $cntCat = 0;
            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                // author ID must be switched to new author ID
                $aid = $arUser[$row['post_author']];

                // category ID must be switched to new category ID
                $cid = $arCat[$row['post_category']];
    if (!$cid) {
     $cid = '1';
    }

                // status mapping
                $stat = $row['post_status'];
                if ($stat == 'published') {
                    $stat = 'publish';
                } else if ($stat == 'deprecated') {
                    $stat = 'publish';
                } else if ($stat == 'protected') {
                    $stat = 'private';
                } else if ($stat == 'private') {
                    $stat = 'private';
                } else if ($stat == 'draft') {
                    $stat = 'draft';
                }
  
  // WordPress needs two timestamps: local and GMT (I wonder why...)
  $local_date = $row['post_datecreated'];
  $gmt_date_timestamp = strtotime($local_date);
  $gmt_date = gmdate("Y-m-d G:i:s ", $gmt_date_timestamp);
  
  $local_modified = $row['post_datemodified'];
  $gmt_modified_timestamp = strtotime($local_modified);
  $gmt_modified = gmdate("Y-m-d G:i:s ", $gmt_modified_timestamp);

  // WordPress saves the comment count in each post record.
  $sql = "SELECT COUNT(comment_ID) AS comment_count FROM `evo_comments` WHERE `comment_post_ID` = " . $row['post_ID'];
         $comment_res = mysql_query($sql,$resB2) or die("Invalid query: " . mysql_error() . "<BR /> SQL : " . $sql);
             $comment_row = mysql_fetch_array($comment_res, MYSQL_ASSOC);
  $comment_count = $comment_row['comment_count'];
 
                // update urls in the post content
                $post_content = $row['post_content'];
                $post_content = str_replace($filepath_b2, $filepath_wp, $post_content);

                $sql = trim(str_replace("\n","","INSERT INTO `". $wp_pref ."posts` ".
                    "(`post_author`,`post_content`,`post_title`,`post_category`,`post_status`,`post_date`,`post_date_gmt`,`post_modified`,`post_modified_gmt`,`comment_count`)" .
                    " VALUES ('".$aid."','".
                 mysql_escape_string($post_content)."','" .
                               mysql_escape_string($row['post_title'])."','".
          $cid."','".
          $stat."','".
          $local_date."','".
          $gmt_date."','".
          $local_modified."','".
          $gmt_modified."','".
          $comment_count."');"));
    echo "$sql <br />";
                $q = mysql_query($sql, $resWP) or die("Invalid query: " . mysql_error() . "<BR /> SQL : " . $sql);
                $id = mysql_insert_id($resWP);
                $eid = $row['post_ID'];
                $cnt = $cnt + 1;

                // get comments for entry
                $sql = "SELECT evo_comments. * , evo_users.user_nickname, evo_users.user_url, evo_users.user_email " .
  "FROM evo_comments " .
  "LEFT JOIN evo_users ON evo_comments.comment_author_ID = evo_users.user_ID " .
  "WHERE evo_comments.comment_post_ID=" . $eid;
                $res = mysql_query($sql, $resB2) or die("Invalid query: " . mysql_error() . "<BR /> SQL : " . $sql);
                if ($res) {
                    while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {

   // if the commentor was a user of the blog, nickname, email and url can be fetched
   // from user_nickname, user_email and user_site
   $checked_author = $row['comment_author'];
   $checked_url = $row['comment_author_url'];
   $checked_email = $row['comment_author_email'];
   if ($row['comment_author_ID'] != NULL) { // The author was a logged in user
       $checked_author = $row['user_nickname'];
       $checked_url = $row['user_url'];
       $checked_email = $row['user_email'];
   }

   // Wordpress needs two timestamps: local and GMT (I wonder why...)
   $local_time = $row['comment_date'];
   $gm_timestamp = strtotime($local_time);
   $gm_time = gmdate("Y-m-d G:i:s ", $gm_timestamp);

   // We need to translate the b2evo comment types to WordPress comment types
   // WordPress does not know linkback, so this script will map linkbacks to pingbacks
   $comment_type = $row['comment_type'];
   if ($comment_type == 'comment') {
       $comment_type = '';
   } else if ($comment_type == 'trackback') {
       // No need to change it
   } else if ($comment_type == 'linkback') {
       $comment_type = 'pingback';
   } else if ($comment_type == 'pingback') {
       // No need to change it
   } else { // This shouldn't happen.
       echo "Unknown comment type '" . $comment_type . "'! Defaulting to normal comment";
       $comment_type = '';
   }

                        $sql = trim(str_replace("\n","","INSERT INTO `". $wp_pref ."comments` ".
                            "(`comment_post_ID`,`comment_author`,`comment_author_email`," .
                            "`comment_author_url`,`comment_author_IP`,`comment_date`," .
                            "`comment_date_gmt`,`comment_content`,`comment_karma`, `comment_type`)" .
                            " VALUES ('".$id."','" . mysql_escape_string($checked_author) . "','" . mysql_escape_string($checked_email) .
                            "','" . mysql_escape_string($checked_url) . "','" . $row['comment_author_IP'] . "','" . $local_time .
                            "','" . $gm_time . "','" . mysql_escape_string($row['comment_content'])."','". $row['comment_karma'] ."','" . $comment_type ."');"));
                        $q = mysql_query($sql, $resWP) or die("Invalid query: " . mysql_error() . "<BR /> SQL : " . $sql);
                        $cntCom = $cntCom + 1;
                    }
                }
                // get categories for entry
    $cntTmp = 0;
    $sql = "SELECT * FROM evo_postcats WHERE postcat_post_ID=" . $eid;
    $res = mysql_query($sql, $resB2) or die("Invalid query: " . mysql_error() . "<BR /> SQL : " . $sql);
    if ($res) {
     while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
      $cid = $arCat[$row['postcat_cat_ID']];
      $sql = trim(str_replace("\n","","INSERT INTO `". $wp_pref ."post2cat` ".
       "(`post_id`,`category_id`)" .
       " VALUES ('" . $id . "','" . $cid . "');"));
      //$q = mysql_query($sql, $resWP) or die("Invalid query: " . mysql_error() . "<BR /> SQL : " . $sql);
      $cntCat = $cntCat + 1;
      $cntTmp = $cntTmp + 1;
     }
    }
    if ($cntTmp == 0) {
     // No categories defined in b2evo - put it in the default category
     $sql = trim(str_replace("\n","","INSERT INTO `". $wp_pref ."post2cat` ".
      "(`post_id`,`category_id`)" .
      " VALUES ('" . $id . "','1');"));
     //$q = mysql_query($sql, $resWP) or die("Invalid query: " . mysql_error() . "<BR /> SQL : " . $sql);
     $cntCat = $cntCat + 1;
    }
            }
            echo $cnt . " Entry record(s) imported! <BR />";
            echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" . $cntCom . "Comment record(s) imported! <BR />";
            echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" . $cntCat . "Entry Category record(s) imported! <BR />";
        } else {
            echo "No Entry records found!<BR />";
        }
        mysql_close($resB2);
        mysql_close($resWP);
        echo "That's all folks!";
 break;
}
?>
</body>
</html>

No comments:

Post a Comment