CodeIgniter: Multiple Databases - Accessing databa

Question

I've been looking into using multiple databases with CodeIgniter. If I know what the databases are ahead of time, then I can set the information in the config file and then call whichever database group I need.

In my situation, however, I need to store that database information in another database. It is sort of a master database with general information about a customer including the database and credentials that the customer's data is stored in. This vendor can then add customers whenever they want and have each customer's data segregated in different databases.

How can I set the database and credentials based on the values I get back from the master database in CodeIgniter, or is there even a way to do that?

Can anyone point me in the right direction? Thanks in advance for any advice.

Answer 1

From the docs ( https://www.codeigniter.com/user_guide/database/connecting.html ) :

The first parameter of this function can optionally be used to specify a particular database group from your config file, or you can even submit connection values for a database that is not specified in your config file.

So you would do something like this, replacing the values with values from the master database:

$config['hostname'] = "localhost";
$config['username'] = "myusername";
$config['password'] = "mypassword";
$config['database'] = "mydatabase";
$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";

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

If you need to maintain a connection to the master database and the customer database, then change the last line to:

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

// to use the master database:
$this->db->query("SELECT * FROM my_table");

// to then use the customer database:
$customer_db->query("SELECT * FROM whatever");

Answer 2

Make the master a default database and the customer for second database
$active_group = 'default'; $active_record = TRUE;

    $db['default']['hostname'] = '';
    $db['default']['username'] = '';
    $db['default']['password'] = '';
    $db['default']['dbdriver'] = '';
    $db['default']['dbprefix'] = '';
    $db['default']['pconnect'] = TRUE;
    $db['default']['db_debug'] = TRUE;
    $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;

    $db['secondDatabase']['hostname'] = '';
    $db['secondDatabase']['username'] = '';
    $db['secondDatabase']['password'] = '';
    $db['secondDatabase']['dbdriver'] = '';
    $db['secondDatabase']['dbprefix'] = '';
    $db['secondDatabase']['pconnect'] = TRUE;
    $db['secondDatabase']['db_debug'] = TRUE;
    $db['secondDatabase']['cache_on'] = FALSE;
    $db['secondDatabase']['cachedir'] = '';
    $db['secondDatabase']['char_set'] = 'utf8';
    $db['secondDatabase']['dbcollat'] = 'utf8_general_ci';
    $db['secondDatabase']['swap_pre'] = '';
    $db['secondDatabase']['autoinit'] = TRUE;
    $db['secondDatabase']['stricton'] = FALSE;

you can load the second database in controller or in model by

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

Answer 3

Add below line in application\config\database.php

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

Now we use our second database in our Controller and model like below.

$CI = &get_instance();
$this->db2 = $CI->load->database('mydb2', TRUE);
$qry = $this->db2->query("SELECT * FROM employee");
print_r($qry->result());

I have taken reference from http://www.tutsway.com/use-multiple-db-connections-in-codeigniter.php .It's work for me.

Answer 4

/** config/database.php **/

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

$db['default']['hostname'] = '';
$db['default']['username'] = '';
$db['default']['password'] = '';
$db['default']['dbdriver'] = '';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = (ENVIRONMENT !== 'production');
$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; 

/** Your controller or model **/

//by default the master database will be loaded and you can directly access db using      $this->db 
   $result = $this->db->query("SELECT * FROM `your_table`")->limit(1)->get()->result();



$config['dbxyz']['hostname'] = $result->hostname;
$config['dbxyz']['username'] = $result->username;
$config['dbxyz']['password'] = $result->password;
$config['dbxyz']['dbdriver'] = '';
$config['dbxyz']['dbprefix'] = '';
$config['dbxyz']['pconnect'] = TRUE;
$config['dbxyz']['db_debug'] = (ENVIRONMENT !== 'production');
$config['dbxyz']['cache_on'] = FALSE;
$config['dbxyz']['cachedir'] = '';
$config['dbxyz']['char_set'] = 'utf8';
$config['dbxyz']['dbcollat'] = 'utf8_general_ci';
$config['dbxyz']['swap_pre'] = '';
$config['dbxyz']['autoinit'] = TRUE;
$config['dbxyz']['stricton'] = FALSE;

//load database config
$this->config->load('database');

//Set database config dynamically        
$this->config->set_item('dbxyz', $config);

//Now you can load the new database using
$this->dbxyz = $this->load->database('dbxyz'); 

NOTE: For more details, refer Config Class Codeigniter documentation