WordPress Hacking: Multiple Blogs On One Set of Tables

Note: This post was written for WordPress 2.1. Please see the comments for discussion on how to apply to newer versions!

I recently rolled out a new theme for my website. Normally when I do this, I do it against the live site, so there’s about an hour where the site is either broken, or looks really weird while I’m working on it. This time I wanted to be a little smarter.

The goal was to build another blog — a “test” blog where I can try out new themes and other experiments — that pointed to the same data as the main site. This way I could get a realistic picture of what the site would actually look like before I publish an updated theme.

The more I thought about that, the more useful the concept seemed. What if I wanted an iPhone-friendly version of the site? I wouldn’t want to write the same post in two places. Now eXpression could do this very easily — since every “page” was actually a query, you could just specify a different theme file (XSL) in the query. WordPress, not so much.

What WordPress can do is allow you to specify a table prefix for each installation on a given server. That means you can have 3 blogs, each with their own set of tables. When you build your wp-config file, you tell it what prefix to use. Since this is the case, it should be fairly easy to install another instance of WordPress and point it back to the original blog tables, right? Here’s how you do it…

Step 1 – Install a new instance of WordPress

  • Make sure it has a unique folder name so it doesn’t over-write your original blog
  • Run the install pages, and give the new blog a unique table prefix
    • IE: If your original blog got the default prefix: wp_ then give the new blog newwp_
  • Get the blog up and running with an admin account, but don’t bother posting anything

Step 2 – Point the new blog to the original blog’s tables

  • Modify wp-settings.php as per the instructions found here
    • Modify all the table pointers EXCEPT for Options
  • This means that only the Options (including Theme and Plugins) will be specific to your new site. Everything else (including Posts and Comments) will come from the original site
  • So anywhere you see: $wpdb->users = $wpdb->prefix .
    Change it to: $wpdb->users = ‘wp_’ . ‘users’;

    • where ‘wp_’ is the table prefix for your original blog

Step 3 – Grant the admin user from your original blog the same permissions on your new blog, by inserting two records into your wp_usermeta table:

  • Where wp_ is the table prefix of your original blog and newwp_ is the table prefix of your new blog…
    • INSERT INTO wp_usermeta (user_id, meta_key, meta_value) VALUES (1, 'newwp_user_level, 10')
    • INSERT INTO wp_usermeta (user_id, meta_key, meta_value) VALUES (1, 'newwp_capabilities', 'a:1:{s:13:"administrator";b:1;}')

That’s it! You now have two uniquely styled blogs with the same data in them! The only thing that sucks is if you have Sidebar widgets configured, you’ll need to re-configure them in your new blog as well and manually keep them in sync. That’s the case for all options, and that’s why this is useful.

A couple other notes:

If you want, you can delete the superfluous “new blog” tables — just make sure you leave the Options table intact.

I have no idea how this will work during a WordPress upgrade. My current plan is to run the upgrade on the original, or base, blog, then manually change the Options table structure, if necessary, for the secondary, or derived, blogs.

Advertisements

25 thoughts on “WordPress Hacking: Multiple Blogs On One Set of Tables

  1. Hi. Thanks for these instructions, but to clarify: do you run these only on one domain? What’s the advantage of doing this as compared to MU? (Apart from the fact that MU doesn’t support all plugins?)

    I would like to run ONE install of WordPress on my root domain, and have it available to all my VirtualHost domains. A bit like EE’s “Multisite Manager” or the Movable Type multiple blogs possibility: http://snipurl.com/mtmulti2

    Any thoughts from you would be super! Thanks!

  2. You can run this with as many domains, subdomains or in-domain directories as you want… but this is really only a hack to get WordPress installations to share a database.
    I don’t know much about WordPress MU, but I can tell you that this approach requires a WordPress install for each site (regardless of domain name) you want to use.
    In other words, you’d have at least one new table in your SQL database for each site, and you’d have one WordPress folder on your server for each site. If you want to upgrade to a new version of WordPress, you’d have to upgrade each installation. For what you’re trying to do, it seems like WordPress MU would be a better approach…

  3. Hi, ran into issues during step 2, I did not find any of the mentioned lines in wp-settings.

    I did find one line which looks similar:

    $prefix = $wpdb->set_prefix($table_prefix);

    but did not have success modifying it as mentioned in the link above, possible i am doing it wrong?

    newwp_ is the custom code for the 2nd install on the same database, so i changed it to read:

    $prefix = ‘newwp_’ . ‘posts’;

    also, i get the following sql error when trying to add the admin user. here is my modified query and the error.

    INSERT INTO wp_usermeta (user_id, meta_key, meta_value) VALUES (1, newwp_user_level, 10)

    #1054 – Unknown column ‘newwp_user_level’ in ‘field list’

    Default is wp_ and new is newwp_

    Any help would be much appreciated.

  4. For problem #1, it appears that wp-settings.php has changed significantly since 2.1. I’m not sure how to update these instructions to make them work. I’ll keep looking when I have some time.

    For problem #2, try enclosing the newwp_user_level in quotes, so the command would look like this:
    INSERT INTO wp_usermeta (user_id, meta_key, meta_value) VALUES (1, ‘newwp_user_level’, 10)

    I used phpMySql to insert it by hand — I didn’t actually use a SQL statement, so I must have made an error when deriving the code for my post. I’ll update it.

    Hope this helps! These instructions were originally written for WordPress 2.1, so if anyone knows how to change things for 2.5, let us know!

  5. The problem with the wp-settings.php file is that they’ve moved to a “set once for all” approach. The line $prefix = $wpdb->set_prefix($table_prefix); sets the table prefix for users, posts AND options, which prevents us from over-riding just the tables we want. I’ll keep poking…

  6. Try this: below the line $prefix = $wpdb->set_prefix($table_prefix);
    just manually over-ride the other tables by adding this code:

    $wpdb->users = ‘wp_’ . ‘users’;
    $wpdb->posts = ‘wp_’ . ‘posts’;
    $wpdb->comments = ‘wp_’ . ‘comments’

    We let WordPress do its global (once-for-all) setting, then we take over by repointing the tables we’re interested in. I’m not able to test this at the moment, but give it a try and let us know if it works!

  7. Jonathan – This was a brilliant help for me. I had a different issue (trying to create a blog for my dad and step-mom and a sub-blog for just my step-mom with the same database). Anyway, in WordPress 2.5 you need to edit the wp-db.php file in the wp-includes directory. In the function set_prefix($prefix) section, you can override the settings. Following

    foreach ( $this->tables as $table )
    $this->$table = $this->prefix . $table;

    the code should look like this:

    $this->posts = ‘wp_’ . ‘posts’;
    $this->users = ‘wp_’ . ‘users’;
    $this->categories = ‘wp_’ . ‘categories’;
    $this->post2cat = ‘wp_’ . ‘post2cat’;
    $this->links = ‘wp_’ . ‘links’;
    $this->postmeta = ‘wp_’ . ‘postmeta’;
    $this->usermeta = ‘wp_’ . ‘usermeta’;
    $this->terms = ‘wp_’ . ‘terms’;
    $this->term_taxonomy = ‘wp_’ . ‘term_taxonomy’;
    $this->term_relationships = ‘wp_’ . ‘term_relationships’;

    Thanks again. This was a big help.

  8. Pingback: United States Of Martin » Blog Archive » One install to rule them all

  9. AAHHHHRRRGG!! I did everything as you said but it does not work.. 😦 Anyway…this is not even what I am trying to achieve but could have helped..what I am really trying to achieve is having one user dedicated admin installation with custom fields and all modified template pages sitting under a subdomain and my admin, full-capable admin installation wherever (on the main domain probably) while BOTH use the same tables and modify ONE website..thanks for the post though..

  10. I can confirm that Tim‘s modification works in 2.6

    Another thing you might want to do is to make symlinks to folders in your main blog, so that your plugins and themes are available in your alternate blog…

    Mario, that sounds like a righteous hack, but its definitely outside of the scope of this. Let us know if you crack it!

  11. HI, I found your site through a google search.
    So I’m a newbie when it comes to wordpress, but I think I can catch on fairly quickly. what I was hoping to find was something that would teach me to have one domain name, and have multiple blogs on that domain name, however, have a main front page, that would have that blog orginized into various preview posts. Basically, I want to be able to do something like ibankcoin.com, only for my own website, with my own theme and content. Do you know how I can do this?

    Thanks,
    Mike

  12. Also, I am interested in having the same type of concept where I have like 5 people that can have their own sign in information, and stuff, as well as a section like you see the “peanut gallery” where anyone can come in register, and then they can come back anytime sign in, and make a post. I know they could register as a “contributer”, but I want to have it seperate from my main blog like it says.

    Any help would be greatly appreciated.

  13. Pingback: One install to rule them all | The Markovian Parallax Denigrate

  14. Anyone have any issues when trying to use the new site admin? I was thinking the users would transfer over? Just wondering since I can’t update the theme I would like to use.

  15. Brun, I haven’t tried this on WordPress 2.7, but the user thing wasn’t a problem in 2.6. If you do any research into this, it’d be great if you could let us know via the comments!

  16. I will! It may have started to fail after removing tables that I’m not using. This is my beta site so I think I’ll start over. I keep you informed.

  17. Well I did some research and I found that options table contains a record for the user roles

    ex. wp_newblog_user_roles a:5:{s:13:”administrator”;a:2:{s:4:”name”;s:23:”Ad… yes

    I needed to update this record to the old user_roles. This allowed me to log into the admin client.

    Now I’m wondering why I would need step 3 since I’m not using any of the users from the new site. I’m just using it for a new theme (iphone) theme of my sit.

  18. Does anyone have any tips/updates for WP v2.8?

    I can’t find many of the code here in the comments that exist in my “wp-settings.php”

    I’m so lost here! :\

    • Sorry, I’m no longer using this method as I’ve switched to a cheaper, but less permissive, host. Honestly, WordPress has changed a lot lately, and I don’t know if this would still work anyway!

  19. Pingback: Jaks Web Design – Single WordPress Database with Multiple Themes

  20. Hello there ! Just wanted to thank you for the little hack, it just works fine 🙂 Thanks to you Jonathan and Tim !

Comments are closed.