Migrating to Drupal: database migration

When migration to Drupal, one of the hardest things will be to migrate your content from your old CMS. If you have some basic conception of SQL and a scripting language like PHP, it doesn't have to be too hard though.You can do it in two steps:

1/ Setting the categories
First you need to create the categories, and mark their tid. You can find those in the term_data table. This is a good time to give them different names, or to merge old categories if you want. For example, let's create three categories:

news, with tid 1
interviews, with tid 2
history, with tid3

Put those in an array, with the old names as index:

<?php
$term = array(
  'newsitems' => 1,
  'intvws' => 2,
  'hist' => 3,
);
?>

2/ Let's migrate!
Now let's continue with the rest of the script.
In the sequence table, look up the current nid, and write the value in a variable:

<?php
$nid="10";
?>

Then, make up your SELECT query. For instance, for pMachine the query will look like this:

<?php
$data = mysql_query("SELECT t_stamp,title,body,weblog FROM yourtable.pm_weblog WHERE
weblog = 'newsitems' OR
weblog = 'intvws' OR
weblog = 'hist'
");
?>

Then all that's left is creating the INSERT query. The whole script will look like this:

<?php
mysql_connect("localhost","username","password");

$data = mysql_query("SELECT t_stamp,title,body,weblog FROM pmtable.pm_weblog WHERE
weblog = 'newsitems' OR
weblog = 'intvws' OR
weblog = 'hist'
");

$term = array(
  'newsitems' => 1,
  'intvws' => 2,
  'hist' => 3,
);

$nid="10";

while ($d = mysql_fetch_array($data)) {
   mysql_query("INSERT INTO drupaltable.node SET
type='story',
title='".addslashes($d["title"])."',
uid='1', // root user
status='1', // status: published
created='".$d["t_stamp"]."', 
changed='".$d["t_stamp"]."',
comment='0', // allow no comments. set 1 for read, 2 for read/write
promote='1', // promote to frontpage. set 0 for disabled
teaser='', // no teaser. you could add "addslashes($d["body"])" here
body='".addslashes($d["body"])."',
sticky='0', // not sticky. set 1 for enabled
format='4'") // 4 is the custom bbcode
or die($d["title"]);
   mysql_query("INSERT INTO drupaltable.term_node SET nid='".$nid."',tid='".$term[$d["weblog"]]."'");
   $nid++;
}
?>

Be sure to update the sequence table's nid with the newest id in the node table. You could add this at the end of the script:

<?php
mysql_query("UPDATE drupaltable.sequence SET nid='".$nid."'");
?>

When migrating from XOOPS for instance, all you need to change is the SELECT statement, and the field names in the INSERT statement. Good luck and happy migrating!

Wim Mostrey Engaging Drupal

Recent posts

Migrating to Drupal: database migration

Comments

Thanx for the nice post very

Post new comment