Codeigniter - multiple database connections

I have to retrieve a MySQL database information from master database and then connect to that database, and fetch some records.

I mean that holding one database I want to load another database.

Is it possible with Codeigniter? Right now I'm using following lines of code in my model.

function connectDb($credential)
{

$config['hostname'] = $credential['server'];
$config['username'] = $credential['username'];
$config['password'] = $credential['password'];
$config['database'] = $credential['database'];
$config['dbdriver'] = "mysql";
$config['dbprefix'] = "";
$config['pconnect'] = FALSE;
$config['db_debug'] = TRUE;
$config['cache_on'] = FALSE;
$config['cachedir'] = "";
$config['char_set'] = "utf8";
$config['dbcollat'] = "utf8_general_ci";

$DB2=$this->load->database($config);

$DB2->db->select('first_name,last_name');
$query = $DB2->db->get('person');
print_r($query);

}

its not working is there any other way?

While looking at your code, the only thing I see wrong, is when you try to load the second database:

$DB2=$this->load->database($config);

When you want to retrieve the database object, you have to pass **TRUE** in the second argument.

From the [Codeigniter User Guide][1]:
> By setting the second parameter to TRUE (boolean) the function will
> return the database object.

So, your code should instead be:

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

That will make it work.

[1]:

[To see links please register here]

Use this.

$dsn1 = 'mysql://user:password@localhost/db1';
$this->db1 = $this->load->database($dsn1, true);

$dsn2 = 'mysql://user:password@localhost/db2';
$this->db2= $this->load->database($dsn2, true);

$dsn3 = 'mysql://user:password@localhost/db3';
$this->db3= $this->load->database($dsn3, true);

Usage


$this->db1 ->insert('tablename', $insert_array);
$this->db2->insert('tablename', $insert_array);
$this->db3->insert('tablename', $insert_array);

The best way is to use different database groups. If you want to keep using the master database as usual ($this->db) just turn off **persistent connexion** configuration option to your secondary database(s). Only master database should work with persistent connexion :

Master database

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "database_name";
$db['default']['dbdriver'] = "mysql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = FALSE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";
$db['default']['swap_pre'] = "";
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Secondary database (notice pconnect is set to false)

$db['otherdb']['hostname'] = "localhost";
$db['otherdb']['username'] = "root";
$db['otherdb']['password'] = "";
$db['otherdb']['database'] = "other_database_name";
$db['otherdb']['dbdriver'] = "mysql";
$db['otherdb']['dbprefix'] = "";
$db['otherdb']['pconnect'] = FALSE;
$db['otherdb']['db_debug'] = FALSE;
$db['otherdb']['cache_on'] = FALSE;
$db['otherdb']['cachedir'] = "";
$db['otherdb']['char_set'] = "utf8";
$db['otherdb']['dbcollat'] = "utf8_general_ci";
$db['otherdb']['swap_pre'] = "";
$db['otherdb']['autoinit'] = TRUE;
$db['otherdb']['stricton'] = FALSE;

Then you can use secondary databases as **database objects** while using master database as usual :

// use master dataabse
$users = $this->db->get('users');

// connect to secondary database
$otherdb = $this->load->database('otherdb', TRUE);
$stuff = $otherdb->get('struff');
$otherdb->insert_batch('users', $users->result_array());

// keep using master database as usual, for example insert stuff from other database
$this->db->insert_batch('stuff', $stuff->result_array());

If you need to connect to more than one database simultaneously you can do so as follows:

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

Note: Change the words “group_one” and “group_two” to the specific group names you are connecting to (or you can pass the connection values as indicated above).

By setting the second parameter to TRUE (boolean) the function will return the database object.

Visit

[To see links please register here]

for further information.

default database
$db['default'] = array(
'dsn' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'test',
'dbdriver' => 'mysqli',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);
another db
$db['database2'] = array(
'dsn' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'test_2', // Database name
'dbdriver' => 'mysqli',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);
in the model you have to call

public function getRecords(){
// Load database
$db2 = $this->load->database('database2', TRUE);

// Select records from 1st database
$this->db->select('*');
$query = $this->db->get('record1');
$result1 = $query->result_array();

// Select records from 2nd database
$db2->select('*');
$query = $db2->get('record2');
$result2 = $query->result_array();
$response = array("response1"=>$result1,"response2"=>$result2);
return $response;
}

It works fine for me(Codeigniter 3)...
-----------------------


> This is default database :


$db['default'] = array(
'dsn' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'mydatabase',
'dbdriver' => 'mysqli',
'dbprefix' => '',
'pconnect' => TRUE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);



> Add another database at the bottom of database.php file

$db['second'] = array(
'dsn' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'mysecond',
'dbdriver' => 'mysqli',
'dbprefix' => '',
'pconnect' => TRUE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);



> In autoload.php config file

$autoload['libraries'] = array('database', 'email', 'session');




> The default database is worked fine by autoload the database library
> but second database load and connect by using constructor in model and
> controller...

<?php
class Seconddb_model extends CI_Model {
function __construct(){
parent::__construct();
//load our second db and put in $db2
$this->db2 = $this->load->database('second', TRUE);
}

public function getsecondUsers(){
$query = $this->db2->get('members');
return $query->result();
}

}
?>

As a supplement to the other answers, if you want to change the active database group **within** the model itself, so not needing to use another database connection, use the following within a model class:

public function set_database($database)
{
$CI =& get_instance();
$CI->db = null;
$this->load->database($database);
}

After calling from a controller, like:

$this->my_model->set_database('other_db');

You can then use your other model methods acting upon the newly specified database group, like:

$this->my_model->insert($data);

Note: Currently, only tested with codeigniter 3.

You should provide the second database information in `application/config/database.php´

Normally, you would set the `default` database group, like so:

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "database_name";
$db['default']['dbdriver'] = "mysql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = FALSE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";
$db['default']['swap_pre'] = "";
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Notice that the login information and settings are provided in the array named `$db['default']`.

You can then add another database in a new array - let's call it 'otherdb'.

$db['otherdb']['hostname'] = "localhost";
$db['otherdb']['username'] = "root";
$db['otherdb']['password'] = "";
$db['otherdb']['database'] = "other_database_name";
$db['otherdb']['dbdriver'] = "mysql";
$db['otherdb']['dbprefix'] = "";
$db['otherdb']['pconnect'] = TRUE;
$db['otherdb']['db_debug'] = FALSE;
$db['otherdb']['cache_on'] = FALSE;
$db['otherdb']['cachedir'] = "";
$db['otherdb']['char_set'] = "utf8";
$db['otherdb']['dbcollat'] = "utf8_general_ci";
$db['otherdb']['swap_pre'] = "";
$db['otherdb']['autoinit'] = TRUE;
$db['otherdb']['stricton'] = FALSE;

Now, to actually use the second database, you have to send the connection to another variable that you can use in your model:

function my_model_method()
{
$otherdb = $this->load->database('otherdb', TRUE); // the TRUE paramater tells CI that you'd like to return the database object.

$query = $otherdb->select('first_name, last_name')->get('person');
var_dump($query);
}

That should do it.
The documentation for connecting to multiple databases can be found here:

[To see links please register here]