





Note: This will work for converting up to the latest version of Drupal to the latest version of WordPress, as of January 1, 2015.

After hunting down some tutorials and finding some missing important parts, no explanations, and being disappointed, I had to do some experimenting. As a developer, I do not mind solving a challenge and putting puzzles together. There were some decent tutorials, however, I found that once I completed my own Drupal to WordPress conversion, I needed to share the process in the easiest way possible.

Please note that that this tutorial to convert Drupal to WordPress is not giving you the goods on how to convert your stat counters over, if you need to make sure your Disqus comments match up (in the case you use Disqus instead of the content management system’s native comments), or special modules that are storing information in the database. You will need to seek building a extended custom plan for you to convert Drupal to WordPress.

Some of the issues I ran into were questions that other developers had with whether tables could be converted over as well as users. In fact, a lot of the questions I saw were over the user conversion from Drupal to WordPress. Also, this was a problem that was not addressed in some of the tutorials to convert Drupal to WordPress. Some of the scripts did not properly work, or did not have anything about converting over users.

I also found issues with the categories and tags not being translated over to the correct taxonomy when doing my conversion. In fact, they were all slopped into the tags in most cases. My solution is before converting to make sure in the original Drupal install that these taxonomies are correctly labeled. Tags are non-hierarchical and Categories are hierarchical.

Another big problem was running into duplicate errors when feeding the SQL through the conversion process. The solution for this was to check the last known table that was converted over and only convert from after that table. It was tedious, especially since my conversion was for 17,000 posts and over 2,500 users. What a way to break in figuring out how to convert Drupal to WordPress, right?

This post goes over how to convert Drupal to WordPress, mostly the core the database (like posts, post meta, taxonomies, and post types, users, and comments), and a little bit about converting the theme.

Convert Drupal To WordPress: The Database

1. Export the Drupal SQL file from the old host.

2. Install a fresh new WordPress site. You need to clear the tables with the TRUNCATE command by going into your database’s SQL queries tab in phpmyadmin. The code is:

TRUNCATE TABLE wordpress.wp_comments; TRUNCATE TABLE wordpress.wp_links; TRUNCATE TABLE wordpress.wp_postmeta; TRUNCATE TABLE wordpress.wp_posts; TRUNCATE TABLE wordpress.wp_term_relationships; TRUNCATE TABLE wordpress.wp_term_taxonomy; TRUNCATE TABLE wordpress.wp_terms;

Replace the wordpress before the period with the database table name before executing the query. In the case you are doing the query from that WordPress database directly, you do not need the wordpress and the period in the code when executing the script.

3. If you have multiple users to convert over, you will need to use the following code.

DELETE FROM wordpress.wp_users WHERE ID > 1; DELETE FROM wordpress.wp_usermeta WHERE user_id > 1;

Again, replace the wordpress before the period with the database table name before executing the query.

4. The following query will convert over tags:

REPLACE INTO wordpress.wp_terms (term_id, `name`, slug, term_group) SELECT DISTINCT d.tid, d.name, REPLACE(LOWER(d.name), ' ', '_'), 0 FROM drupal.term_data d INNER JOIN drupal.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.term_data d INNER JOIN drupal.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 ) ;

5. The following converts over posts.

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 `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`, n.type `post_type`, IF(n.status = 1, 'publish', 'private') `post_status` FROM drupal.node n INNER JOIN drupal.node_revisions r USING(vid) LEFT OUTER JOIN drupal.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') ;

Please note that if your Drupal installation has several post types, you will want to add the name of the post type into the to the line that says WHERE n.type IN (‘post’, ‘page’, ‘blog’). If you do not add all post types, then not all will be converted over.

6. In the case you want to combine post types in WordPress. Say you wanted post and blog together, you would put the following code:

UPDATE wordpress.wp_posts SET post_type = 'post' WHERE post_type IN ('blog') ;

7. The following code defines the post/ tag relationship

INSERT INTO wordpress.wp_term_relationships (object_id, term_taxonomy_id) SELECT DISTINCT nid, tid FROM drupal.term_node ; # Update tag 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 ) ;

8. The following code is for 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(timestamp), comment, thread, name, mail, homepage, ((status + 1) % 2) FROM drupal.comments ; # 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` ) ;

9. If you are keeping your Drupal images and filed in the same place, you do not have to do anything. However, if you are FTP-ing your files to the uploads folder in your WordPress wp-content folder, you will want to use the following script to fix the URLs to the images.

UPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, '"/files/', '"/wp-content/uploads/');

10. The following code is suppose to help fix taxonomy… that is if it was set up correctly in the original Drupal site.

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 ;

11. Author roles for your users:

INSERT IGNORE INTO wordpress.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 drupal.users u INNER JOIN drupal.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') ) ;

This sets the author role’s permissions.

INSERT IGNORE INTO wordpress.wp_usermeta (user_id, meta_key, meta_value) SELECT DISTINCT u.uid, 'wp_capabilities', 'a:1:{s:6:"author";s:1:"1";}' FROM drupal.users u INNER JOIN drupal.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 wordpress.wp_usermeta (user_id, meta_key, meta_value) SELECT DISTINCT u.uid, 'wp_user_level', '2' FROM drupal.users u INNER JOIN drupal.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') ) ;

You may want to uncomment (remove the number sign) before the line that has the email address and put your own in so your role remains as an administrator.

12. Assign and give administrator status.

UPDATE wordpress.wp_usermeta SET meta_value = 'a:1:{s:13:"administrator";s:1:"1";}' WHERE user_id IN (1) AND meta_key = 'wp_capabilities' ; UPDATE wordpress.wp_usermeta SET meta_value = '10' WHERE user_id IN (1) AND meta_key = 'wp_user_level' ;

13. The following code helps assign authors to the posts they wrote.



UPDATE wordpress.wp_posts

SET post_author = NULL

WHERE post_author NOT IN (SELECT DISTINCT ID FROM wordpress.wp_users)

;



14. Depending on how the original Drupal site has been set up, sometimes the URLs are funny, try feeding the following query. It does not hurt to try.

UPDATE wordpress.wp_posts SET post_author = NULL WHERE post_author NOT IN (SELECT DISTINCT ID FROM wordpress.wp_users) ;

15. The following is some extra clean up for the editor so your posts do not look too funky from the conversion.

UPDATE wordpress.wp_posts SET post_name = REVERSE(SUBSTRING(REVERSE(post_name),1,LOCATE('/',REVERSE(post_name))-1)) ;

There are quite a few other options, but I found these the most necessary for my install. In the case you want to look through for more options, you can check out the Drupal to WordPress Migration at the Underdog of Perfection.

I did run into one last thing that was a problem to convert Drupal to WordPress. Once I had everything over, I went to click on the individual posts and pages. I did already set the permalinks to postname. However, the links kept redirecting back to the home page. I had to update the slugs.

To do so, you need to put the following code in the wp-config.php file before the ending PHP tag. Once you have, click save and then reload the front of your site. The script will run and should not take long at all. Once the front of your site loads, go back into the wp-config.php file and remove the code.

// We prepare a variable to hold arrays with post titles so we don't accidentally make duplicates, that would be bad $slug_done = array(); // Run a query to grab all the posts, we only want posts/pages though $posts = $wpdb->get_results( " SELECT `ID`, `post_title` FROM `" . $wpdb->posts . "` WHERE `post_type` = 'page' OR `post_type` = 'post' " ); // Loop through results foreach( $posts AS $single ) { // Generate a URL friendly slug from the title $slug_base = sanitize_title_with_dashes( $single->post_title ); $this_slug = $slug_base; $slug_num = 1; // Check if the slug already exists, if it does, we add an incremental integer ot the end of it while (in_array( $this_slug, $slug_done ) ) { $this_slug = $slug_base . '-' . $slug_num; $slug_num++; } $slug_done[] = $this_slug; // We are happy with our slug, update the database table $wpdb->query( " UPDATE `" . $wpdb->posts . "` SET `post_name` = '" . $this_slug . "' WHERE `ID` = '" . $single->ID . "' LIMIT 1 " ); }

Convert Drupal To WordPress: The Theme

If you are familiar with, HTML, PHP, CSS, WordPress theming, and Drupal theming, great! Most people usually write about how to convert Drupal to WordPress, and not talk about the theme conversion, especially for those who love their theme and just want to switch the content management system. You should know that the concepts of theming in WordPress are similar in Drupal. You design the theme, and in the parts that you want WordPress to do the work, like pages, posts, and widgets, you place the code with the coordinating loop and dynamic sidebar PHP call to those areas. If you are not familiar with theming, you can refer to my podcast presentation that includes a slideshow on PSD to WordPress, which delves a little deeper into WordPress theme templates.

You can literally remove all the codes for modules and such from the Drupal theme templates and replace with the WordPress code.

I hope this helps explain the steps to convert Drupal to WordPress. Please use at your own risk and always have a back up before you remove anything for good.