Doing a loop with multiple DBs simultaneoulsy

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.

Related Posts:

  1. $wpdb->get_row() only returns a single row?
  2. How to parse row results from $wpdb -> get_results
  3. what is the way to see the currently executing query in wordpress?
  4. Fetch array with $wpdb
  5. $wpdb->last_error doesn’t show the query on error
  6. Theoretical Multi-Server WordPress Setup with Shared Users
  7. Delete/replace img tags in post content for auto published posts [closed]
  8. Speed optimization of $wpdb->get_results
  9. What does wp_update_post() do that the $wpdb class does not?
  10. wpdb-> not adding prefix to custom table
  11. WordPress insert NOW() in TIMESTAMP column returns all zeros
  12. $wpdb prepare issue with mysql DATE_FORMAT
  13. How-To: wpdb Insert Record With Date
  14. How to pass NULL in where array for $wpdb->update
  15. Get random row from custom table
  16. store custom WP table names in a global variable
  17. I am not understandinhg $wpdb->prepare correctly
  18. How to save html and text in the database?
  19. XOR functionality for meta_query
  20. Using WPDB class
  21. WPDB Update using Conditional Arrays
  22. $wpdb->get_results returns empty but value exists
  23. WP Sql query multiple where clause
  24. Problem displaying inserted form
  25. What is _transient_random_seed for?
  26. Get published posts and pages?
  27. Are there risks associated with using wp_options table using wpdb to update theme options
  28. wpdb get_results() and prepare when to use prepare?
  29. Syntax for $wpdb->prepare when searching in two columns
  30. Foreach loop using $wpdb not results from rows
  31. Get an array of meta_values for a user meta_key
  32. How to get a value-only flat array from $wpdb->get_results when selecting a single column, without foreach()?
  33. How to Instantiate wpdb Object in New File
  34. How do you build a wpdb query dynamically?
  35. Would this WPDB setup result in potential race conditions?
  36. How to update a row in a table in WordPress
  37. What’s the proper way to add users to my site in order to test things?
  38. Optimizing WordPress Queries – Removing Group By ID
  39. External DB Connection [closed]
  40. How to get entry by title by using wpdb
  41. Codex: Database Description: meaning of Cardinality
  42. Code only works every other time its run
  43. Can’t pass variable in wordpress wpdb->get_results
  44. WordPress db prepare
  45. How do I count columns on a custom WPDB query?
  46. how to get db values without using an loop with wpdb->get_results()
  47. Custom SQL query ORDER BY term_order
  48. Trouble inserting string containing quotations marks with wpdb in save_post hook
  49. How to left join meta in queries [closed]
  50. How to run wp_insert_post() & wpdb on the background?
  51. Trying to get variable from WP table and toggle its value
  52. Set MySQL variables in WPDB
  53. get_results query with accent
  54. How to prepare an array of values with $wpdb
  55. Is querying wpdb directly and skipping actions provided by WP’s core “wp_update_post” a good idea?
  56. $wpdb->insert() doesnt work anymore
  57. $wpdb query outputs php code instead of executing it
  58. wpdb query not working
  59. Getting values from wpdb
  60. Where can I see MySQL hostname and port for wp-config.php
  61. WordPress wpdb->insert returns int(0) => doesn’t insert anything, no errors!
  62. Using “->” in a page to exceute $wpdb query gives error
  63. Exclude specific terms from all queries using posts_where or something similar
  64. $wpdb->get_results not returning an array
  65. query a newly created table using $wpdb
  66. Get all sticky posts from one user through user ID
  67. WPDB Query Question with Category Only
  68. wpdb result arrray inside an array
  69. $wpdb->num_rows doesn’t work
  70. How can I change my meta_query to SQL wpdb query?
  71. How capturate wpdb exceptions?
  72. how to list all post that are in the custom taxonomy using $wpdb
  73. Limit left join
  74. “This message was added in version X” showing a later version than current one
  75. Get records from Formidable Table using $wpdb->get_col
  76. Prepare WPDB with meta key and meta value
  77. How to get row value from wpdb
  78. I am using wpdb but it not working perfectly.but if I dont use form data its work
  79. Escaping a WPDB Object in One Shot
  80. wont add form details to database or send me mail
  81. Missing argument 2 for wpdb::prepare() [duplicate]
  82. wpdb->update update the entire table instead of one row
  83. WPDP related functions look to work but they don’t
  84. Save customizer default values to DB on theme activation
  85. Plugin with connection to database – Single function
  86. Alter the main search query to search posts by coauthor user name
  87. Protect custom form from SQL injection
  88. why nl2br() is adding an extra ?
  89. What is the best practice to initialise $wpdb by loading wp-load.php?
  90. How to add more custom fields in user meta table simultaneously
  91. wpdb->update error
  92. get unserialized array without using get_option()
  93. SQL Query to select post title & post ID from a particular category
  94. Create Table Failed Column Date DateType
  95. Using $wpdb to update current post
  96. Checking if meta_value exists for any user
  97. Can you create a new wpdb that connects to an SQL (not MYSQL) database? [duplicate]
  98. DBDelta: “table doesn’t exist” for a table that was just created
  99. query using wpdb in wordpress gets me no result
  100. WP Recommended Table Exclusions?
tech