I’ve been running this site on Drupal for quite a while. Back in March I decided to move the site to Drupal 7. While I really like Drupal, there are some things that I would like to do, but just can’t seem to be able to get worked out.
One of the main things I’ve wanted to do was use Windows Live Writer, or my phone to post to the blog. I’ve messed with Blog_api to no avail. While there has been some work in that area, it seemed like there wasn’t enough interest, so the only way I could really post was to go to the website….which I didn’t do very often.
After trying out the WordPress app on my Android phone I realized my best option was to move the site to WordPress. That is _much_ easier said than done. So, for those of you who want to move your Drupal site to WordPress, I want to outline the _basic_ steps. I do this knowing full well that my site isn’t completely functional. I will get to that in a moment.
The best article I’ve seen about moving to WordPress from Drupal is here: http://blog.room34.com/archives/4530 The only problem is he was moving a Druapl 6 site to WordPress. Of course the table names, structures, etc. are different in Druapl 7. So, I took his script (you can get it from the URL above), and had to tweak it a bit.
Basically I ran his script section by section. Since the script doesn’t touch the Drupal database, you can run it over and over again and your Drupal site will remain in tact. I did not run all of his script, just the things I needed.
To use these scripts you’ll have to replace “drupal” with your drupal DB name and wordpress, with your WordPress DB name.
TAGS
The tag section attempts to fill the wp_terms, wp_term_relationship, and wp_term_taxonomy tables. I had to comment out the INNER JOIN because I was having issues with it and couldn’t remember how INNER JOINs worked exactly. I was too lazy to look it up.
# TAGS
# Using REPLACE prevents script from breaking if Drupal contains duplicate terms.
REPLACE INTO wordpress.wp_terms
(term_id, `name`, slug, term_group)
SELECT DISTINCT
d.tid, d.name, REPLACE(LOWER(d.name), ‘ ‘, ‘_’), 0
FROM drupal.taxonomy_term_data d
INNER JOIN drupal.taxonomy_term_hierarchy h
USING(tid)
# INNER JOIN drupal.term_node n
# USING(tid)
WHERE (1
# This helps eliminate spam tags from import; uncomment if necessary.
# AND LENGTH(d.name) < 50
)
;
INSERT INTO wordpress.wp_term_taxonomy
(term_id, taxonomy, description, parent)
SELECT DISTINCT
d.tid `term_id`,
‘post_tag’ `taxonomy`,
d.description `description`,
h.parent `parent`
FROM drupal.taxonomy_term_data d
INNER JOIN drupal.taxonomy_term_hierarchy h
USING(tid)
# INNER JOIN drupal.term_node n
# USING(tid)
WHERE (1
# This helps eliminate spam tags from import; uncomment if necessary.
# AND LENGTH(d.name) < 50
)
;
The next thing I did was run the script for the posts. I think I had to play around with this script a bit to get it to finally do what I wanted. I’m sorry, but I can’t remember for sure if this is what I landed on or not. NOTE: The code below is not functional. You will receive an error if you try to run it. If I get a chance I’ll figure out what I did to get the posts into Drupal.
INSERT INTO wordpress.wp_posts
(id, post_author, post_date, post_content, post_title, post_excerpt,
post_name, post_modified, post_type, `post_status`)
SELECT DISTINCT
n.nid `id`,
n.uid `post_author`,
FROM_UNIXTIME(n.created) `post_date`,
r.body_value `post_content`,
n.title `post_title`,
r.body_summary `post_excerpt`,
n.type `post_type`,
r.entity_id `rid`, <- There is an issue here. Haven’t had a chance to fix this yet.
IF(n.status = 1, ‘publish’, ‘private’) `post_status`
FROM drupal.node n, drupal.field_data_body r
WHERE n.vid = r.entity_id
;
This SQL creates the posts to tag relationships:
# POST/TAG RELATIONSHIPS
INSERT INTO wordpress.wp_term_relationships (object_id, term_taxonomy_id)
SELECT DISTINCT nid, tid FROM drupal.term_node
;
SQL to create the tag counts:
# Update tag counts.
UPDATE wordpress.wp_term_taxonomy tt
SET `count` = (
SELECT COUNT(tr.object_id)
FROM wordpress.wp_term_relationships tr
WHERE tr.term_taxonomy_id = tt.term_taxonomy_id
)
;
Next I ran the SQL for the comments:
INSERT INTO wordpress.wp_comments
(comment_post_ID, comment_date, comment_content, comment_parent, comment_author,
comment_author_email, comment_author_url, comment_approved)
SELECT DISTINCT
nid, FROM_UNIXTIME(created), comment_body_value, thread, name,
mail, homepage, ((status + 1) % 2)
FROM drupal.comment, wordpress.field_data_comment_body
;
# Update comments count on wp_posts table.
UPDATE wordpress.wp_posts
SET `comment_count` = (
SELECT COUNT(`comment_post_id`)
FROM wordpress.wp_comments
WHERE wordpress.wp_posts.`id` = wordpress.wp_comments.`comment_post_id`
)
;
I cannot remember if I ran this next SQL statement or not:
# Fix taxonomy; http://www.mikesmullin.com/development/migrate-convert-import-drupal-5-wordpress-27/#comment-27140
UPDATE IGNORE wordpress.wp_term_relationships, wordpress.wp_term_taxonomy
SET wordpress.wp_term_relationships.term_taxonomy_id = wordpress.wp_term_taxonomy.term_taxonomy_id
WHERE wordpress.wp_term_relationships.term_taxonomy_id = wordpress.wp_term_taxonomy.term_id
;
A couple of problems I ran into:
Because I had Drupal setup to do aliases, my URLs were all off. My main concern was with the traffic I was getting from Google. I tried to setup WordPress to do aliases in the same way, which it will do, however it uses the variable postname. However, in the database table wp_posts the post_name was blank. So I had to take the post_title and try to replace the spaces with dashes, and strip out all the other stuff I didn’t want in a URL. The result was mixed. I had to run a series of REPLACE commands replacing various characters I would use in a title (such as “?”, “!”) with nothing.
Again, this post is simply a guide. If you want to convert your Drupal 7 site to WordPress you need to take the information from the Drupal 7 database and shove it into the WordPress database. The scripts from the URL above will give you a good start. Hopefully some of the SQL statements I used will help as well.
Leave a Reply