Querying multiple databases at once

mysql multiple-databases
Boldewyn picture Boldewyn · Jan 25, 2010 · Viewed 94.9k times · Source

I have WordPress instances with each in its own database. For an update I need to query all active plugins, that are stored in a table 'wp_options' and accessible via

WHERE option_name='active_plugins'

How can I access all active plugin settings (spread over multiple databases) and output them in one single SQL result? I know the database.tablename syntax, but how do I go on from there with the above Where statement?

A request in a single database would look like this:

SELECT option_value
  FROM `database`.`wp_options`
 WHERE option_name="active_plugins"

Answer

Pentium10 picture Pentium10 · Jan 25, 2010 
SELECT option_value
 FROM `database1`.`wp_options`
  WHERE option_name="active_plugins"
UNION
SELECT option_value
 FROM `database2`.`wp_options`
  WHERE option_name="active_plugins"

Related questions

How to use multiple databases dynamically for one model in CakePHP

Ok, my first question was modified so many times that I chose to delete it and reformulate my question. I've made a small test-case project with different model-names to find a proper solution for my problem. Warning: Don't mix the …

Read More
Multiple database connection in Rails

I'm using active_delegate for multiple connection in Rails. Here I'm using mysql as master_database for some models,and postgresql for some other models. Problem is that when I try to access the mysql models, I'm getting the error …

Read More
Join query of two databases in codeigniter

I need to write a join query of two tables from two databases and fetch the joined data. For eg, consider I have a database db1 which has some tables named companies, plans, customers. Suppose I need to join the …

Read More