Hi,
In Zend Framework 2, you can set multiple database connection in your project.
I accomplish it via creating a common plugin. I like to address all the process to manage multiple connection step wise manner:
Step 1: Create directory inside vendor i.e. "multiDB".
Step 2: Create src, Controller, Model and other directory as specified below:
Step 3: Create Module.php file inside "multiDB" directory.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
| <?php
namespace multiDB;
use Zend\Mvc\ModuleRouteListener;
use Zend\Mvc\MvcEvent;
use multiDB\Model\ClientDetailsTable;
use multiDB\Controller\multiDbActionController;
use multiDB\Adapter\MyAdapterFactory;
class Module
{
public function onBootstrap(MvcEvent $e)
{
$eventManager = $e->getApplication()->getEventManager();
$moduleRouteListener = new ModuleRouteListener();
$moduleRouteListener->attach($eventManager);
}
public function getConfig()
{
return array();
}
public function getAutoloaderConfig()
{
return array(
'Zend\Loader\StandardAutoloader' => array(
'namespaces' => array(
__NAMESPACE__ => __DIR__ . '/src/' . __NAMESPACE__
)
)
);
}
/**
* Get service configuration
*
* @access public
* @return array
*/
public function getServiceConfig()
{
return array(
'factories' => array(
'master' => new MyAdapterFactory('master'),
'client' => new MyAdapterFactory('client'),
'multiDB\Model\ClientDetailsTable' => function ($serviceManager) {
return new ClientDetailsTable($serviceManager->get(multiDbActionController::getDb()));
},
)
);
}
}
|
Step 4: Create multiDbActionController.php file inside multidb's controller directory.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
| <?php
namespace multiDB\Controller;
use Zend\Mvc\Controller\AbstractActionController;
use multiDB\Model\ClientDetailsTable;
use Zend\Session\Container;
/**
* Basic action controller
*
* @category Zend
* @package multiDB
* @subpackage Controller
*/
class multiDbActionController extends AbstractActionController
{
/**
* Get client Creadentials on the behalf of selected clients
* @param string $masterDB
* @param string $masterHost
* @param string $masterUser
* @param string $masterPassword
* @param int $client_id
* @return array
*/
public function getClientDb($masterDB, $masterHost, $masterUser, $masterPassword, $client_id) {
$adapter = new \Zend\Db\Adapter\Adapter(array(
'driver' => 'pdo',
'dsn' => "mysql:dbname=$masterDB;host=$masterHost",
'username' => $masterUser,
'password' => $masterPassword
));
if($client_id != '') {
$clientDb = new ClientDetailsTable($adapter);
$clientcredential = $clientDb->getClientDetails(array(
"id" => $client_id
), array(
'client_name',
'db_name',
'db_user',
'db_host',
'db_password'
));
return $clientcredential[0];
}else {
echo "";
}
}
/**
* To set the adaptor for the specific client's OR Master database
* @return string
*/
public static function getDb()
{
$session = new Container('client');
if($session->offsetExists('client_id')) {
return 'client';
} else {
return 'master';
}
}
}
|
Step 5: Create "ClientDetailsTable.php" file inside multiDB's Model directory.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
| <?php
namespace multiDB\Model;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\ServiceManager\ServiceLocatorAwareInterface;
use Zend\ServiceManager\ServiceLocatorInterface;
use Zend\Db\Sql\Sql;
class ClientDetailsTable extends AbstractTableGateway implements ServiceLocatorAwareInterface
{
protected $_serviceLocator;
public $table = 'client_schema';
public function __construct(Adapter $adapter)
{
$this->adapter = $adapter;
$this->resultSetPrototype = new ResultSet(ResultSet::TYPE_ARRAY);
$this->initialize();
}
/**
* Set $_serviceLocator
*
* @access pubic
* @param ServiceLocatorInterface $serviceLocator
* // ServiceLocatorInterface instance
* @see \Zend\ServiceManager\ServiceLocatorAwareInterface::setServiceLocator()
*/
public function setServiceLocator(ServiceLocatorInterface $serviceLocator)
{
$this->_serviceLocator = $serviceLocator;
}
/**
* Get $_serviceLocator
*
* @access pubic
* @return \Zend\ServiceManager\ServiceLocatorAwareInterface
* @see \Zend\ServiceManager\ServiceLocatorAwareInterface::getServiceLocator()
*/
public function getServiceLocator()
{
return $this->_serviceLocator;
}
public function getClientDetails($where, $columns) {
$sql = new Sql($this->adapter);
$select = $sql->select()->from(array(
'cd' => $this->table
));
if (count($columns) > 0) {
$select->columns($columns);
}
if (count($where) > 0) {
$select->where($where);
}
if (! empty($orderBy)) {
$select->order($orderBy);
}
$statement = $sql->prepareStatementForSqlObject($select);
$clients = $this->resultSetPrototype->initialize($statement->execute())
->toArray();
return $clients;
}
}
|
Step 6: Create "MyAdapterFactory.php" inside multiDB's Adapter directory.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| <?php
namespace multiDB\Adapter;
use Zend\ServiceManager\FactoryInterface;
use Zend\ServiceManager\ServiceLocatorInterface;
use Zend\Db\Adapter\Adapter;
class MyAdapterFactory implements FactoryInterface
{
protected $configKey;
public function __construct($key)
{
$this->configKey = $key;
}
public function createService(ServiceLocatorInterface $serviceLocator)
{
$config = $serviceLocator->get('Config');
return new Adapter($config[$this->configKey]);
}
}
|
Step 7: Now you have create all the files for the multiDB module inside your vendor directory. Now you just have to club it with your running modules.
Here, Some changes required in your global.php file where you mentioned the connection settings. Please see the demo file settings for the global.php file.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
| <?php
use Zend\Session\Container;
/**
* Global Configuration Override
*
* You can use this file for overriding configuration values from modules, etc.
* You would place values in here that are agnostic to the environment and not
* sensitive to security.
*
* @NOTE: In practice, this file will typically be INCLUDED in your source
* control, so do not include passwords or other sensitive information in this
* file.
*/
$masterDB = "master_db";
$masterHost = "localhost";
$masterUser = "root";
$masterPassword = "root";
$session = new Container('client');
if(isset($_REQUEST['id']))
$session->offsetSet('client_id', $_REQUEST['id']);
$con = new \multiDB\Controller\multiDbActionController();
$client = $con->getClientDb($masterDB, $masterHost, $masterUser,
$masterPassword, $session->offsetGet('client_id'));
$clientDB = $client['db_name'];
$clientHost = $client['db_host'];
$clientUser = $client['db_user'];
$clientPass = $client['db_password'];
$session->offsetSet('client_name', $client['client_name']);
return array(
'master' => array('driver' => 'Pdo',
'dsn' => "mysql:dbname=$masterDB;host=$masterHost",
'username' => $masterUser, 'password' => $masterPassword),
'client' => array('driver' => 'Pdo',
'dsn' => "mysql:dbname=$clientDB;host=$clientHost",
'username' => $clientUser, 'password' => $clientPass)
);
|
Step 8: Now you have to include multiDB module in application.config.php file
1
2
3
4
5
6
7
| <?php
return array(
// This should be an array of module namespaces used in the application.
'modules' => array(
'Application',
'multiDB' //To handle multiple database on the fly
),
|
Step 9: Now you have setup everything for the multi db connection. now you just call the services for the model.
For example: i use the default module "Application", where i change in the Module.php.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| /**
* Get service configuration
*
* @access public
* @return array
*/
public function getServiceConfig()
{
return array(
'factories' => array(
'Application\Model\ClientModel' => function ($serviceManager)
{
return new ClientModel($serviceManager->get(multiDbActionController::getDb()));
},
'Application\Model\TestModel' => function($serviceManager) {
return new TestModel($serviceManager->get('master'));
}
)
);
}
|
Step 10: Now create the controller class inside module's controller directory i.e. IndexController.php file
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
| <?php
/**
* Zend Framework (http://framework.zend.com/)
*
* @link http://github.com/zendframework/ZendSkeletonApplication for the canonical source repository
* @copyright Copyright (c) 2005-2014 Zend Technologies USA Inc. (http://www.zend.com)
* @license http://framework.zend.com/license/new-bsd New BSD License
*/
namespace Application\Controller;
use Zend\Mvc\Controller\AbstractActionController;
use Zend\View\Model\ViewModel;
use Zend\Mvc\Application;
use Zend\Session\Container;
use Zend\EventManager\SharedEventManager;
use Application\Listener\ExampleEventsListener;
use Application\Entity\Product;
use Application\Form\CreateProduct;
class IndexController extends AbstractActionController
{
/**
* Use to handle multiple database on the fly
*/
public function dbswitchAction() {
$dataset = $this->getServiceLocator()->get('Application\Model\TestModel');
$emp = $dataset->getClientData();
return new ViewModel(array(
'data' => $emp
));
}
public function clientSetAction() {
$view = new ViewModel();
$session = new Container('client');
$clientData = "";
if(isset($_REQUEST['id']) && $_REQUEST['id'] != '') {
echo $clientData = "Connected to the client ".$_REQUEST['id']." Client NAME: ".$session->offsetGet('client_name');
$dataset = $this->getServiceLocator()->get('Application\Model\ClientModel');
$data = $dataset->getClientDataSet();
$session->offsetUnset('client_id');
print_r($data);
}
$view->setTerminal(true);
$session->offsetUnset('client_id');
return $view;
}
}
|
Step 11: Create view file for the same action inside module... i.e. db-switch.phtml file inside application->index directory.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
| <pre>
<?php
print_r($this->data);
?>
</pre>
<select id="client_name_id">
<option value="" onclick=onSelect();>Select</option>
<?php foreach($this->data as $key => $value) {?>
<?php echo "<option value=".$value['id']." onclick=onSelect();>".$value['client_name']."</option>";?>
<?php }?>
</select>
<script>
function onSelect() {
var val = $("#client_name_id option:selected").val();
console.log(val);
if(val != '') {
$.ajax({
url : "http://zf.local.com/client-set/",
type : 'GET',
data : {'id' : val},
success : function(resp) {
console.log(resp);
$("#client-set-id").text(resp);
}
});
} else {
$("#client-set-id").text('');
}
}
</script>
<pre>
<div id="client-set-id"></div>
<?php echo $this->clientData;?>
</pre>
|
Step 12: Now when you run your application with your local URL i.e.
http://localhost/db-switch then you will see the below output:
Here, you will see the dropdown where you can select the desired client and adepter auto connect with respective database.
Here, are the database mysql query for the application testing...
master_db
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
| -- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: master_db
-- ------------------------------------------------------
-- Server version 5.5.38-0ubuntu0.14.04.1
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `client_schema`
--
DROP TABLE IF EXISTS `client_schema`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `client_schema` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`client_name` varchar(45) NOT NULL,
`db_name` varchar(45) NOT NULL,
`db_user` varchar(45) NOT NULL,
`db_password` varchar(45) NOT NULL,
`db_host` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `client_schema`
--
LOCK TABLES `client_schema` WRITE;
/*!40000 ALTER TABLE `client_schema` DISABLE KEYS */;
INSERT INTO `client_schema` VALUES (1,'zend','zfskel','root','root','localhost'),(2,'test db 1','test_db1','root','root','localhost');
/*!40000 ALTER TABLE `client_schema` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2014-10-13 20:14:22
|
Another database i used it i.e. test_db1, dump of this database is:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
| -- MySQL dump 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: test_db1
-- ------------------------------------------------------
-- Server version 5.5.38-0ubuntu0.14.04.1
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `employee`
--
DROP TABLE IF EXISTS `employee`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `employee`
--
LOCK TABLES `employee` WRITE;
/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
INSERT INTO `employee` VALUES (0,'kaushal',27),(1,'tarun',26),(2,'varun',34);
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2014-10-13 20:16:09
|
That's It......
