Multiple Database Connections

[eluser]helmutbjorg[/eluser]
Hi Guys,

I have read a bit about multiple database connections in the manual but i'm a little stuck.

I know I can do this....

Code:

$DB1 = $this->load->database('group_one', TRUE);
$DB2 = $this->load->database('group_two', TRUE);

But what I really want to do is have a model use a particular database. So I found this:

Code:

$config['hostname'] = "localhost";
$config['username'] = "myusername";
$config['password'] = "mypassword";
$config['database'] = "mydatabase";
$config['dbdriver'] = "mysql";
$config['dbprefix'] = "";
$config['pconnect'] = FALSE;
$config['db_debug'] = TRUE;
$this->load->model('Model_name', '', $config);

The problem with this is that I am getting the database connection settings from another database (which is already connected). I want to load the new database and use the methods from the model on it. But from looking at the load code it only loads the db from that $config if there is not already a database connection. Is that correct?

Also can you use ignitedrecord with multiple database connections?

[eluser]helmutbjorg[/eluser]
I managed to simply overwrite the db object and that seems to work. Like so...

Code:

$this->db = $this->load->database($config, true);
$this->load->model('example');

But this isn't perfect as I cannot use the other connection (including the database session table) from that point on. Anyone have any better ideas?

[eluser]xwero[/eluser]
if the model uses another database why not load that database connection in the model constructor, this way the model has no dependencies. You have to watch out with the settings because the default pconnect setting is true.

[eluser]helmutbjorg[/eluser]
Thanks for the quick response! Can you give me an example of how you would do that?

Code:

class Example extends Model {

      function Example() {
           parent::Model();
      }

}

I didn't think I could load it because the model uses another database connection that is defined inside a database.

For example...

Code:

$this->load->model('account');
// The table of accounts is on DATABASE #1
$account = $this->account->find(7);
// The table of groups is on DATABASE #2 (which is owned by the account above)
$this->load->model('group', '', array('hostname'=>$account->db_host, 'username'=>$account->db_user, etc));
$group = $this->group->find(7);
$group->name = 'Hello';
$group->save();
// And then I still want to be able to use DATABASE #1
$this->account->find(8);
// And also the session
$this->session->userdata('testing');

But it doesn't seem to be working... The group model defaults to the first database connection even with the parameter specified.

[eluser]warrennz[/eluser]
From my basic understanding, PHP caches the MySQL DSN string. When you change database and attempt to use a different table, it still attempts to use that new table in the previous database. One way (not too sure how...'good' this practice is) I got around this was to create 2 very different DSN's so that php would cache them individually.

For example on the database config I would have


config/database.php

Code:

$active_group = "default";
$active_record = TRUE;

$db['default']['hostname'] = "127.0.0.1";
$db['default']['username'] = "user1";
$db['default']['password'] = "pass1";
$db['default']['database'] = "table1";

$db['seconddb']['hostname'] = "localhost";
$db['seconddb']['username'] = "user2";
$db['seconddb']['password'] = "pass2";
$db['seconddb']['database'] = "table2";
$db['seconddb']['dbdriver'] = "mysql";

'default' is my default config, and would be accessed via the normal $this->db in most places. Localhost and 127.0.0.1 are obviously pointing to the same host and user1 and user2 have exactly the same privileges on the database. The difference in DSN is enough to make php cache separately.

If I had a model that accesses a table in a different database, I would add a 2nd db property in the constructor

Code:

class radius extends Model {

    function radius_model()
    {
         parent::Model();

         $this->asidb = $this->load->database('seconddb', TRUE, TRUE);
    }

    function do_something()
    {

         return $this->asidb->where('something')->get('some_table')->result_array();

    }
}

Again, I'm to sure if this is a good practice or not but was the only way I managed to get around my multiple database issues.

Also, I don't think ORM is designed to support multiple databases. At least in my experience.

[eluser]xwero[/eluser]
warrennz that was what i wrote about in my response but i would add a class variable $asidb to the model to show the variable only lives in the model.

[eluser]helmutbjorg[/eluser]
Thanks guys...

I guess what I was under the impression of, was that when you loaded a model with a database connection passed that that model would then only use that specific connection. But what actually happens (on inspection of codeigniter source files) is that if there is already a connection it doesn't bother with the new one.

Most of the libraries use the $this->db object when dealing with a database. For example models, ignitedrecord, sessions, etc.

So what I needed to do was whenever I hit the model that needed to use the alternate database I switched the value of $this->db with the alternate connection.

Then afterwards I would switch it back so session calls worked and other things.

For example...

Code:

// Backup the local database connection (so we can switch between remote and local)
$local = $this->db;
// Load the models
$this->load->model(array('account','group'));
// The table of accounts is on DATABASE #1
$account = $this->account->find(7);
// Create a remote connection to the database for this account
$remote = $this->load->database(array('hostname'=>$account->db_host, 'username'=>$account->db_user, etc), true);
// Switch to remote connection
$this->db = $remote;
// The table of groups is on DATABASE #2 (which is owned by the account above)
$group = $this->group->find(7);
$group->name = 'Hello';
$group->save();
// Switch back so I can use database 1
$this->db = $local;
// I can still use DATABASE #1
$this->account->find(8);
// And also the session
$this->session->userdata('testing');

This works... can you guys see anything glaringly stupid about this solution. I really want the functionality of IgnitedRecord.

[eluser]fishboy[/eluser]
Hello helmutbjorg

Codeigniter was wonderful framework that I have use.But days ago I have met this problem.

When I found this solution I have been depressed.I wish CI developer solve this BUG ASAP.

[eluser]helmutbjorg[/eluser]
Fishboy I don't think it is a bug. It is just the way CI is made. I can understand how they have made it because it would get SERIOUSLY complicated if multiple database support worked how I originally thought.

The solution I have posted above works and it is good enough for me.

[eluser]fishboy[/eluser]
Thanx Helmutbjorg for your solution and also it works with me.

I said it is bug becuase of this tow line:

Code:

$DB1 = $this->load->database('group_one', TRUE);
$DB2 = $this->load->database('group_two', TRUE);

I think now, no meaning for this.