CODEWITHSUNDEEP
mysqldatabasecodeigniter
Sadaf Sid
Sadaf Sid

Reputation: 1570

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?

Upvotes: 69

Views: 169003

Answers (8)

Repox
Repox

Reputation: 15475

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: http://codeigniter.com/user_guide/database/connecting.html

Upvotes: 104

James John McGuire 'Jahmic'
James John McGuire 'Jahmic'

Reputation: 12684

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.

Upvotes: 0

AngularJMK
AngularJMK

Reputation: 1278

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(); 
        }
 
    }
?>

Upvotes: 17

linsha
linsha

Reputation: 21

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;
 }

Upvotes: 2

Dhiraj Shrestha
Dhiraj Shrestha

Reputation: 58

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 https://www.codeigniter.com/userguide3/database/connecting.html for further information.

Upvotes: 1

SimDion
SimDion

Reputation: 1080

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());

Upvotes: 24

Rayiez
Rayiez

Reputation: 1599

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);

Upvotes: 18

Quetzy Garcia
Quetzy Garcia

Reputation: 1840

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:

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.

Upvotes: 7

Related Questions