Doing a loop with multiple DBs simultaneoulsy

Question

I have a website setup made up of with more than 3-4 WordPress setups as subdirectories of main. For example:

Site
|
---------- Site/Site1
|
---------- Site/Site2
|
---------- Site/Site3

Each have separate codebase, separate DB for itself. The Database server is one and the host directory is also one. Basically we can say these are the mirrors of same setup as subdirectories of the main site.

Now, I want to know: how can I run a Loop in the homepage of parent site "Site" which will fetch and include the posts from "Site1", "Site2", "Site3"?

Note:

-- I can't make it Multisite setup as the codebase is existing one and complex.

-- The tables inside each DB are prefixed the same.

-- For better understanding of the setup situation I have attached a image here.

Answer 1

In the name of bad practices, I'm going to advise that you immediately rule out these using raw SQL queries with remote databases in the same page. It would be slow, insecure, and a hassle to maintain.

That you've landed yourself in a predicament where a combination of hyperDB+multisite is not an option demonstrates that you face serious architectural challenges in your code. Ruling out refactoring and normalisation of your code is not an option because even if you get an appropriate answer to your question, it will still be necessary further down the line, else you'll spend more time in support and maintenance costs than it would be to fix things.

But having said that, you asked a question. Here are potential solutions.

Keep in mind that these criteria will raise the cost and lower the scalability quite dramatically:

• Insisting on having multiple posts from multiple sites in the same loop
• Instantaneous publishing on the main site

So here are some ways of trying it:

• Use RSS to grab posts via WP-Cron. Generate the html for those posts and save it in a transient. Fetch the posts again when it expires. Display the contents of the transient on the frontend.
• Push posts to the main site on publish/update from the sub sites, using XML-RPC
• iframes ( this is a horrific kludgey way of doing it, I mention it here for completeness and advise strongly against it )
• Use an aggregation plug in to grab posts, then publish them on the main site. When a post is clicked on, use wp_redirect to redirect to the original post on the subsites.

Problems I foresee with your setup:

• You're using HyperDB yet all your sites are separate installs
• A site in the root folder may interfere with those in subfolders as you start using rewrite rules
• /site/ may not be a good idea since site is a reserved keyword internally. This will need testing.
• In your example you would end up with 4 usernames and passwords per person. Trying to merge them without multisite is going to be painful.
• Listing site1 posts separately from site 2 posts in the main site is going to be significantly faster and easier than mixing the two, and will scale better.
• Having different versions and configurations of the same code on 4 instances on the same server and domain is going to fall apart at some point in the future. That they cannot be merged into a common codebase with child themes and separate plugins for the specialised components is a sign of bad architecture.