Codeigniter - best way to use two different database

Question

Does someone knows the best practice for using 2 different database in my application?

I need to store data in both databases which are differently located (host,username,password , all does change).

I'm planning to create models as usual, and in construct set the db host,name,pass etc.

Solution 1

I'm not sure if you call this "best" way, but a way, as described in the tutorial, is this,

in the database file, you have the default configuration, a part of which is:

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "user";
$db['default']['password'] = "database";
$db['default']['database'] = "db1";

now you can create another group, say we call it group1 and we want it to have everything the same as the default database settings except for the name, so you can do

$db['group1']=$db['default'];
$db['group1']['database']="db2";

then, when you want to use the second database, just go

$DB2 = $this->load->database('group1', TRUE); 

and then, instead of $this->db->foo() , you will do $DB2->foo()

alternatively (as suggested in comments by sbaaaang), you can do $this->db=$DB2; to keep everything the same

and you can extend this to multiple groups like this

 $DB1 = $this->load->database('group1', TRUE); 
 $DB2 = $this->load->database('group2', TRUE); 
 ...
 $DBn = $this->load->database('groupn', TRUE); 

Solution 2

Well i would like just to write here my solution cause i've used less code i think:

in database.php i set the database groups so for ex:

$database['default']['dbname'] = 'db1';
$database['second_db']['dbname'] = 'db2';

then in models i used the constructor to switch to database i want to use like this:

//use db1
function __construct()
    {
        // Call the Model constructor
        parent::__construct();
         $this->load->database('default',true);
    }
//use db2
function __construct()
    {
        // Call the Model constructor
        parent::__construct();
         $this->load->database('second_db',true);
    }

if anyone would like to show more solutions do that please! These are just my two cents.

Solution 3

I read that

$db['default']['pconnect'] = FALSE;

must be false for these things to work. Please correct me if I'm wrong. For ALL multiple databases you use, it has to be off. I don't know if this is a good solution.

Comments

[itsme]

i think it's ok, then i can set the db group to use in the model construct

[itsme]

$this->db = $this->load->database('group1',TRUE);

[itsme]

there is one problem, when loading 2 models in controllers the first one loaded sets the database to use also for the second loaded,and also if the second model loaded has different db in __construct , so.. if anyone can help me fixing this i think we really are!

[Max]

This looks OK. But isn't there a simple way to just change database name and not the whole config. Something like we use in core php, mysql_select_db(dbname). As in my case I only need to change db name and all my dbs are on same server.