I can load data from Joomla database and external database?

Question

It is possible to join 2 databases at the same time in a single query? For example: I want to see how many users I have on my server and how many users I have on an external server, all in a single SQL query.

I've been testing the use of union and JDatabase: http://docs.joomla.org/Using_the_union_methods_in_database_queries http://docs.joomla.org/How_to_connect_to_an_external_database

My code:

// Create a new query object.
$db1 = ExternalDataBase::getDbo();
$db  = JFactory::getDBO();

$query1 = $db1->getQuery(true);
$query1
   ->select('COUNT(id) AS users_number')
   ->from('#__users');

$query = $db->getQuery(true);
$query
   ->select('COUNT(id) AS users_number')
   ->from('#__users')
   ->union($query1);

$usersNumber = $db->setQuery($query)->loadObjectList();

The second query should select the user from the external database does not work.

Anyone have an idea how to solve this problem, thanks.

Answer 1

Query object is created from a database connection. It cannot manage two connected databases in a single query.

To solve the requirement, it is better if you enable the acces at MySQL level. If the two databases are local, you can just a ssign permissions to access the second database to your database user. In this way, the multi-database query is solved efficiently by MySQL server.