how to switch database in codeigniter

Question

i'm new to CI and i just wanna know is there a way to switch databases in CI,Eg: In login Page, there's a dropdown list that you can specify which database you wanna connect with, view look like this

<select name="select" id="select"  >
  <option value="1">DB1</option>                
  <option value="2">DB2</option>              
</select>

And i got 2 databases in database.php one is defaultthe other is local by default i'm using the default one.

$active_group = 'default'; $active_record = TRUE;

My question is how do i specify db based on the dropdown value, say if i pick DB1, connect to default one, DB2 goes to local one.... I know how to swtich db manually by :

 $this->load->database('default', TRUE); OR
 $this->load->database('local', TRUE);

Since i got different controller and model, how can i achieve this... i did try like: first get the value of dropdownlist in login function:

$this->load->model('My_Model');
$db = $this->input->post('select')
$this->My_Model->getDB($db);

and then in my model i got function :

function getDB($db)
{
    if($db ==1)
    {
        $this->db  = $this->load->database('default', TRUE);
         }
    elseif($db ==2)
    {
        $this->db  = $this->load->database('local', TRUE);
    }
}

Unfortunately, it doesn't work..... Any help would be much Appreciated!!!!

Answer 1

I got it to work. First I edited my database.php configuration file to include the new database. Then, in my model, I declared a class variable $current_db, and I wrote the following two functions:

function getDB($db){
    if($db ==1){
        $this->current_db  = $this->load->database('default', TRUE);
    } elseif($db ==2) {
        $this->current_db  = $this->load->database('local', TRUE);
    }
}

function dyno_query($table, $id){
    $query = $this->current_db->get_where($table, array('id' => $id));
    return $query->result_array();
}

Then in my controller, I ran the following:

$arr = array();
$this->My_Model->getDB(1);
$arr['default'] = $this->My_Model->dyno_query('default_table', 2);
$this->My_Model->getDB(2);
$arr['local'] = $this->My_Model->dyno_query('local_table', 74);
var_dump($arr);

The result was an array with data from both databases. Maybe the key to it all is defining a class variable with a name other than $db.

EDIT:

To keep the current database loaded on each page load, you'll need to use sessions. In the controller function that handles the dropdown data, you could enter something similar to the following:

$db = $this->input->post('select')
$this->My_Model->getDB($db);
$this->session->set_userdata($db);

Within any controller that will use the database you'll want to add code to load the current database:

function __construct(){
    parent::__construct();
    $this->load->model('My_Model');
    $db = $this->session->userdata('db');
    $this->My_Model->getDB($db);
}

EDIT:

If you are going to need to access the same database from various models, I suggest using a library by mddd at EllisLab. Just create a PHP file named Db_manager.php with the folowing code and drop it into your application/libraries directory:

class Db_manager
{
    var $connections = array();
    var $CI;

    function __construct()
    {
        $this->CI =& get_instance();
    }

    function get_connection($db_name)
    {
        // connection exists? return it
        if (isset($this->connections[$db_name])) 
        {
            return $this->connections[$db_name];
       }
       else
       {
            // create connection. return it.
            $this->connections[$db_name] = $this->CI->load->database($db_name, true);
            return $this->connections[$db_name];
        }
    }
}

In the constructor of each model that will use the database add the following:

var $current_db;

function __construct(){
    parent::__construct();
    $this->load->library('Db_manager');
    $db = $this->session->userdata('db');
    if ($db == 1){
        $this->current_db = $this->db_manager->get_connection('default');
    } else {
        $this->current_db = $this->db_manager->get_connection('alternate');
    }
}