Connect with Multiple Databases in CodeIgniter 3

A database is one of the common requirements while building a dynamic application. It uses to store related data in an organized manner.

You can use more than one database in your application.

If you are using CodeIgniter Framework then you need to define the database connection details separately for multiple databases and load it explicitly for performing operations on the database.

---

Contents

1. Configuration
2. Model – Accessing multiple database dataModel – 
3. Create Controller
4. Conclusion

---

1. Configuration

Database connections information defined in application/config/database.php file.

Open application/config/database.php file.

• Define your first database details in $data['default'] array.
• Now for defining another database create copy of $data['default'] array.
• Change the array key name from ‘default’ to any other name e.g. ‘database2’. This key name use for loading the database.
• Set the configuration.

Completed Code

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
 'dsn' => '',
 'hostname' => 'localhost',
 'username' => 'root',
 'password' => '',
 'database' => 'tutorial', // 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
);

// Another database
$db['database2'] = array(
 'dsn' => '',
 'hostname' => 'localhost',
 'username' => 'root',
 'password' => '',
 'database' => 'tutorial_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
);

---

2. Model – Accessing multiple database data

How to Load Database?

Manually load the database by calling $this->load->database() method where pass the newly defined $data Array key name ($data['database2']) which defined in application/cofing/database.php.

Pass the second parameter TRUE to get the connection ID otherwise the object instance is not created.

$variable-name = $this->load->database('connection-name',TRUE);

---

Implement

Create a Main_model.php file in application/models/ folder.

Define a single method –

• getRecords() – Load the database using $this->load->database() and initialize $db2.

To select records from another database using the $db2 object instead of $this->db.

Completed Code

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Main_model extends CI_Model {

 function getRecords(){
 
     // Load database
     $db2 = $this->load->database('database2', TRUE);

     // Select records from 1st database
     $this->db->select('*');
     $q = $this->db->get('users');
     $result1 = $q->result_array();

     // Select records from 2nd database
     $db2->select('*');
     $q = $db2->get('students');
     $result2 = $q->result_array();
 
     $response = array("response1"=>$result1,"response2"=>$result2);
     return $response;
 }

}

---

3. Create Controller

Create a User.php file in application/controllers/ folder.

In the index() method load the Main_model and call getRecords() method to fetch records from two database tables.

Completed Code

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class User extends CI_Controller {

   public function index(){

       // load model
       $this->load->model('Main_model');

       // get data
       $data['response'] = $this->Main_model->getRecords();
 
   }

}

---

4. Conclusion

If you like to use multiple databases in your CodeIgniter project then define the other database details in the application/config/database.php file.

Load the database and use the connection object for data manipulation.

If you found this tutorial helpful then don't forget to share.