By ravinggenius on

I am in the process of converting a Joomla! site to Drupal (Drupal rocks!), and I had a bit of difficulty finding documentation on how exactly I needed to move the Joomla! articles over. I did find this post, but it didn't highlight the details like I was hoping for. So I thought I'd sign up so to share what I have learned. I do hope this will help others. I am moving over to Drupal 4.7.x, so I hope this is still applicable to 5.x.

By the way using a program like SQLyog or MySQL Query Browser makes this process very much easier, and faster to boot, since you don't have to wait for page loads with PHPmyAdmin.

  1. First, to make things easy, copy the db_joomla.jos_content table straight over to the Drupal database, naming it db_drupal.node_jos_content. This allows for very convenient access.
  2. Next run the following SQL for each article, but be sure to make a note of the comments.

Here is the SQL script I came up with. It has a few caveats:

  • As it is @nid and @vid (MySQL user variables) have to be updated manually for each article. That blows if you have a lot to import.
  • I wasn't really concerning myself with keeping Joomla! articles in certain Drupal categories, as Drupal's way of doing things is much more flexible. I was planning on manually reassigning categories later.
  • I found that @vid was only being incremented by one each time I ran this. Therefore, the first time through, I set it manually, but the rest of the times, I made it auto-increment (see the code).
/**
i tried and tried to get variations of SET @nid = (SELECT MIN(id) FROM node_jos_content WHERE state = 1 AND sectionid = 1); to work, but it just wouldn't. therefore i was stuck with manually changing this variable. this also meant that I was not able to put this in a loop with out using PHP. (i didn't want to use PHP for this.)
*/
SET @nid = 0;
/**
find out what the largest value for node:vid is; it should correspond to the largest value in node_revisions:vid. set @vid to that value (plus one) for the first time through, then comment it out. the SET @vid = @vid + 1; at the bottom will keep it up.
*/
# SET @vid = 0;
INSERT
INTO node (nid, vid, type, title, uid, created, changed, promote)
	SELECT id AS nid, @vid AS vid, 'page' AS type, title, 1 AS uid, UNIX_TIMESTAMP(created) AS created, UNIX_TIMESTAMP(modified) AS modified, 1 AS promote
	FROM node_jos_content
	WHERE id = @nid
	LIMIT 1;
INSERT
INTO node_revisions (nid, vid, uid, title, body, teaser, timestamp, format)
	SELECT id, @vid AS vid, 1 AS uid, title, introtext AS body, introtext AS teaser, UNIX_TIMESTAMP(modified) AS timestamp, 3 AS format
	FROM node_jos_content
	WHERE id = @nid
	LIMIT 1;
/**
this makes sure that articles don't get inserted more than once.
*/
UPDATE node_jos_content
SET state = 0
WHERE id = @nid;
/**
these two updates are very important for when you create a new node. you will get an error when trying to post if these are not set to the MAX() of node[_revisions]:n[v]id, respectively
*/
UPDATE sequences
SET id = @nid
WHERE name = 'node_id';
UPDATE sequences
SET id = @vid
WHERE name = 'node_revisions_id';
SET @vid = @vid + 1;

I know that this is pretty easy to do, and that it should be in a loop before tackling a large number of articles. Hopefully somebody will expound on this and help fill it out.

⋅ Categories: Drupal 4.7.x