X
    Categories: Learn PHP Tutorials, Tips And Guides

Create Multiple Database Connections in CodeIgniter Applications

In real world CodeIgniter projects, developers need to work with multiple databases at the same time. This presents a unique challenge to developers. Since this is a common enough problem, CodeIgniter offers a simple solution for it.

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.

The Default Configuration Array

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;

Connect to the Right Database

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

}

Close the Connections

CodeIgniter does close the database connections after it determines that the code no longer need the connection. However, as a good practice, developers should close the connections explicitly. Here is how to take care of the issue:

$this->db->close(); // for default Connection

$this->legacy_db->close(); // for second Connection

Conclusion

In this article, I have discussed the problem and the solution to using multiple database connections in CodeIgniter projects. If you need help with implementing the idea in your projects, do leave a comment below and I will get back to you.

Owais Alam: is the WordPress Community Manager at Cloudways - A Managed WooCommerce Hosting Platform and a seasoned PHP developer. He loves to develop all sorts of websites on WordPress and is in love with WooCommerce in particular. You can email him at owais.alam@cloudways.com