It took me longer to write this up than it did to fix the issue, but I thought I’d share a couple of handy SQL queries for fixing the users and posts relationships when converting existing sites to MultiSite:

How I Moved Users From a Single WordPress Install to a MultiSite Install

I have a client on WPEngine who wanted to convert one of their existing sites to a MultiSite install and then merge another of their properties (Site Two) with it as a new blog.

After MultiSite was enabled, I tried a few different solutions for safely merging Site Two’s content, users and plugin options into the new blog and thought I’d share my notes for those of you who are familiar with managing mySQL with phpMyAdmin but maybe not really comfy with SQL.

What should have worked:

From reading the two following links –

http://teqsnacks.com/2009/05/18/moving-multi-author-wordpress-blog-import-export-users/ http://stephanieleary.com/2010/09/migrating-single-wordpress-installations-into-multisite-networks/

I should have been able to export the users from the old site to CSV (with a plugin), import them into the new site in CSV (with another plugin), then export all the posts, pages, etc. with WordPress’ export tool and import the XML file into the new site and have WordPress assign those posts to the users I just imported.

That might have worked. But WPEngine threw 502 errors after I manually assigned 150 old users to their new user counterparts during the import. That’s annoying.

You’re still going to need these plugins tho, so here’s the links:

http://wordpress.org/plugins/export-users-to-csv/
http://wordpress.org/plugins/import-users-from-csv/

Okay. So I’m getting a 502 trying to do this over http in the backend, but I have database access, right? Right. I have the posts in the new database, but with the old user IDs (I imported those via phpMyAdmin between the old and new databases). I have the users imported, but they have all new user IDs assigned. Here’s what I did to match the users with their posts in the new database:

– I went to the old database and exported the wp_users table to a file. I included Create Table statements in the export (check the handy checkbox in phpMyAdmin)

– I opened that file up and edited it so that it would create a new, uniquely named users table in my new database. Why? Because all these users already exist in the new wp_users table (remember? I imported them via CSV), I just want their old ID numbers so I can join them with the new IDs that were created when they were imported. The old user IDs are still being referenced in the new wp_X_posts table, so the posts in the admin aren’t linking to the correct users.

– I imported the SQL file into the new database and it created a new users table named wp_X_users (X being the blog id so I know which site it belongs to) that contained the user logins, passwords and IDs for the users in Site Two.

– I edited the new users table in phpMyAdmin to append a new column to it called “new_ID”. This is where I’m going to store the new IDs that were created when these users were imported into the new database.

– Then I ran the following SQL queries to match up the old IDs with the new IDs and update them in the new wp_X_users table (edit the $wpdb and $blogid variables to match your own db setup):

UPDATE $wpdb_$blogid_users INNER JOIN $wpdb_users ON $wpdb_$blogid_users.user_login = $wpdb_users.user_login SET $wpdb_$blogid_users.new_ID = $wpdb_users.ID WHERE $wpdb_$blogid_users.user_login = $wpdb_users.user_login;

What that query is doing is joining the new users table I created with the live wp_users table that WordPress MultiSite is totally relying on (so don’t modify it) by matching the user_logins in both tables. Then it’s updating the new_ID field in the new users table with the IDs that were assigned to the users when I imported them via CSV.

Awesome. Let’s tell the posts table about this so the posts and authors can be reunited and live happily ever after:

UPDATE $wpdb_$blogid_posts INNER JOIN $wpdb_$blogid_users ON $wpdb_$blogid_users.ID = $wpdb_$blogid_posts.post_author SET $wpdb_$blogid_posts.post_author = $wpdb_$blogid_users.new_ID WHERE $wpdb_$blogid_posts.post_author = $wpdb_$blogid_users.ID;

So I’m joining my new users table and the posts table for Site Two on the old user ID from the old database that still lives in the post_author field in the new database. Then I’m updating that post_author field with the new_ID I grabbed from the wp_users table in the new database so that the posts are correctly linked to their authors.

Obvs. this write up assumes you have a lot of experience with WordPress, WordPress MultiSite and its data structure, if not SQL itself, so hopefully it’s a handy reference when you haven’t JOINed tables in awhile and even more hopefully, maybe it can be turned into a better conversion tool from Single to MultiSite installs since porting users over is the weak point in that whole process.