We are creating a web application using Zend Framework 2 with multiple databases. I have a core
database which loads info of all customers. This database contains customer
table. The fields of customer table are ::
- id
- username
- password
- database_name
- customer_name
- ...................
When a customers logs in, i have to load his database name from the core
database and then make query requests to the database.
I cannot have multiple adapters either, because all customers have their own database which i have to load from customer
table of core_db
!
I thought i would prefix database name with table name.
I tried this in Module.php
:
"CategoryTableGateway" => function ($sm) {
$dbAdapter = $sm->get("Zend\Db\Adapter\Adapter");
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Category());
return new TableGateway("databasename.category", $dbAdapter, null, $resultSetPrototype);
}
I had configured default database in my config\autoload\database.global.php
as this:
'db' => array(
'driver' => 'Pdo',
'dsn' => 'mysql:dbname=core_db;host=localhost',
'driver_options' => array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
),
)
I got a exception like:
Base table or view not found: 1146 Table 'core_db.databasename.category' doesn't exist.
And then, I removed dbname=core_db
from config\autoload\database.global.php
.
Now, I got another exception like:
SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected
So how do i handle that situation in Zend Framework 2. I am new to Zend Framework 2.
Edit
: I got the solution to my question by myself.
To connect to table of another schema you need to pass TableIdentifier
instead of table!
For example,
Instead of:
$CategoryTableGateway = new TableGateway("category", $dbAdapter, null, $resultSetPrototype);
You have to do:
$CategoryTableIdentifier = new TableIdentifier('category','dbname');
$CategoryTableGateway = new TableGateway($CategoryTableIdentifier, $dbAdapter, null, $resultSetPrototype);
Hope It Works!