ZEND FW : Joining two tables from different databa

Question

I want to join two tables in differenet databases on the same server. Could someone tell me how I could do this in Zend Frameworks Db adapter?

Answer 1

If the databases are on the same server and your user have access to them both, you can use full path - SELECT database_name.table_name.col_name...

Otherwise there is no chance for you to join them because of the principle. The join is done by SQL server - which would need to login to the other database...

On Oracle there is a feature called DBLink... which offers abbility to link other table/view from different server. Not sure about MySQL.

To make queries using different adapters you can use:

$select1 = new Zend_Db_Select($adapter1);
$select2 = new Zend_Db_Select($adapter2);

But again, you are still limited in things like union of these two... You can't do that because of the very nature of how the database works

Answer 2

see:

connecting to two different databases with Zend Framework

http://blog.keppens.biz/2009/04/zendapplication-multiple-databases.html

http://blog.ekini.net/2009/03/04/zend-framework-connecting-to-2-databases/

Answer 3

Build tableGateway from your dbAdapter before joining two tables from different database.

use use Zend\Db\Sql\Select;
use Zend\Db\Sql\Where;

$someCondition=new Where();
$someCondition->equalTo('columnName',$columnValue);
//you can build $this->tableGateway from your DB adapter
$rowset = $this->tableGateway->select(function (Select $select) use ($someCondition) {
        $table2forInnerJoin = new \Zend\Db\Sql\TableIdentifier('table2Name', 'table2Database');
        $select->join(array('table2Name'=>$table2forInnerJoin),"table1Name.id = table2Name.id");
        $select->where($someCondition);
});
return $rowset;