How Can I Query Multiple Databases Simultaneously to Retrieve Data?

Querying Multiple Databases Simultaneously

Accessing data across multiple databases can be a complex task, especially when querying for specific information spread over several databases.

Scenario

Consider a situation where you have multiple separate WordPress databases, each representing a different WordPress site. To update a feature, you need to query the list of active plugins for each WordPress instance. Active plugins are stored in the 'wp_options' table using the WHERE clause:

WHERE option_name = 'active_plugins'

Challenge

The challenge lies in retrieving active plugin information from all databases and displaying it as a single SQL result. While the database.tablename syntax is known, determining how to apply the WHERE statement across multiple databases remains the issue.

Solution

To query multiple databases simultaneously, the UNION operator can be employed. The UNION operator combines multiple SELECT statements into a single result. By using the UNION operator, you can seamlessly merge the results from each database into one consolidated SQL result.

The following SQL query exemplifies how to accomplish this:

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"

This query retrieves active plugin data from both 'database1' and 'database2' and collates the results into a single, comprehensive result set.

The above is the detailed content of How Can I Query Multiple Databases Simultaneously to Retrieve Data?. For more information, please follow other related articles on the PHP Chinese website!