We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Connect to different db within LOOP

Kindly refer following code snippest to understand what I am trying to achieve,


<?php 

function getDBParam()
{
$dbs['test1'] = array('db_name' => 'test1', 'db_user' => 'test', 'db_host' => 'localhost','db_pass' => 'pass'); 
$dbs['test2'] = array('db_name' => 'test2', 'db_user' => 'test', 'db_host' => 'localhost','db_pass' => 'pass');
$dbs['test3'] = array('db_name' => 'test3', 'db_user' => 'test', 'db_host' => 'localhost','db_pass' => 'pass');
global $global_r; 
return $dbs[$global_r];
}

$di->set('db', function(){
$temp = getDBParam();
$array = array(
        'host' => $temp['db_host'],
        'username' => $temp['db_user'],
        'password' => $temp['db_pass'],
        'dbname' => $temp['db_name'],
    );

$connection = new \Phalcon\Db\Adapter\Pdo\Mysql($array);
return $connection;
}

// Code to loop through different db and display the count of records

$records = ['test1', 'test2', 'test3'];
    foreach ($records as $r) {
    $global_r = $r;
    $robots = Robots::find();
    echo " \nCount :" . count($robots) . "\n";
    }
?>

db test1 has 10 records of Robots,

db test2 has 20 records of Robots,

db test3 has 30 records of Robots,

But while printing it always give count as 10 records i.e. the count of test1 db.

Kindly let me know if there is any way to connect to different Database in a loop?



5.7k
edited May '15

There are two sections of the documentation you might benefit from:

https://docs.phalcon.io/en/latest/reference/models.html#setting-multiple-databases

https://docs.phalcon.io/en/latest/reference/models.html#pointing-to-a-different-schema

You could create a database service for each of the databases then use a custom flag before your query to set your connection service. Another idea could be to again create a custom flag that you would use to set the database name you want to connec to and use that to change the value of your getSchema function.



2.0k
in reply to Steven

Hi Steven,

Thanks for the reply. If I understand correctly what you are suggesting is to have

  1. $di->set('db') setup dynamically like for e.g. $di->set($flag)
  2. use $this->setConnectionService($flag); within Models Initialize method?

There are two seconds of the documentation you might benefit from:

https://docs.phalcon.io/en/latest/reference/models.html#setting-multiple-databases

https://docs.phalcon.io/en/latest/reference/models.html#pointing-to-a-different-schema

You could create a database service for each of the databases then use a custom flag before your query to set your connection service. Another idea could be to again create a custom flag that you would use to set the database name you want to connec to and use that to change the value of your getSchema function.



5.7k

Thats close to what I was thinking. The only thing I would do differently is that I wouldn't create the services dynamically.

I would explicitly set the service manually:

$di->set('db',function(){...}); // Default
$di->set('db2',function(){...}); // Specific Schema 2
$di->set('db3',function(){...}); // Specific Schema 3

And as for #2, that is pretty much what I was thinking. How you actually implement it may vary.



2.0k
in reply to Steven

Hi Steven,

Thanks for prompt reply. Actually setting the db manually is not an option, as we setup new db with every new signup. Also we have around 50+ models so if possible would like to have some quick solution to this if possible.

Thanks once again.

Thats close to what I was thinking. The only thing I would do differently is that I wouldn't create the services dynamically.

I would explicitly set the service manually:

$di->set('db',function(){...}); // Default
$di->set('db2',function(){...}); // Specific Schema 2
$di->set('db3',function(){...}); // Specific Schema 3

And as for #2, that is pretty much what I was thinking. How you actually implement it may vary.