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 ::
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!
When a customers logs in, i have to load his database name from the core database and then make query requests to the database.
This is what you are doing wrong.
Just keep everything in single database, like very e-commerce site in the world does.