In CodeIgniter how do I load a database from a separate file, not the default config\database.php ?
Lets say that under libraries I have a folder named db_configs. Inside each database, details will be stored in separate file, ex. DB_01.php, DB_02.php,etc.
Thanks,
It is much easier to use the functionality built in to CI where multiple database connections can be defined in one file. To do otherwise is to reinvent the wheel.
Any given connection set (as defined in database.php) can be selected when you load the database. For instance, given DB_01 and DB_02 you would load them with
$this->load->database('DB_01');
or
$this->load->database('DB_02');
If you need both at once you can do this
$db1 = $this->load->database('DB_01', TRUE);
$db2 = $this->load->database('DB_02', TRUE);
But if you must have separate files there are a couple different approaches. Perhaps the easiest is to use helpers
application/helpers/db2_helper.php
function db2Config()
{
return array(
'dsn' => '',
'hostname' => 'localhost',
... etc.
);
}
In some controller
$this->load->helper('db2');
$db2_settings = db2Config();
$this->load->database($db2_settings);
It could also be done using the Config class like this.
application/config/db2.php
<?php
$config['dsn'] = '';
$config['hostname'] = 'localhost';
$config['username'] = 'IAmAUser';
$config['password'] = 'mypassword';
$config['database'] = 'theDB';
$config['dbdriver'] = 'mysqli';
$config['dbprefix'] = '';
$config['pconnect'] = TRUE;
...
In some controller
$this->config->load('db2', TRUE);
$db2_config = $this->config->item('db2');
$this->load->database($db2_config);
Best thing is use Codeigniter provided database.php.
Important !!!!!
You no need to create separate database configurations if you only need to use a different database on the same connection. You can switch to a different database when you need to, like this:
$this->db->db_select($database2_name);
In Codeigniter 2
Load first databse as defult
$db['default']['hostname'] = 'localhost';
$db['db_1']['username'] = 'root';
$db['db_1']['password'] = '';
$db['db_1']['database'] = 'my_db';
$db['db_1']['dbdriver'] = 'mysql';
$db['db_1']['dbprefix'] = '';
$db['db_1']['pconnect'] = TRUE;
$db['db_1']['db_debug'] = TRUE;
$db['db_1']['cache_on'] = FALSE;
$db['db_1']['cachedir'] = '';
$db['db_1']['char_set'] = 'utf8';
$db['db_1']['dbcollat'] = 'utf8_general_ci';
$db['db_1']['swap_pre'] = '';
$db['db_1']['autoinit'] = TRUE;
$db['db_1']['stricton'] = FALSE;
Load Second database
$db['db_2']['hostname'] = 'localhost';
$db['db_2']['username'] = 'root';
$db['db_2']['password'] = '';
$db['db_2']['database'] = 'my_db_2';
$db['db_2']['dbdriver'] = 'mysql';
$db['db_2']['dbprefix'] = '';
$db['db_2']['pconnect'] = TRUE;
$db['db_2']['db_debug'] = TRUE;
$db['db_2']['cache_on'] = FALSE;
$db['db_2']['cachedir'] = '';
$db['db_2']['char_set'] = 'utf8';
$db['db_2']['dbcollat'] = 'utf8_general_ci';
$db['db_2']['swap_pre'] = '';
$db['db_2']['autoinit'] = TRUE;
$db['db_2']['stricton'] = FALSE;
In Codeigniter 3
Load First database
$db['db_1'] = array(
'dsn' => '',
'hostname' => 'localhost',
'username' => '',
'password' => '',
'database' => '',
'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
Load Second database
$db['db_2'] = array(
'dsn' => '',
'hostname' => 'localhost',
'username' => '',
'password' => '',
'database' => '',
'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 codeigniter 2
$db_1 = $this->load->database('db_1', TRUE); # load First DB
$db_2 = $this->load->database('db_2', TRUE); # load Second DB
$query = $secondDb->select('first')->get('login');
By setting the second parameter to TRUE (boolean) the function will return the database object.
Early when single DB we use this(below) lines at end of the Query.
$this->db->query();
$this->db->result();
But now just little bit change to it
$db_1->query();
$db_1->result();
# or
$db_2->query();
$db_2->result();
In order to use multiple database connections in your CodeIgniter project, you just need to create multiple configuration arrays that simplify working with multiple databases.
Following is the structure of the default Codeigniter database configuration array:
$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'cloudwaysdb';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = FALSE;
$db['default']['cache_on'] = FALSE;
$db['default']['autoinit'] = FALSE;
$db['default']['stricton'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
So in order to create another database connection, you should create another configuration array. This array has to follow the same structure. Here is an example of the array:
$db['anotherdb']['hostname'] = 'XXX.XXX.X.XXX';
$db['anotherdb']['username'] = 'another_user';
$db['anotherdb']['password'] = '';
$db['anotherdb']['database'] = 'anothercloudwaysdb';
$db['anotherdb']['dbdriver'] = 'mysql';
$db['anotherdb']['dbprefix'] = '';
$db['anotherdb']['pconnect'] = TRUE;
$db['anotherdb']['db_debug'] = FALSE;
$db['anotherdb']['cache_on'] = FALSE;
$db['anotherdb']['cachedir'] = '';
$db['anotherdb']['char_set'] = 'utf8';
$db['anotherdb']['dbcollat'] = 'utf8_general_ci';
$db['anotherdb']['swap_pre'] = '';
$db['anotherdb']['autoinit'] = FALSE;
$db['anotherdb']['stricton'] = FALSE;
At this point, you have two databases in your sample project. To connect to a specific database, you must specify the database name. Here is the proper syntax:
this->load->database(anotherdb, TRUE)
After connecting to the database, you can perform databse operations as shown below:
// load 'anothercloudwaysdb'
$this->legacy_db = $this->load->database(anothercloudwaysdb, true);
// fetch result from 'cloudwaysdb'
$this->legacy_db->select ('*');
$this->legacy_db->from ('cloudwaysdb');
$query = $this->legacy_db->get();
$result = $query->result ();
Now if you need to work with the second database, you have to send the connection to a variable that is usable in your model:
function db_calling_model_method()
{
$otherdb = $this->load->database('anotherdb', TRUE); // the TRUE paramater tells CI that you'd like to return the database object.
$query = $otherdb->select('column_one, column_two')->get('table');
var_dump($query);
}
Source: https://www.cloudways.com/blog/pass-data-between-functions-in-codeigniter/
