1

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.

2

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.

  • @Jordi did this answer solve your question to your satisfaction? If so, please award the green tick, if not please ask for more help. All questions should progress to a system-recognized resolution. – mickmackusa Jan 28 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.