I recently migrated Georgetown’s Communication, Culture & Technology Journal Gnovis from Drupal to WordPress and I thought I would share how I did it.

In the coming weeks I will be working with Georgetown’s Gnovis team to redesign the site entirely. The first step in this process was migrating the site from Drupal to WordPress at the request of the editors who prefer the WordPress CMS over Drupal.

Before starting the site redesign, I wanted to make sure I backed up the Drupal database, which consists of journal articles, blog posts and event posts.

**MAKE SURE YOU BACK UP YOUR DRUPAL DATABASE SO YOU WON’T LOSE ANY DATA**

Once you have a backup, you’ll want to install WordPress on your webserver, create a database for this WordPress site, then copy the Drupal database into you new WordPress database and cleanup Drupal/Wordpress differences using the scripts below to complete the migration.

1. Install WordPress

– Download WordPress and unzip this file on your webserver.
– Create an empty mySQL database for WordPress using phpMyAdmin or your host.
– Configure your WordPress installation going to your site ../wp-config.php (EX:www.mysite.com/wp-config.php)
– Remove any posts and pages that already exist in your fresh WordPress site, including the “About” demo page and “Hello World” post.

2. Copy the Drupal database into your new WordPress database

– Login into phpMyAdmin.
– Click on the old Drupal database in phpMyAdmin and select the tab “Operations“
– In the section “Copy database to:“, fill in the name of the WordPress database
– Leave the selection on “Structure and data”
– Deselect “CREATE DATABASE before copying“, because we have already the database
– Select “Add DROP TABLE / DROP VIEW“
– Select “Add AUTO_INCREMENT value“
– Leave the other selections off
– Now press on the Go-button, to copy the database

* For my Gnovis migration, I created a new MySQL database called “gnovis_wp” (you will want to find and replace this with the name of your new WordPress database (Example: “wordpress”). If you add a prefix to your WordPress database such as “wp_” you will also need to refine the following to take that into account.

3. Converting the Drupal tables into WordPress database tables

Here are a few scripts I adapted from other scripts to complete the migration:
** I highly recommend editing and pasting each item separately into your SQL editor so that they complete **

To run the conversion script, you need to perform the following steps:

– Login into phpMyAdmin
– Click on the new WordPress database
– Click on the tab “SQL“
– Select the above script and copy it (Ctrl-C)
– Open your favorite editor and paste (Ctrl-V) the script in the editor
– Comment out VIDEO and IMAGE if applicable
– Select the complete script in the editor (Ctrl-A) and past it in the “SQL” tab
– Press the Go-button to start the conversion
– It is possible to re-run the script more than once in case of problems.

# TAGS
# Using REPLACE prevents script from breaking if Drupal contains duplicate terms.
#REPLACE INTO gnovis_wp.wp_terms
   # (term_id, `name`, slug, term_group)
   # SELECT DISTINCT
#	d.tid, d.name, REPLACE(LOWER(d.name), ' ', '_'), 0
   # FROM gnovis_wp.term_data d
   # INNER JOIN gnovis_wp.term_hierarchy h
#	USING(tid)
   # INNER JOIN gnovis_wp.term_node n
#	USING(tid)
   # WHERE (1
	 # This helps eliminate spam tags from import; uncomment if necessary.
	 #AND LENGTH(d.name) < 50
   # )
#;

#INSERT INTO gnovis_wp.wp_term_taxonomy
   # (term_id, taxonomy, description, parent)
    #SELECT DISTINCT
	#d.tid `term_id`,
	#'post_tag' `taxonomy`,
	#d.description `description`,
	#h.parent `parent`
    #FROM gnovis_wp.term_data d
    #INNER JOIN gnovis_wp.term_hierarchy h
#	USING(tid)
    #INNER JOIN gnovis_wp.term_node n
	#USING(tid)
    #WHERE (1
	 # This helps eliminate spam tags from import; uncomment if necessary.
	 #AND LENGTH(d.name) < 50
    #)
#;

# POSTS
# Keeps private posts hidden.
INSERT INTO gnovis_wp.wp_posts
    (id, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt,
post_name, post_modified, post_modified_gmt, post_type, `post_status`)
    SELECT DISTINCT
	n.nid `id`,
	n.uid `post_author`,
	FROM_UNIXTIME(n.created) `post_date`,
	FROM_UNIXTIME(n.created-3600) `post_date_gmt`,
	r.body `post_content`,
	n.title `post_title`,
	r.teaser `post_excerpt`,
	IF(SUBSTR(a.dst, 11, 1) = '/', SUBSTR(a.dst, 12), a.dst) `post_name`,
	FROM_UNIXTIME(n.changed) `post_modified`,
	FROM_UNIXTIME(n.changed-3600) `post_modified_gmt`,
	n.type `post_type`,
	IF(n.status = 1, 'publish', 'private') `post_status`
    FROM gnovis_wp.node n
    INNER JOIN gnovis_wp.node_revisions r
	USING(vid)
    LEFT OUTER JOIN gnovis_wp.url_alias a
	ON a.src = CONCAT('node/', n.nid)
    # Add more Drupal content types below if applicable.
    WHERE n.type IN ('post', 'page', 'blog')
;

# Fix post type;
# Add more Drupal content types below if applicable.
UPDATE gnovis_wp.wp_posts
    SET post_type = 'post'
    WHERE post_type IN ('blog')
;

# Set all pages to "pending".
# If you're keeping the same page structure from Drupal, comment out this query
# and the new page INSERT at the end of this script.
UPDATE gnovis_wp.wp_posts SET post_status = 'pending' WHERE post_type = 'page';

# POST/TAG RELATIONSHIPS
INSERT INTO gnovis_wp.wp_term_relationships (object_id, term_taxonomy_id)
    SELECT DISTINCT nid, tid FROM gnovis_wp.term_node
;

# Update tag counts.
UPDATE term_taxonomy tt
    SET `count` = (
	SELECT COUNT(tr.object_id)
	FROM term_relationships tr
	WHERE tr.term_taxonomy_id = tt.term_taxonomy_id
    )
;

# COMMENTS
# Keeps unapproved comments hidden.

INSERT INTO gnovis_wp.wp_comments
    (comment_post_ID, comment_date, comment_date_gmt, comment_content,
comment_parent, comment_author, comment_author_email, comment_author_url, comment_approved)
    SELECT DISTINCT
	nid, FROM_UNIXTIME(timestamp), FROM_UNIXTIME(timestamp-3600), comment,
thread, name,
	mail, homepage, ((status + 1) % 2)
    FROM gnovis_wp.comments
;

# Fix taxonomy;
UPDATE IGNORE gnovis_wp.wp_term_relationships,
gnovis_wp.wp_term_taxonomy
    SET gnovis_wp.wp_term_relationships.term_taxonomy_id =
gnovis_wp.wp_term_taxonomy.term_taxonomy_id
    WHERE gnovis_wp.wp_term_relationships.term_taxonomy_id =
gnovis_wp.wp_term_taxonomy.term_id
;

# OPTIONAL ADDITIONS ‚ REMOVE ALL BELOW IF NOT APPLICABLE TO YOUR CONFIGURATION

# CATEGORIES
# These are NEW categories, not in gnovis_wp.wp_gnovis_ Add as many sets as needed.
INSERT IGNORE INTO gnovis_wp.wp_terms (name, slug)
    VALUES
    ('First Category', 'first-category'),
    ('Second Category', 'second-category'),
    ('Third Category', 'third-category')
;

# Set category names to title case (in case term already exists [as a tag] in lowercase).
UPDATE gnovis_wp.wp_terms SET name = 'First Category' WHERE name = 'first category';
UPDATE gnovis_wp.wp_terms SET name = 'Second Category' WHERE name = 'second category';
UPDATE gnovis_wp.wp_terms SET name = 'Third Category' WHERE name = 'third category';

# Add categories to taxonomy.
INSERT INTO gnovis_wp.wp_term_taxonomy (term_id, taxonomy)
    VALUES
    ((SELECT term_id FROM wp_terms WHERE slug = 'first-category'), 'category'),
    ((SELECT term_id FROM wp_terms WHERE slug = 'second-category'), 'category'),
    ((SELECT term_id FROM wp_terms WHERE slug = 'third-category'), 'category')
;

# Auto-assign posts to category.
# You'll need to work out your own logic to determine strings/terms to match.
# Repeat this block as needed for each category you're creating.
INSERT IGNORE INTO gnovis_wp.wp_term_relationships (object_id, term_taxonomy_id)
    SELECT DISTINCT p.ID AS object_id,
	(SELECT tt.term_taxonomy_id
	FROM gnovis_wp.wp_term_taxonomy tt
	INNER JOIN gnovis_wp.wp_terms t USING (term_id)
	WHERE t.slug = 'enter-category-slug-here'
	AND tt.taxonomy = 'category') AS term_taxonomy_id
    FROM gnovis_wp.wp_posts p
    WHERE p.post_content LIKE '%enter string to match here%'
    OR p.ID IN (
	SELECT tr.object_id
	FROM gnovis_wp.wp_term_taxonomy tt
	INNER JOIN gnovis_wp.wp_terms t USING (term_id)
	INNER JOIN gnovis_wp.wp_term_relationships tr USING (term_taxonomy_id)
	WHERE t.slug IN ('enter','terms','to','match','here')
	AND tt.taxonomy = 'post_tag'
    )
;

# Update category counts.
UPDATE wp_term_taxonomy tt
    SET `count` = (
	SELECT COUNT(tr.object_id)
	FROM wp_term_relationships tr
	WHERE tr.term_taxonomy_id = tt.term_taxonomy_id
    )
;

# AUTHORS
INSERT IGNORE INTO gnovis_wp.wp_users
    (ID, user_login, user_pass, user_nicename, user_email,
    user_registered, user_activation_key, user_status, display_name)
    SELECT DISTINCT
	u.uid, u.mail, NULL, u.name, u.mail,
	FROM_UNIXTIME(created), '', 0, u.name
    FROM gnovis_wp.users u
    INNER JOIN gnovis_wp.users_roles r
	USING (uid)
    WHERE (1
	# Uncomment and enter any email addresses you want to exclude below.
	# AND u.mail NOT IN ('test@example.com')
    )
;

# Assign author permissions.
# Sets all authors to "author" by default; next section can
# selectively promote individual authors
INSERT IGNORE INTO gnovis_wp.wp_usermeta (user_id, meta_key, meta_value)
    SELECT DISTINCT
	u.uid, 'capabilities', 'a:1:{s:6:"author";s:1:"1";}'
    FROM gnovis_wp.users u
    INNER JOIN gnovis_wp.users_roles r
	USING (uid)
    WHERE (1
	# Uncomment and enter any email addresses you want to exclude below.
	# AND u.mail NOT IN ('test@example.com')
    )
;
INSERT IGNORE INTO gnovis_wp.wp_usermeta (user_id, meta_key, meta_value)
    SELECT DISTINCT
	u.uid, 'user_level', '2'
    FROM gnovis_wp.users u
    INNER JOIN gnovis_wp.users_roles r
	USING (uid)
    WHERE (1
	# Uncomment and enter any email addresses you want to exclude below.
	# AND u.mail NOT IN ('test@example.com')
    )
;

# Change permissions for admins.
# Add any specific user IDs to IN list to make them administrators.
# User ID values are carried over from gnovis_wp.wp_gnovis_
UPDATE gnovis_wp.wp_usermeta
    SET meta_value = 'a:1:{s:13:"administrator";s:1:"1";}'
    WHERE user_id IN (1) AND meta_key = 'capabilities'
;
UPDATE gnovis_wp.wp_usermeta
    SET meta_value = '10'
    WHERE user_id IN (1) AND meta_key = 'user_level'
;

# Reassign post authorship.
UPDATE gnovis_wp.wp_posts
    SET post_author = NULL
    WHERE post_author NOT IN (SELECT DISTINCT ID FROM gnovis_wp.wp_users)
;

# Fix post_name to remove paths.
# If applicable; Drupal allows paths (i.e. slashes) in the dst field,
# but this breaks
# WordPress URLs. If you have mod_rewrite turned on,
# stripping out the portion before
# the final slash will allow old site links to work properly,
# even if the path before
# the slash is different!
UPDATE gnovis_wp.wp_posts
    SET post_name =
    REVERSE(SUBSTRING(REVERSE(post_name),1,LOCATE('/',REVERSE(post_name))-1))
;

# Miscellaneous clean-up.
# There may be some extraneous blank spaces in your Drupal posts;
#use these queries or other similar ones to strip out the undesirable tags.
UPDATE gnovis_wp.wp_posts
    SET post_content = REPLACE(post_content,'

','')
;
UPDATE gnovis_wp.wp_posts
    SET post_content = REPLACE(post_content,'

','')
;

# NEW PAGES, READ BELOW AND COMMENT OUT IF NOT APPLICABLE
TO YOUR SITE
# MUST COME LAST IN THE SCRIPT AFTER ALL OTHER QUERIES!
# If your site will contain new pages, you can set up the basic structure
#for them here.
# Once the import is complete, go into the WordPress admin and copy content
#from the Drupal
# pages (which are set to "pending" in a query above) into the appropriate
#new pages.
INSERT INTO gnovis_wp.wp_posts
    (`post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`,
    `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`,
    `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`,
    `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`,
    `post_mime_type`, `comment_count`)
    VALUES
    (1, NOW(), NOW(), 'Page content goes here, or leave this value empty.', 'Page Title',
    '', 'publish', 'closed', 'closed', '',
    'slug-goes-here', '', '', NOW(), NOW(),
    '', 0, 'http://full.url.to.page.goes.here', 1, 'page', '', 0)
;

Once you have completed your SQL scripts you should have all of your Drupal content in your WordPress database. Check your WordPress installation and you should see your posts, authors, comments, etc. have moved over to your WordPress site, as shown here: